Mastering ColdFusion 4.5 with CD Rom

Chapter 9: Creating Databases and Tables

In this chapter, you will take a brief look at the process of designing a database and implementing it. The subject of designing relational database models is complex and worthy of one more book of its own. We can't hope to do justice to the subject beyond the smallest glimpse into it; a whole body of theory relates to the design of efficient databases. For most small databases, the best way to learn design is simply by doing it.

You will design a small contact-management database in this chapter. You will not be creating the user interface to the database, which could be done using ColdFusion, but rather will look at the requirements of the project, and then walk through the decisions that go into designing the back-end database that would support the application. Finally, you will walk through the implementation of the model you have designed using Microsoft Access. The process of implementing database design differs from database to database, and especially between desktop databases and full-scale client-server database systems. Still, the basic approach used inModel

For the purposes of our exercises in this section, you will walk through the design and implementation of a database to act as the back end in a simple contact-management system. The system will need to provide the following functionality:

• Track a contact's personal information, including name, age, gender, occupation, title, company, address, phone and fax numbers, e-mail address, URL, Social Security number, and customer identification number
• Ensure that state codes, telephone country codes, telephone area codes, and country names are entered in a standardized fashion
• Provide the capability to track incoming and outgoing correspondence with a given contact, including the type of correspondence (mail, fax, phone, or e-mail), the date and time of the correspondence, the direction of the correspondence (incoming or outgoing), the filename of the correspondence if it is in electronic form, indication of which other piece of correspondence it is in response to, and asummary of the correspondence

In designing this database, you need to follow several steps:

1. Gather requirements: In this stage, you look at the data you are tracking and any special issues that need to be addressed in this data.

2. Determine tables: Based on the requirements, you need to look at the specific data-base tables that you will create.

3. Assign primary keys: Each table needs a primary key. You will look at the data to be stored in each table to see whether there is a logical primary key, and, if not, consider how to create one.

4. Define columns: For each table you need to define the fields (or columns) to appear in the table, the data type and other limitations on the type of data stored in the field.

5. Make relationships: In the final stage, you specify relationships between tables. The whole idea behind relational database systems is that they leverage relation-ships between sets of data to create complex models of information.

Gathering Requirements and Determining Tables

We have already outlined the basic database requirements in our specification of application requirements. For instance, you know that you need to track personal information about each contact as well as maintain a historical log of correspondence with pointers to actual electronic documents of correspondence. One central issue is left from that definition: ensuring that "state codes, telephone country codes, telephone area codes, and country names are entered in a standardized fashion."

The standard tactic for doing this in a relational database is to maintain separate lists of countries, states, and telephone codes, and then, rather than include the state name or phone code in the personal information, simply point to an item in the list. Because all personal information records simply point to items in these country and state lists, you can ensure that every record uses a consistent spelling for country and state names, and that any required spelling changes need to be done only one place: in the separate list of countries or states.

This does raise one issue, though: It is reasonable to maintain a separate list of all countries and their telephone codes, and all states and provinces in the U.S. and Canada. However, it seems unwieldy to maintain a list of states and provinces in all countries in the world. Therefore, you will also need to provide a way to handle U.S. and Canadian addresses slightly differently from those of other countries. For U.S. and Canadian addresses, the user should be able to select an entry from the list of states and provinces, but for other addresses, the user should be able to manually enter a province or state.

This is a common tactic in many Web sites that ask for addresses in forms. They pro-vide a drop-down list of all states and provinces in Canada and the U.S. This list, however, includes one entry along the lines of Outside the U.S. and Canada and then provides a blank text field that the user can fill in when they choose this option on the drop-down list. This type of form simply reflects the way in which data may be structured in the underlying database.

The question, then, is how all this translates into tables in your database design. You will need the following databases to store the information required by the application:

Info A table for tracking personal information

Countries A list of countries and their associated telephone codes and international two-letter codes

States A list of states and provinces in the U.S. and Canada and their associated two-letter codes for addressing envelopes

Correspondence A table for tracking correspondence with all contacts

Assigning Primary Keys

For each of these tables, you need to define a primary key. The primary key for a table is a table field that can be used to uniquely identify any record in the table without reference to information in the table. This means that the primary key value needs to be unique for each record in the table.

In some cases, data being tracked in the table may be suited to being the primary key. For instance, an employee identification number will be unique for each employee and therefore can serve as the primary key. However, in many tables, a field will not be suited to being the primary key. In these cases, you need to create an additional field in the table for the purposes of storing an arbitrary primary key.

When using such an arbitrary primary key, different approaches can be taken in deciding what value to use for the key. The simplest approach is to use an automatically incrementing number for the primary key; that is, with each new record, the value of the primary key increments by one, and new records can never have a primary key smaller than any used before. Therefore, if a record in the middle of the table is deleted, its primary key is not freed up for reuse by a new record.

Other approaches include deriving the primary key from information in the table based on a formula that is guaranteed to provide a unique value. This approach has advantages in terms of being able to derive the key for any record and provides a non-arbitrary relationship between the key and the data in the record. For the purposes of small databases, if the data in a table doesn't offer a possible primary key, it is probably easiest to use an automatically-incrementing number for the primary key...