Pros and Cons of Multi-User
Database Alternatives
All organizations work with data, and it's usually necessary for data to be shared between several people. Not all organizations, however, have the infrastructure necessary to support high-end database platforms. Many organizations do not have any type of information technology department, and therefore need solutions that require a minimum of maintenance and infrastructure upgrade.
This document looks at some of the choices available for developing multi-user database applications. Specifically, this document considers the pros and cons of the following options:
Spectrum's Edge developed this document to help our clients and potential clients better understand their alternatives, and to explain what infrastructure a given application will require.
Option 1. Multi-user local database
In this scenario, a single local database is placed on one workstation, and is then shared by all of the workstations that need to add or view data in the database. Typically, the database file itself is a Microsoft Access .mdb file or something similar.
Pros:
Ease of setup
A peer-to-peer network and a shared folder on a single machine are all that are needed.
Inexpensive
Everything you need comes with Windows.
Cons:
Non-scalable
Local
databases are not designed to be used simultaneously by several users.
I'd recommend no more than five people reading or writing data at the
same time.
Possibility of data loss
If the machine with the database file crashes in the middle of a transaction, the database could become corrupt. Backups are essential, although this is true for all of the options.
Performance
Larger
databases will begin to run more slowly, since the entire file is transmitted
over the network to every user.
When to use this approach:
Use this option when you have a small number of simultaneous users, no need to get to your data from a remote location, and a good backup method. In terms of hardware savings, this option can prove to be very economical, particularly for small applications.
Option 2. Client/Server Architecture
In this scenario, an SQL Server is installed on the network, and the application is written to use a database stored on the server.
Pros:
Performance
A dedicated SQL Server can provide data to hundreds of users simultaneously.
Scalability
The number of users can grow into the hundreds. Also, the same SQL Server can be used to host several different applications, allowing you to share data easily between applications.
Reliability
SQL Server has several built-in safeguards that protect your data in the event of a server crash. Often, backups can be scheduled so that even in the event of a catastrophe, you won't even lose an entire day's worth of data.
Cons:
Expense
Requires
purchasing a dedicated computer, server software, and providing for periodic
maintenance and updates. Typically,
a network engineer can cost between $50 and $150 per hour to perform these
duties.
Infrastructure Requirements
If you're not already using a server on your network, this is a big step, and requires a lot of changes to how you do things on your network. Again, there is the cost of the network engineers to set this up.
When to use this approach:
Use this option when you expect to have large numbers of users working with your data simultaneously, or when you already have the infrastructure in place. This option can also be combined with other applications, including a Web Application, so that you can use the same data in all of your applications.
In this scenario, your application is written as a series of web pages, and you access the application through the Internet. The database and web server can be located either on your network or offsite, although locating them on your network introduces all of the same issues as using a client/server architecture. The Web Application is essentially another variation of the client/server approach.
Pros:
Remote access
You can get to your application from any Internet connection, and using any type of computer.
Freedom from network maintenance
You won't need to set anything up on your network, and you won't have to fix it if anything goes wrong with the server.
Cons:
Data is not in-house
If anything happens to the hosting company or their servers, you need to make sure you have a backup of your data, and a plan in place to get your application running on another server.
Less sophisticated user interface
Web applications simply can't have as sophisticated a user interface as a desktop application for the same amount of effort. This may or may not be an issue, depending upon what type of application you're using.
Ongoing expense
Hosting companies charge periodically for their service. Prices range from $50 / month and up. This is still probably cheaper than bringing in a network engineer periodically, but be aware that this expense will go on forever, or at least for the life of your application.
When to use this approach:
Use this option when you need to get to your data from many remote locations, or when using different platforms of computers. Also, this approach is only appropriate when data entry can be done effectively with web forms.