HomeMySQL Page 8 - Database Applications and the Web
Why use a database server? - MySQL
With most of the services on the web being powered by web database applications, it becomes important for any web developer to know how bring together the web and databases to build applications. This article gets you started. It is excerpted from chapter one of the book Web Database Applications with PHP and MySQL, written by Hugh E. Williams & David Lane (O'Reilly, 2004; ISBN: 0596005431).
Why use a complex database server to manage data? There are several reasons that can be explained by contrasting a database with a spreadsheet, a simple text file, or a custom-built method of storing data. A few example situations where a database server should and should not be used are discussed later in this section.
Take spreadsheets as an example. Spreadsheet worksheets are typically designed for a specific application. If two users store names and addresses, they are likely to organize data in a different way and develop custom methods to move around and summarize the data. The program and the data arenít independent: moving a column might mean rewriting a macro or formula, while exchanging data between the two usersí applications might be complex. In contrast, a database server and SQL provide data-program independence, where the method for storing the data is independent of the language that accesses it.
Managing complex relationships is difficult in a spreadsheet or text file. For example, consider what happens if we want to store information about customers: we might allocate a few spreadsheet columns to store each customerís residential address. If we were to add business addresses and postal addresses, weíd need more columns and complex processing to, for example, process a mail-out to customers. If we want to store information about the purchases by our customers, the spreadsheet becomes wider still, and problems start to emerge. For example, it is difficult to determine the maximum number of columns needed to store orders and to design a method to process these for reporting. In contrast, databases are designed to manage complex relational data.
A database server usually permits multiple users to access a database at the same time in a methodical way. In contrast, a spreadsheet should be opened and written only by one user; if another user opens the spreadsheet, she wonít see any updates being made at the same time by the first user. At best, a shared spreadsheet or text file permits very limited concurrent access.
An additional benefit of a database server is its speed and scalability. It isnít totally true to say that a database provides faster searching of data than a spreadsheet or a custom filesystem. In many cases, searching a spreadsheet or a special-purpose file might be perfectly acceptable, or even faster if it is designed carefully and the volume of data is small. However, for managing large amounts of related information, the underlying search structures allow fast searching, and if information needs are complex, a database server should optimize the method of retrieving the data.
There are also other advantages of database servers, including data-oriented and user-oriented security, administration software, portability, and data recovery support. A practical benefit of this is reduced application development time: the system is already built, it needs only data and queries to access the data.
Examples of when to use a database server
In any of these situations, a database server should be used to manage data:
There is more than one user who needs to access the data at the same time.
There is at least a moderate amount of data. For example, you might need to maintain information about a few hundred customers.
There are relationships between the stored data items. For example, customers may have any number of related invoices.
There is more than one kind of data object. For example, there might be information about customers, orders, inventory, and other data in an online store.
There are constraints that must be rigidly enforced on the data, such as field lengths, field types, uniqueness of customer numbers, and so on.
New or consolidated information must be produced from basic, related information; that is, the data must be queried to produce reports or results.
There is a large amount of data that must be searched quickly.
Security is important. There is a need to enforce rules as to who can access the data.
Adding, deleting, or modifying data is a complex process.
Adding, deleting, and updating data is a frequent or complex process.
Examples of when not to use a DBMS
There are some situations where a relational DBMS is probably unnecessary or unsuitable. Here are some examples:
There is one type of data item, and the data isnít searched. For example, if a log entry is written when a user logs in and logs out, appending the entry to the end of a simple text file may be sufficient.
The data management task is trivial and accessing a database server adds unnecessary overhead. In this case, the data might be coded into a web script in the middle tier.