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 cold Fusion but rather will look at the requirements of the project, and then walk 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 in Access will help you understand what needs to be done when implementing databases on other systems.
Creating Your Database Model
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)1 the date and time of the correspondence the direction of the correspondence (incoming or outgoing) the file name of the correspondence if it is in electronic form indication of which other piece of correspondence it is in response to and a summary 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 z. Determine tables: B’ on ~e 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 stored in each table to see thether 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 of the field 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 relationships 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 (qr 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-provide 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 cat 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 18 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 lcey 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 ~rd and provides a nonarbitrary 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.
up to this point, you have considered the tables only generally indicating the general information to be stored in each and which fields will be used as the primary keys. Now you need to indicate the specific columns (or fields) that will make up each record in each table. For each field you will need to clearly define the data to be stored in the field the data type of information stored in the field and any restrictions on data to be stored in the field
Let’s start with the personal information table, Info, outlined in Table 9.2.
Note that the phone numbers have all been specified as numeric values. This is a matter of preference. As numeric fields they can’t include dashes but they take up less space in the database than text fields. Similarly the SSN field is numeric which is fine because the standard places for dashes can be added when the Social Security number is displayed.
You probably noticed that there are no fields for telephone country codes. The logic ‘here is that the country code specifies both the country name for addresses as well as the telephone cooe for phone numbers. Of course an individual’s phone fax and mobile numbers could be in different countries, but the assumption is made here that this would not be the case.
Next consider the Countries table outlined in Table 9.3
The States table is similar to the Countries table as shown in Table 9.4.
Because you need to account for non-U.S./Canada addresses for which the user can manually enter a state or province name you need to include one record in the States table that users from countries other than the U.S. or Canada can select.
Finally you need to specify fields for the Correspondence table outlined in Table 9.5
The final step in defining your database model is to specify the relationships between tables. For this purpose you will specify individual fields in tables by using the form TableName.
FieldName.Table 9.6 outlines the relationships between tables.
Most of these relationships should be clear. Linking Info. State to States. Code enables the full state name to be included in addresses if needed. Similarly, the Info. Country to Countri es. Code link enables country names to be displayed in addresses, and telephone country codes to be provided with phone numbers. Correspondence. Contact is linked to Info. CustomerID to indicate whom the correspondence is to or from.
The final link from Correspondence. Response To to Correspondence. 10 may not make sense at first. This means that in any given correspondence record, you can’t point to another correspondence record indicating that it is the item being responded to.