The Cold Fusion Web Database Construction Kit
Any application that involves data manipulation of some kind uses a database. A thorough understanding of databases and what they do is a prerequisite to developing Cold Fusion applications.
In this chapter, I introduce you to databases, teach you the terminology used in the world of databases, and explain the differences between different database technologies.
Say that you have just been assigned a project. You must create and maintain a list of all employees in your company and their phone extensions. The list changes constantly because new employees start working for the company, others leave, and extensions and job titles change, too.
What do you use to maintain this list? Your first thought might be to use a word processor. You could create the list, one employee per line, and manually insert each employee's name so that the list is alphabetical and usable. Your word processor provides you with sophisticated document-editing capabilities, so adding, removing, or updating employees is no more complicated than editing any other document.
Initially, you might think you have found the perfect solution--that is until someone asks you to sort the list by department and then alphabetically within each department. Now you must re-create the entire list, again sorting the names manually and inserting them in the correct sequence. You end up with two lists to maintain. You must add new employees' names to both lists and remove names of employees leaving the company from both lists as well. You also discover that correcting mistakes or simply making changes to your list has become more complicated because you have to make every change twice. Still, the list is manageable. You have only the two word processor documents to be concerned with, and you can even open them both at once and make edits simultaneously.
Okay, the word processor is not the perfect solution, but it is still a manageable solution--that is, until someone else asks for the list to be sorted by extension. As you fire up your word processor yet again, you review the entire list-management process in your mind. New names must now be added to three lists. Likewise, any deletions must be made to all three lists. If an extension changes, then you need to change just the extension on two lists, and you need to re-sort the third as well.
And then, just as you think you have the entire process worked out, your face pales, and you freeze. What if someone else wants the list sorted by first name? And then what if yet another department needs the list sorted by job title. You panic, break out in a sweat, and tell yourself, "There must be a better way!"
Okay, so this example is a bit extreme, but the truth is, a better way really does exist. You need to use a database.
Let me start with a definition. A database is simply a structured collection of similar data. The important words here are "structured" and "similar," and the employee list is a perfect example of both.
Imagine the employee list as a two-dimensional grid or table, as shown in Figure 6.1. Each horizontal row in the table contains information about a single employee. The rows are broken up by vertical columns. Each column contains a single part of the employee record. The First Name column contains only employees' first names, and every employee's first name is listed in this column, one in each row. Similarly, the Last Name column contains only employee's last names.
Figure 6.1 Databases view data in an imaginary two-dimensional grid.
The employee list contains "similar" data for all employees. Every employee's record, or row, contains the same type of information. Each has a first name, last name, phone extension, department, and job title. The data is also "structured," in that the data can be broken into logical columns, or fields, that contain a single part of the employee record.
Here's the rule of thumb: Any list of information that can be broken into similar records of structured fields should probably be maintained in a database. Product prices, phone directories, invoices, invoice line items, and vacation schedules are all database candidates.
You probably use databases all the time, often without even knowing it. If you use a software-based accounting program, you are using a database. All accounts payable, accounts receivable, vendor, and customer information is stored in databases. Scheduling programs use databases to store appointments and to-do lists. Even e-mail programs use databases for directory lists and folders.
These databases are designed to be hidden from you, the end user. You never add accounts receivable invoice records into a database yourself. Rather, you enter information into your accounting program, and it adds records to the database.
Now that you understand what a database is, I must clarify some important database terms for you. In the SQL world (you learn about SQL in depth in Chapter 10, "SQL Data Manipulation"), this collection of data is called a table. The individual records in a table are called rows, and the fields that make up the rows are called columns. A collection of tables is called a database.
Picture a filing cabinet. The cabinet houses drawers, each of which contains groups of data. The cabinet is a means of keeping related but dissimilar information in one place. Each cabinet drawer contains a set of records. One drawer may contain employee records, whereas another drawer may contain sales records. The individual records within each drawer are different, but they all contain the same type of data, in fields.
As shown in Figure 6.2, the filing cabinet is the database, a collection of drawers or tables, containing related but dissimilar information. Each drawer contains one or more records, or rows, made up of different fields, or columns.
Each row in a database table is made up of one or more columns. Each column contains a single piece of data, part of the complete record stored in the row. When a table is created, each of its columns needs to be defined. Defining columns involves specifying the column name, its size, and data type. The data type specifies what data may be stored in a column.
Data types specify the characteristics of a column and instruct the database as to what kind of data may be entered into it. Some data types allow the entry of free-form alphanumeric data. Others restrict data entry to specific data, like numbers, dates, or true or false flags. A list of common data types is shown in Table 6.1.
Figure 6.2 Databases store information in tables, columns, and rows similarly to how records are filed in a filing cabinet.
| Data Type | Restrictions | Typical Use |
| Character | Upper- and lowercase text, numbers, symbols | Names, addresses, descriptions |
| Numeric | Positive and negative numbers, decimal points | Quantities |
| Date | Numbers, date or time formatting characters | Dates, times |
| Money | Positive and negative numbers, decimal points | Prices, billing amounts, invoice line items |
| Boolean | Yes and No, or True and False | On/off flags, switches |
| Binary | Non-text data | Pictures, sound, and video data |
Most database applications provide a graphic interface to database creation, thus enabling you to select data types from a list. Microsoft Access uses a drop-down list box, as shown in Figure 6.3, and provides a description of each data type as well.
Figure 6.3 Microsoft Access uses a drop-down list box to enable you to select data types easily.
You use data types for several reasons, instead of just entering all data into simple text fields. One of the main reasons is to control or restrict the data that a user can enter into that field. A field that has to contain a person's age, for example, could be specified as a numeric field. This way, the user cannot enter characters into it; only the digits 0 through 9 would be allowed. This restriction helps ensure that no invalid data is entered into your database.
Different data types are also used to control how data is sorted. Data that is entered in a text field is sorted one character at a time, as if it were left justified. The digit 0 comes before 1, which comes before 9, which comes before a, and so on. As each character is evaluated individually, a text field containing the number 10 is listed after 1 but before 2 because 10 is greater than 1 but less than 2, just as a0 is greater than a but less than b. If the value being stored in this column is a person's age, correctly sorting the table by that column would be impossible. Data entered into a numeric field, however, is evaluated by looking at the complete value rather than a character at a time. So 10 is considered greater than 2 instead of less than 2. Figure 6.4 shows you how data is sorted if numbers are entered into a text field.
Figure 6.4 Unless you use the correct data type, data might not be sorted the way you want.
The same is true for date fields. Dates in text fields are evaluated one character at a time, from left to right. The date 02/05/98 is considered less than the date 10/12/97 because the first character of the date 02/05/98, the digit 0, is less than the first character of the date 10/12/97, the digit 1. If the same data is entered in a date field, the database evaluates the date as a complete entity and therefore sorts the dates correctly.
The final reason for using different data types is the storage space that plain-text fields take up. A text field that is large enough to accommodate up to 10 characters takes up 10 bytes of storage. Even if only 2 characters are entered into the field, 10 bytes are still stored. The extra space is reserved for possible future updates to that field. Some types of data can be stored more efficiently when not treated as text. For example, a 4-byte numeric field can store numeric values from 0 to over 4 billion! Saving 4 billion in a text field requires 10 bytes of storage. Similarly, a 4-byte date/time field can store the date and time with accuracy to the minute. Storing that same information in a text field would take a minimum of 14 bytes or as many as 20 bytes depending on how the data is formatted.
NOTE: Different database applications use different terms to describe the same data type. For example, Microsoft Access uses the term text to describe a data type that allows the entry of all alpha- numeric data. Microsoft SQL Server calls this same data type char and uses text to describe variable length text fields. After you determine the type of data you want a column to contain, refer to your database application's manuals to make sure that you use the right term.
When you're designing a database, you should give careful consideration to data types. You usually cannot change the type of a field after the table is created. If you do have to change the type, you typically have to create a new table and write routines to convert the data from one table to the next.
Planning the size of fields is equally important. With most databases, you cannot change the size of a field after the table is created. Getting the size right the first time and allowing some room for growth can save you much aggravation later on.
CAUTION: When you're determining the size of data fields, always try to anticipate future growth. If you're defining a field for phone numbers, for example, realize that not all phone numbers follow the three-digit area code plus seven-digit phone number convention that is used in the USA. Paris, France, for example, has eight-digit phone numbers, and area codes in small towns in England can contain four or five digits.
With some databases, you can define your own data types. This way, you can specify exactly what data is allowed and how it should be formatted.
Take a look at the employee list again. Suppose that you have to track your employees' social security numbers with their records. You can't use a numeric field here because you need to save the social security numbers with the dashes in their correct locations. You could create a text field that stores 11 characters, the length of a social security number with dashes in the right places, and require the users to enter the data exactly as you want to save it. Doing so, however, would require that you ensure that only numbers are entered and that the dashes are in the correct places. The database does not do this validation automatically for you because, as far as it is concerned, the field is a text field and all text is allowed.
A better solution would be for you to create a data type specifically for social security numbers. Under the hood, this data type actually is an 11-character text field. But unlike a simple text field, this new data type has the dashes exactly where you want them, in positions four and seven, and allowed data would be limited to digits only.
After you define the new data type, you can use it whenever you need to store a social security number in a table. You can simply specify this new data type as the column's data type without having to detail the column's characteristics over again.
NOTE: Not all database applications support custom data types, and no fixed set of rules governs those that do. Consult your database's manuals to determine whether custom data types are supported and, if so, how to use them.
Now, back to the example. At this point, you have determined that a database will make your job easier and might even help preserve your sanity. You create a table with columns for employee first name, employee last name, Social Security number, department, job title, and extension. You enter your employee list data into the table, one row at a time, and are careful to put the correct data in each column.
Next, you instruct the database application to sort the list by employee last name. In a second or less, the list is sorted, and you print it out. Impressed, you try additional sorts, by first name, and by phone extension. The results of these sorts are shown in Figure 6.5.
Figure 6.5 Data entered once into a Microsoft Access table can be sorted any way you want.
You now have two or more lists, but you had to enter the information only once, and because you were careful to break the employee records into multiple columns, you can sort or search the list in any way needed. Whenever you add or delete employee names or make changes to employees' records, you just need to reprint the lists. The new or changed data is automatically sorted for you.
"Yes," you think to yourself, "this really is a better way."
You have just seen a practical use for a database. The employee list is a simple database. It involves a single table and a small set of columns. Most well-designed database applications require many tables and ways to link them together. To let me introduce relational databases, revisit the employee list once again.
Your first table was a hit. You have been able to accommodate any requests for lists, sorted any way anyone could possibly need. But, just as you are beginning to wonder what you're going to do with all your newfound spare time, your human resources department has a brainstorm. "Now that we have a database of all our employees, and it is so easy to manage and maintain, could we add their vacation schedules, too? That would be a real time-saver for us."
"No problem," you say. You open your database application and modify your table. You add two new columns, one called Vacation Start Date and one called Vacation End Date. Now, when an employee schedules a vacation, you can simply fill in the dates in the appropriate columns. You even create a report of all upcoming scheduled vacations. Once again, you and your database have saved the day, and all is well.
Or so you think. Just when things are looking good, you get a call from an employee who wants to schedule two vacation dates, one in June and one in October. Your database has room for only one set of dates. Entering the October vacation dates will overwrite the June dates!
You think for a few moments and come up with two solutions to this new problem. The first solution is simply to add two more columns to your table: Vacation 2 Start Date and Vacation 2 End Date. You can enter the second set of vacation dates into these new columns.
This solution, you realize, is not a long-term solution at all. Inevitably, someone else will need space for three, four, or even more sets of dates. Adding all these extra columns that will not be used by most records is a tremendous waste of disk space. Furthermore, data manipulation becomes extremely complicated if data is stored in more than one column. If you need to search for who has booked vacation time on a specific date, you have to search multiple columns. This situation greatly increases the chances of reporting incorrect results. It also makes sorting data impossible because databases sort data one column at a time, and you have data that must be sorted together spread over multiple columns.
NOTE: An important rule in database design is that, if columns are seldom used by most rows, they probably don't belong in the table.
Your second solution is to create additional rows when an employee wants to book another set of dates. With this solution, you can add as many sets of dates as needed without creating extra columns.
This solution, though, is not workable. Although it does indeed solve the problem of handling more than a predetermined number of vacation dates, in doing so it introduces a far greater problem. Adding additional rows requires repeating the basic employee information, like name and phone number fields, over and over, once for each row.
Not only does reentering this information waste storage space, it also greatly increases the likelihood of your being faced with conflicting data. If an employee's extension changes, for example, you have to make sure to change every row that contains that employee's data. Failing to update all rows would result in queries and searches returning conflicting results. If you do a search for an employee and find two rows, both of which have different phone extensions, how do you know which is correct?
This problem is probably not overly serious if the conflicting data is phone extensions. But imagine that the problem is customer billing information. If you reenter a customer's address with each order and then the customer moves, you could end up shipping orders to an incorrect address.
You should avoid maintaining multiple live copies of the same data whenever possible.
NOTE: Another important rule in database design is that data should never be repeated unnecessarily. As you multiply the number of copies that you have of the same data, the chances of data entry errors also multiply.
One point worth mentioning here is that the "never duplicate data" rule does not apply to backups of your data. Backing up data is incredibly important, and you can never have too many backup plans. The rule of never duplicating data applies only to live data, that is, data to be used in a production environment on an ongoing basis.
The solution to your problem is to break the employee list into two separate tables. The first table, the employee list, remains just that, an employee list. To link employees to other records, you add one new column to the list, a column containing a unique identifier for each employee. It might be an employee ID, social security number, or just a sequential value that is incremented as each new employee name is added to the list. The important thing is that no two employees have the same ID.
TIP: Generally, never reusing record unique identifiers is a good idea. If an employee with ID number 105 leaves the company, for example, that number should never be reassigned to a new employee. This policy guarantees that there are no chances of the new employee record getting linked to data that belonged to the first employee.
Next, you create a new table with just three columns: an Employee ID column, a Vacation Start Date column, and a Vacation End Date column. As long as no employees have vacations booked, the second table, the vacation table, remains empty. When an employee books vacation dates, you add a row to the vacation table. The row contains the employee ID that uniquely identifies this specific employee and the vacation start and end dates.
The point here is that no employee information is stored in the vacation table except for that employee ID, which is the same employee ID assigned in the employee list table. So how do you know which employee a record is referring to when vacation dates are reported? The employee information is retrieved from the employee list table. When displaying rows from the vacation table, the database relates the row back to the employee list table and grabs the employee information from there. This relationship is shown in Figure 6.6.
Figure 6.6 Relational databases link tables together, enabling you to link many records in one table to a master record in another.
This database design is called a relational database. Using a relational database, you can store data in different tables and then define links, or relationships, to find associated data stored in other tables in the database. In this example, an employee who booked vacations in both June and October would have two rows in the employee vacation table. Both of these rows contain the same employee ID, and therefore both refer back to the same employee record in the employee list table.
The process of breaking up data into multiple tables to ensure that data is never duplicated is called normalization.
The database term for the column or columns that contain values that uniquely identify each row is primary key. A primary key is usually a single column but need not be.
The following are the only two requirements for primary keys:
When your human resources department head asks for a list of all upcoming vacations sorted by employee, you can instruct the database to build the relationship and retrieve the required data. The employee list table is scanned in alphabetical order, and as each employee is retrieved, the database application checks the employee vacation table for any rows that have an employee ID that matches the current primary key. You can even instruct the database to ignore the names of employees who have no vacation time booked at all and retrieve only those who have related rows in the employee vacation table.
NOTE: Not all data types can be used as primary keys. You cannot use columns with data types for storing binary data like sound or images, variable length records, and OLE links as primary keys.
The employee ID column in the employee vacation table is not a primary key. The values in that column are not unique if any employee books more than one vacation. All records of a specific employee's vacations contain the same employee's ID. The employee ID is a primary key in a different table, the employee list table. The database term for this kind of key is foreign key. A foreign key is a non-unique key whose values are contained within a primary key in another table.
To see how the foreign key is used, assume that you have been asked to run a report to see who will be out of the office during October. To do so, you instruct the database application to scan the employee vacation table for all rows that have vacation dates in October. Then the database application uses the value in the employee ID foreign key field in the vacation table to find the name of the employee in the employee table by that table's primary key. This relationship is shown in Figure 6.7.
Figure 6.7 The values in a foreign key in one table are always primary key values in another table, allowing tables to be "related" to each other.
The relational database model helps overcome a scalability problem. A database that can handle an ever increasing amount of data without having to be redesigned is said to "scale well." When you're designing databases, you should always take scalability into consideration.
Now you've made a significant change to your original database, but what you've created is a manageable and scaleable solution. Your human resources department is happy once again, and the employee booking two vacations at once (and who obviously has far less work to do than you) is happy, too. Once again, your database management skills save the day.
The type of relationship I have been describing to this point is called a one-to-many relationship. This kind of relationship allows an association between a single row in one table and multiple rows in another table. In the example, a single row in the employee list table may be associated with many rows in the employee vacation table. The one-to-many relationship is the most common type of relationship in a relational database.
Two other types of relational database relationships also exist: the one-to-one relationship and the many-to-many relationship.
The one-to-one relationship allows a single row in one table to be associated with no more than one row in another table, as shown in Figure 6.8. This relationship type is used infrequently. The reality is that, if you run into a situation in which a one-to-one relationship is called for, you should probably revisit the design. Most tables that are linked with one-to-one relationships can simply be combined into one large table.
Figure 6.8 One-to-one relationships create links between a single row in one table and a single row in another.
The many-to-many relationship is another type of relationship that is used infrequently. The many-to-many relationship allows one or more rows in one table to be associated with one or more rows in another table. This kind of relationship is usually the result of bad design. Most many-to-many relationships can be more efficiently managed with multiple one-to-many relationships.
Database applications make extensive use of a table's primary key whenever relationships are used. It is therefore vital that accessing a specific row by primary key value be a fast operation. When data is added to a table, you have no guarantee that the rows are stored in any specific order. A row with a higher primary key value possibly can be stored before a row with a lower value. You should make no assumptions about the actual storage location of any data.
Now take another look at the relationship between the employee list table and the employee vacation table. To know who has vacation time booked in October, you have the database scan the employee vacation table, and only rows in which October is between the vacation start and end date are selected. This operation, however, returns only the employee ID, the foreign key value. To determine which employee this row is referring to, you have the database check the employee list table, and a single row is selected, the row that has this employee ID as its primary key value.
To find a specific row by primary key value, you could have the database application sequentially read through the entire table. If the first row stored is the one needed, then the sequential read would be terminated. If not, then the next row would be read and then the next row, until the desired row is retrieved.
This process might work for small sets of data. Sequentially scanning hundreds, or even thousands, of rows is a relatively fast operation, particularly for a fast computer with plenty of available system memory. But as the number of rows increases, so does the time it takes to find a specific row.
The problem of how to find specific data quickly in an unsorted list is not limited to databases. Suppose that you're reading a book on mammals and are looking for information on cats. You could start at the first page of the book and read every word looking for the word cat. This approach might work if you have just a few pages to search through. But as the number of pages grows, so does the difficulty of the task of locating specific words and the likelihood that you will make mistakes and miss references.
To solve this problem, books have indexes. The purpose of a book's index is to allow rapid access to specific words or topics spread throughout the book. Although the words or topics referred to by the index are not in any sorted order, the index itself is. Cat is guaranteed to appear in the index somewhere after bison but before cow. To find all references to cat, you would first search the index. Searching the index is a quick process because the list is sorted. You don't have to read as far as dog if the word you're looking for is cat. When you find cat in the index list, you also find the page numbers where cats are discussed.
Databases use indexes in much the same way. Database indexes serve the same purpose as book indexes--specifically, allowing rapid access to unsorted data. Just as book indexes list words or topics alphabetically to facilitate the rapid location of data, so do database table indexes list the values they index in a sorted order. And just as book indexes list page numbers for each index listing, database table indexes list the physical location of the matching rows, as shown in Figure 6.9. After the database application knows the physical location of a specific row, it can retrieve that row without having to scan every row in the table.
Figure 6.9 Database indexes are lists of rows and where they appear in a table.
However, two important differences exist between an index at the back of a book and an index to a database table. First, an index to a database table is dynamic. This means that every time a row is added to a table, the indexes are modified automatically to reflect this change. Likewise, if a row is updated or deleted, the index is dynamically updated to reflect this change. This way, the index is always up-to-date and always useful. Second, unlike a book index, the table index is never explicitly browsed by the end user. Instead, when the database application is instructed to retrieve data, it uses the indexes to determine how to complete the request quickly and efficiently.
The indexes are maintained by the database application and are used only by the database application. You never actually see the index in your database, and in fact most modern database applications hide the actual physical storage location of the indexes altogether.
When you create a primary key for a table, it is automatically indexed. The database assumes that the primary key will be used constantly for lookups and relationships and therefore does you the favor of creating that first index automatically.
With the employee vacation list, when you run a report to determine who is off during October, the following process occurs. First, the database application scans the employee vacation table to find any rows that have vacation dates that fall anytime in October. This process returns the employee IDs of any employees with vacations booked for that time. Next, the database application retrieves the matching employee name for each vacation table row it has retrieved. To find the matching employee record in the employee list table, it searches the primary key index. The index contains all employee IDs in order and, for each ID, lists the physical location of the required row. After the database application finds the correct index value, it obtains a row location from the index and then directly jumps to that location in the table. Although this process may look overly involved on paper, it actually all happens very quickly and in less time than any sequential search would take.
Now revisit your employee database. Your company has grown dramatically, and the number of employee names in your employee table has therefore grown, too. Lately, you have begun to notice that operations are taking longer than they used to. The alphabetical employee list report takes considerably longer to run, and as more names are added to the table, the performance drops even more. The database design was supposed to be a scaleable solution, so why is the additional data bringing the system to its knees?
The solution here is the introduction of additional indexes. The database application automatically creates an index for the primary key. Any additional indexes have to be explicitly defined. To improve sorting and search by last name, you just need an index on the Last Name column. With this index, the database application can instantly find the rows it is looking for without having to sequentially read through the entire table.
The maximum number of indexes that a table can have varies from one database application to another. Some databases have no limit at all and allow every column to be indexed. This way, all searches or sorts can benefit from the faster response time.
CAUTION: Some database applications limit the number of indexes that any table can have. Before you create dozens of indexes, check to see whether you should be aware of any limitations.
Before you run off and create indexes for every column in your table, you have to realize the trade-off here. I explained earlier that, unlike an index at the end of a book, a database table index is dynamic. As data changes, so do the indexes. And updating indexes takes time. The more indexes a table has, the longer write operations will take. Furthermore, each index takes up additional storage space, so unnecessary indexes waste valuable disk space.
When should you create an index then? The answer is entirely up to you. Adding indexes to a table makes read operations faster and write operations slower. You have to decide the number of indexes you create and which columns to index for each application individually. Applications that are used primarily for data entry have less need for indexes. Applications that are used heavily for searching and reporting can definitely benefit from additional indexes.
You should index your employee list table, for example, by last name, as you will often be sorting and searching by your employees' last names. You will seldom need to sort by employees' first names, so you don't have any justification for indexing the First Name column. You still can search or sort by first name if the need arises, but the search takes longer than a last name search. Likewise, the phone extension or department columns might be candidates for indexing; whether you add indexes is up to you and how you determine the application will be used.
With some database applications, you can create and drop indexes as needed. You may decide that, before running a batch of infrequently used reports, you want to create additional temporary indexes. They enable you to run your reports faster. After you finish running the reports, you can drop the new indexes, restoring the table to its previous state. The only downside to doing so is that, while the additional indexes are present, write operations are slower. This slowdown may or may not be a problem; again, the decision is entirely up to you.
Often, you may find yourself sorting data on more than one column. An example could be indexing on last name plus first name. Your employee list table may have more than one employee with the same last name. To correctly display the names, you need to sort on last name plus first name. This way, Jack Smith always appears before Jane Smith, who always appears before John Smith, as shown in Figure 6.10.
Figure 6.10 Indexing data on more than one column is an effective way to achieve the sort order you need.
Indexing on two columns, like the last name plus first name, is not the same as creating two separate indexes, one for last name and one for first name. You have not created an index for the First Name column itself, and searches or sorts on the First Name column cannot use this index. The index is of use only when you're searching or sorting the Last Name column.
As with all indexes, indexing more than one column may often be beneficial, but this benefit comes with a cost. Indexes that span multiple columns take longer to maintain and take up more disk space. Here, too, you should be careful only to create indexes that are needed and justifiable.
All that I have described to this point applies to all databases equally. The basic fundamentals of databases, tables, keys, and indexes are supported by all database applications. At some point, however, databases start to differ. They may differ in price, performance, features, security, scalability, and more.
One decision that you should make very early in the process is whether to use a shared-file-based database, such as Microsoft Access, or a client/server database application, such as Microsoft SQL Server. Each has advantages and disadvantages, and the key to determining which will work best for you is understanding the difference between shared-file-based applications and client/server systems.
Databases such as Microsoft Access and Borland dBASE are shared-file-based databases. They store their data in data files that are shared by multiple users. These data files are usually stored on network drives so that they are easily accessible to all users who need them, as shown in Figure 6.11.
Figure 6.11 The data files in a shared-file-based databases 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 that you read is read by Microsoft Access running on your computer. Likewise, any data changes are made locally by the copy of Access that is running on your computer.
Considering this point is very important when you're evaluating shared-file-based database applications. The fact that every running copy of Microsoft Access has the data files open locally has serious implications, as you can see in the following list:
I'm not saying that you should never use shared-file-based databases. The following are some compelling reasons to use this type of database:
Databases such as Microsoft SQL Server and Oracle are client/server-based 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 that is called the database server. In the case of Microsoft SQL Server, it is a computer running Windows NT and the SQL Server software.
Only the server software interacts with the data files. All requests for data, data additions and deletions, or 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 with which the user interacts. If you request a list of employees sorted by last name, for example, the client software submits that request over the network to the server software. The server software processes the request; filters, discards, and sorts data as needed; and then sends back the results to your client software. This process is illustrated in Figure 6.12.
Figure 6.12 Client/server databases allow clients to perform database operations that are processed by the server software.
All this action happens 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 is stored.
In the following ways, client/server-based database servers overcome the limitations of shared-file-based database applications:
As you can see, client/server databases are more secure and more robust than shared-file databases. But all this extra power and security comes with a price.
Now that you have learned the various types of database systems that you can use, how do you determine which is the right one for your application?
Well, unfortunately, this question has no simple answer. To make this decision, you really need to review your application needs, the investment you are willing to make into the system, and what 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 two feet?
Do you have any database expertise or experience? If yes, what database systems are you familiar with?
Do you have database programmers or administrators in house? If yes, what systems are they familiar with?
How many users do you anticipate using the system concurrently?
How many records to 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. And getting the job done right the first time will save you time, money, and aggravation later on.
In this chapter, you learned what databases are and what they are used for. I introduced and explained important database terminology. You also learned what a relational database is and how it is used to help normalize data.
For more information about topics mentioned in this chapter, see the following chapters:
© Copyright, Macmillan Computer Publishing. All rights reserved.
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}