Understanding the Various Types of Database Applications

All the information described to this point applies equally to all databases. The basic fundamentals of databases, tables, keys, and indexes are supported by all database applications. At some point, however, databases start to differin price, performance, features, security, scalability, and more.

One decision you should make very early in the process is whether to use a shared-filebased database, such as Microsoft Access, or a client/server database application, such as Microsoft SQL Server and Oracle. Each has advantages and disadvantages, and the key to determining which will work best for you is understanding the difference between shared-filebased applications and client/server systems.

Shared-FileBased Databases

Databases such as Microsoft Access and Visual FoxPro and FileMaker are shared-filebased databases. They store their data in data files that are shared by multiple users. These data files usually are stored on network drives so they are easily accessible to all users who need them, as shown in Figure 5.11.

Figure 5.11. The data files in a shared-filebased database are accessed by all users directly.

When you access data from a Microsoft Access table, for example, that data file is opened on your computer. Any data you read is also read by Microsoft Access running on your computer. Likewise, any data changes are made locally by the copy of Access running on your computer.

Considering this point is important when you're evaluating shared-filebased database applications. The fact that every running copy of Microsoft Access has the data files open locally has serious implications:

• Shared data files are susceptible to data corruption. Each user accessing the tables has the data files open locally. If the user fails to terminate the application correctly or the computer hangs, those files don't close gracefully. Abruptly closing data files like this can corrupt the file or cause garbage data to be written to it.

• Shared data files create a great deal of unnecessary network traffic. If you perform a search for specific expenses, the search takes place on your own computer. The database application running on your computer has to determine which rows it wants and which it does not. The application has to know of all the recordsincluding those it will discard for this particular queryfor this determination to occur. Those discarded records have to travel to your computer over a network connection. Because the data is discarded anyway, unnecessary network traffic is created.

• Shared data files are insecure. Because users have to open the actual data files they intend to work with, they must have full access to those files. This also means that users can either intentionally or accidentally delete the entire data file with all its tables.

This isn't to say that you should never use shared-filebased databases. The following are some reasons to use this type of database:

• Shared-filebased databases are inexpensive. The software itself costs far less than client/server database software. And unlike client/server software, shared-file based databases don't require dedicated hardware for database servers.

• Shared-filebased databases are easier to learn and use than client/serverbased databases.

Client/ServerBased Databases

Databases such as Microsoft SQL Server, Oracle, and MySQL are client/serverbased databases. Client/ server applications are split into two distinct parts. The server portion is a piece of software that is responsible for all data access and manipulation. This software runs on a computer called the database server. In the case of Microsoft SQL Server, it's a computer running Windows and the SQL Server software.

Only the server software interacts with the data files. All requests for data, data additions and deletions, and data updates are funneled through the server software. These requests or changes come from computers running client software. The client is the piece of software the user interacts with. If you request a list of movies sorted by rating, for example, the client software submits that request over the network to the server software. The server software processes the request; it filters, discards, and sorts data as necessary, and sends the results back to your client software. This process is illustrated in Figure 5.12.

Figure 5.12. Client/server data bases enable clients to perform database operations that are processed by the server software.

All this action occurs transparently to you, the user. The fact that data is stored elsewhere or that a database server is even performing all this processing for you is hidden. You never need to access the data files directly. In fact, most networks are set up so that users have no access to the data, or even the drives on which it's stored.

Client/serverbased database servers overcome the limitations of shared-filebased database applications in the following ways:

• Client/serverbased data files are less susceptible to data corruption caused by incorrect application termination. If a user fails to exit a program gracefully, or if their computer locks up, the data files do not get damaged. That is because the files are never actually open on that user's computer.

• Client/serverbased database servers use less network bandwidth. Because all data filtering occurs on the server side, all unnecessary data is discarded before the results are sent back to the client software. Only the necessary data is transmitted over the network.

• End users in a client/server database environment need never have access to the actual physical data files. This lack of access helps ensure that the files are not deleted or tampered with.

• Client/server databases offer greater performance. This is true of the actual database server itself. In addition, client/server databases often have features not available in shared-file based databases that can provide even greater performance.

As you can see, client/server databases are more secure and more robust than shared-file databases but all that extra power and security comes with a price:

• Running client/server databases is expensive. The software itself is far more expensive than shared-file database applications. In addition, you need a database server to run a client/server database. It must be a high-powered computer that is often dedicated for just this purpose.

• Client/server databases are more difficult to set up, configure, and administer. Many companies hire full-time database administrators to do this job.

Which Database Product to Use

Now that you have learned the various types of database systems you can use, how do you determine which is right for your application?

Unfortunately, this question has no simple answer. You really need to review your application needs, the investment you are willing to make in the system, and which systems you already have in place.

To get started, try to answer as many of the following questions as possible:

• Do you have an existing database system in place? If yes, is it current technology that is still supported by the vendor? Do you need to link to data in this system, or are you embarking on a new project that can stand on its own feet?

• Do you have any database expertise or experience? If yes, with which database systems are you familiar?

• Do you have database programmers or administrators in-house? If yes, with which systems are they familiar?

• How many users do you anticipate will use the system concurrently?

• How many records do you anticipate your tables will contain?

• How important is database uptime? What is the cost associated with your database being down for any amount of time?

• Do you have existing hardware that can be used for a database server?

These questions are not easy to answer, but the effort is well worth your time. The more planning you do up front, the better chance you have of making the right decision. Getting the job done right the first time will save you time, money, and aggravation later.

Of course, there is no way you can anticipate all future needs. At some point you might, in fact, need to switch databases. If you ever have to migrate from one database to another, contact the database vendor to determine which migration tools are available. As long as you select known and established solutions from reputable vendors, you should be safe.

TIP

As a rule, shared-filebased databases should never be used on production servers.

Most developers opt to use client/server databases for production applications because of the added security and scalability. But shared-file databases are often used on development and testing machines because they are cheaper and easier to use.

This is a good compromise, and one that is highly recommended if it isn't possible to run client/server databases on all machinesclient/server on production machines, shared-file on development machines (if necessary).