During this portion of the database-design process, you'll define a preliminary table list that you'll use to identify and establish the tables for the new database. You'll use three procedures to develop this list. The first involves using the preliminary field list, the second involves using the list of subjects you gathered during the interviewing process, and the third involves using the mission objectives you defined at the beginning of the database-design process. You'll then move on to build the structure of each table using fields from the preliminary field list.
The process of defining the tables for the database begins with a review of the preliminary field list. Your objective is to identify subjects that are implied by the fields on the list.
You may wonder why you're reviewing the preliminary field list instead of starting with the list of subjects. The list of subjects does seem to be a more intuitive place to start. After all, you've carefully built this list during the interview process, and you've been influenced by the conversations you've had with the users and management. Surely, all of this has helped you identify every subject that needs to be represented in the database. You may be correct, but you could have a minor problem if you're wrong: missing tables.
Studying the fields on the primary field list helps you identify subjects from an unbiased viewpointyou're letting the fields "talk" to you. It's crucial that you now look at this list as objectively as possibleas though you've never seen it beforewithout any of the biases you've assimilated during the interview process. This enables you to see how certain groups of fields suggest specific subjects, some of which may not have been identified during the interview process. You can also use the preliminary field list to verify many of the subjects on the list of subjects. Using the preliminary field list in these ways allows you to crosscheck your previous work and helps you ensure that the new database structure includes all of the necessary subjects.
As you review the preliminary field list, ask yourself whether a certain set of fields defines or describes a particular subject. Move on to another set of fields if nothing readily comes to mind. When you can infer a subject from the field in the list, enter that subject on a new preliminary table list. Figure 7.1 shows a partial sample of a preliminary field list and illustrates how a subject can be suggested by a set of fields.
Continue your review until you've scanned all the fields and identified as many subjects as possible. Be sure to add each subject you identify to the preliminary table list. This list will grow as you work with the list of subjects and mission objectives. Figure 7.2 shows an example of the first version of a preliminary table list.
Now, create a second version of the preliminary table list by merging the list of subjects (created during the interviews with users and management) with the first version of the preliminary table list (compiled by studying the preliminary field list). This new version contains a more complete list of tables. Merging the two lists is a three-step process, which involves resolving duplicate items, resolving items that represent the same subject, and combining the remaining items together into one list.
Start this step by reviewing and crosschecking each item on the list of subjects against the items on the preliminary table list. Your objective here is to identify duplicate items, which are items on the list of subjects that already appear on the preliminary table list. You must be very careful how you resolve the duplicate items that you find. Begin by determining whether the items represent different subjects, despite the fact that they share the same name. (Use your interview notes as necessary to help you make the determination.) If they do represent different subjects, rename each item so that it accurately identifies the subject it represents and then add both items to the preliminary table list; otherwise, determine whether they truly represent the same subject. When you determine that both items do represent the same subject, cross out the item on the list of subjects and keep the one that appears on the preliminary table list. Then resume the review until you've examined all of the items on both the list of subjects and the preliminary table list. Let's take a look at an example of this process.
Assume that you're developing a database for an equipment rental business, and you're working with the list of subjects and the preliminary table list shown in Figure 7.3.
As you review these lists, you discover two duplicate items: "Equipment" and "Rental Agreements." These items warrant further examination, so you start with "Equipment" and try to determine whether each occurrence represents a different subject. In reviewing your interview notes, you find that "Equipment" on the list of subjects represents items such as tools, appliances, and audiovisual equipment. Then you remember that "Equipment" on the preliminary table list also includes trucks, vans, and trailers. You review your interview notes further and discover that vehicle rentals are treated differently from "regular" equipment rentals. Therefore, each occurrence of "Equipment" does represent a different subject. You resolve the duplication by keeping one occurrence of "Equipment" and renaming the other "Vehicles." You then list both items on the preliminary table list.
Now you go through the same process with "Rental Agreements." Fortunately, you discover that both occurrences share exactly the same meaning. The only thing you have to do in this case is cross out "Rental Agreements" on the list of subjects. Now you can continue your review until you've inspected each item on the list of subjects. Figure 7.4 shows the revised list of subjects and the preliminary table list.
Your objective during this step of the merge process is to determine whether an item on the list of subjects and an item on the preliminary table list represent the same subject even though they have different names. When you identify such a set of items, select the name that best represents the subject and use it as the sole identifier for that subject. Then deal with the name in this manner:
If the name you've selected already appears on the preliminary table list, cross out its counterpart on the list of subjects.
If the name appears on the list of subjects, remove its counterpart on the preliminary table list and replace it with the name from the list of subjects.
Repeat this process until you've covered all the items on the list of subjects.
Continuing with the equipment rental business example, assume you've discovered that "Clients" and "Employees" on the list of subjects and "Customers" and "Sales Reps" on the preliminary table list represent (respectively) the same subject (see Figure 7.4). Deciding to deal with "Clients" and "Customers" first, you review your interview notes and determine that "Customers" is the name that best represents both the people and the organizations that rent equipment from the business. You then resolve the duplication by keeping "Customers" and crossing out "Clients." Moving on to the next set of duplicate items, you decide to keep "Employees" and discard "Sales Reps" because you believe that "Employees" best describes those people who are employed by the business, regardless of their position. Figure 7.5 shows a revised version of both lists and the resolution of the duplicate items.
The final step of this process is the easiest of the three. All you do is add the remaining items from the list of subjects to the preliminary table list. Then throw away the list of subjectsyou won't need it anymore. The list that remains becomes the second version of the preliminary table list. That's all there is to it! Figure 7.6 shows the second version of the preliminary table list, which is the result of merging the two lists shown in Figure 7.5.
In this third and final procedure, you use the mission objectives to determine whether you've overlooked any subjects during the previous two procedures. This is your final opportunity to add tables to the preliminary table list.
Start with the first mission objective, and use the subject-identification technique to identify the subjects represented in that statement. Underline each subject you identify and then crosscheck it against the items on the preliminary table list. Use the same techniques here that you used in the previous procedure.
When an item you underlined in a mission objective statement matches an item on the preliminary table list, determine whether the items represent different subjects. If they do, assign an appropriate name to each occurrence and then add each one to the preliminary table list; otherwise, cross out the duplicate item on the mission objective.
When an item you underlined in the mission objective statement has a name that is synonymous with the name of an item on the preliminary table list and both items represent the same subject, select the name that best identifies that subject and use it in the preliminary table list.
When an item you underlined in the mission objective statement represents a new subject, add it to the preliminary table list.
Repeat these steps until you've worked through all the mission objectives. Here's an example of how you use these techniques to review the mission objectives.
Assume that you're designing a database for a flight training school. You're just starting this particular process, and you've just used the subject-identification technique on the following statement:
We need to maintain data on our and their .
You now crosscheck the subjects you identified in this mission objective against the items in the preliminary table list shown in Figure 7.7.
In this case, you cross out "pilots" in the mission objective statement because it already exists on the preliminary table list and it represents the same subject. You then decide to examine "certifications" further, and, after some careful thought, you make these observations:
It does not appear on the preliminary table list.
It doesn't duplicate any item on the preliminary table list.
Its name is not synonymous with any item on the preliminary table list.
It doesn't represent the same subject as any other item on the preliminary table list.
These findings indicate that "certifications" is a new item and should be added to the preliminary table list. So, you add it to the preliminary table list and cross it out on the mission objective statement; this shows you that you've already dealt with this particular item. Figure 7.8 shows the revised version of the preliminary table list.