Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design

Defining the Final Table List

Your preliminary table list is as complete as it can be at this point, so you'll now transform it into a final table list . This new list incorporates two elements that are not currently on the preliminary table list: table type and table description . Figure 7.9 shows an example of a final table list.

A table type allows you to classify a table by the role it plays within the database and provides you with a means of identifying tables that function in a similar manner. The table's role determines its type, and there are four table types that you can associate with a given table:

1. A data table represents a subject that is important to the organization and is the primary foundation of the information that the database provides. (You'll learn more about data tables later in this chapter.)

2. A linking table establishes a link between two tables in a many-to-many relationship. (Chapter 10 covers linking tables in more detail.)

3. A subset table contains fields that are related to a particular data table and further describes the data table's subject in a very specific manner. (You'll learn more about subset tables later in this chapter.)

4. A validation table contains relatively static data and is a crucial component of data integrity. (Chapter 11 provides further details on this type of table.)

A table description provides a clear definition of the subject represented by the table and states why the subject is important to the organization. There are certain guidelines that govern how you create a table description, and you'll learn about them later in this chapter. There is a final task you have to perform before you transform your preliminary table list into the final table list: refining the table names .

Refining the Table Names

Naming a table is a more complex affair than you may realize at the moment. As you learned in Chapter 3, a table represents a single subject; therefore, its name must clearly identify the subject it represents. The following guidelines will help you create table names that are clear, unambiguous, descriptive, and meaningful. They will also help ensure that you name your tables in a consistent manner.

Guidelines for Creating Table Names

• Create a unique, descriptive name that is meaningful to the entire organization. Using unique names helps to ensure that each table clearly represents a different subject and that everyone in the organization will understand what the table represents. (If you encounter duplicate table names at this point, resolve the problem using the techniques you learned earlier in this chapter.) Choose names that are descriptive enough to be self-explanatory. "Vehicle Maintenance" is an example of a good, descriptive name. Defining a unique and descriptive name does take some work on your part, but it's well worth the effort in the long run.

• Create a name that accurately, clearly, and unambiguously identifies the subject of the table. Vague or ambiguous names usually indicate that the table represents more than one subject. When you encounter such a name, identify the subjects the table truly represents and then treat each subject as a separate table. "Dates" is a good example of a vague table name. You really don't know what the table represents without referring to its description. For example, assume you're designing a database for an entertainment agency and this table appears in the preliminary table list. Upon seeing this table name, you decide to review your interview notes. You discover that one person says "Dates" represents appointments for client meetings, and another person says it represents booking dates for the agency's stable of entertainers. This table clearly represents two subjects, so you remove "Dates" from the preliminary table list and replace it with two new tables called "Client Meetings" and "Entertainer Schedules."

  Possibly the most vague and ambiguous name you could assign to a table is "Miscellaneous"it doesn't identify a single subject whatsoever. You might occasionally feel compelled to create a "Miscellaneous" table because you just can't figure out what to do with certain fields on your preliminary field list. When that happens, stop, take a break, and then come back and re-examine those fields. Carefully and methodically apply the design techniques you've learned, and you're sure to determine what to do with the fields after all.

• Use the minimum number of words necessary to convey the subject of the table. Everyone in the organization should be able to identify what the table represents without having to read its description. Although your objective is to create a short, succinct table name, avoid using a minimalist approach. "TD_1" is a good example of a name that is exceedingly short. You won't have the slightest idea what this table represents unless you know the meaning of each character in the name. You should also avoid going in the opposite direction as well. "Multiuse Vehicle Maintenance Equipment" is much too long and can easily be shortened to just "Equipment."

• Do not use words that convey physical characteristics. Avoid using words such as "file," "record," and "table" in the table name because they add a level of confusion that you don't need. A table name that includes this type of word is very likely to represent more than one subject. Consider the name "Patient Record." On the surface, this may appear to be an acceptable name. You'll realize, however, that there are potential problems with this name when you take some time to think about what a "patient record" is supposed to represent. The name contains a word that you're trying hard to avoid ("record") and it potentially represents three subjects: " patients ," " doctors ," and "examinations." With this in mind, remove "patients" from the preliminary table list and replace it with three new tables, one for each of the three subjects.

• Do not use acronyms and abbreviations. Acronyms are hard to decipher, abbreviations rarely convey the subject of the table, and both violate the first guideline in this list. Take acronyms, for example. Say you're helping an organization revise its database structure and you encounter a table named "SC." How do you know what the table represents without knowing the meaning of the letters themselves ? The fact is that you can't easily identify the subject of the table. What's more, you may find that the table means different things to different departments in the organization. So, you decide to conduct a brief interview with some of the staff in order to determine what the letters represent. (Now, this is the scary part.) To your disbelief, you discover that the folks in personnel think it stands for "Steering Committees"; the information systems staff believes it to be "System Configurations"; and the people in security insist that it represents "Security Codes." This example clearly illustrates why you should make every effort to avoid using abbreviations and acronyms in a table name.

• Do not use proper names or other words that will unduly restrict the data that can be entered into the table. This guideline will keep you from falling into the trap of creating duplicate table structures. A name such as "Southwest Region Employees," for example, severely restricts the data that you can enter into this table. As the organization grows, how will you deal with employees from other regions ? When the organization begins to hire employees in Washington, Oregon, and Idaho, you'll have to create a "Pacific Northwest Region Employees" table, and you'll have to create a "Western Region Employees " table when the organization begins to hire folks in Arizona, Utah, Nevada, and California.

  Proper database-design principles dictate that you should not create duplicate structures such as these because they can be quite problematic .

  1. Users could have a difficult time retrieving data from all three tables simultaneously .

  2. The person maintaining the database would have the added responsibility of ensuring that the tables are always structurally synchronized. If he adds, modifies, or deletes a field in one table, he must take the same action on all the other tables.

  3. The person maintaining the database would also have the added responsibility of ensuring synchronized data integrity between the tables. He must be able to guarantee that data is completely and accurately transferred from one table to the other when an employee relocates from one region to another.

• Do not use a name that implicitly or explicitly identifies more than one subject. This is one of the most common mistakes you can make with a table name, and it is relatively easy to identify. This type of name typically contains the words "and" or "or" and characters such as the slash (\) or ampersand (&); examples include "Department or Branch" and "Facility\Building." A table with an ambiguous name suggests that you may have not identified the subject clearly or accurately during the analysis and interview processes. You can rectify this problem by reviewing your notes and conducting further analysis and interviews as necessary. Just remember that you must always ensure that each table represents only one subject.

  Another name that falls under this category is "Miscellaneous." (Yes, here's that name again!) A moment ago, I said that this name didn't identify a single subject whatsoever; this is a correct and valid assertion. It is also true, however, that the name implicitly identifies more than one subject; you can't specifically identify the subjects because the name is vague and ambiguous. The Concise Oxford Dictionary, Ninth Edition , defines the word itself as follows :

  Miscellaneous adj . 1. of mixed composition or character. 2. of various kinds.

  You can clearly see the problems that this name creates, so you should not use it as a table name at all. There are certainly good reasons not to do so.

• Do use the plural form of the name. As you know, a table represents a single subject, which can be an object or event. You can take this definition one step further and state that a table represents a collection of similar objects or events. For example, a sales representative wants to maintain data on all of his customers, not just a single one; and a car rental business wants to keep track of all its vehicles, not just the blue BMW. Using the plural form of the table name is a sound idea because it makes clear your intention to refer to a collection. Collections, of course, always take the plural ("Boats," not "Boat"). In contrast, words that identify fields are always singular ("Home Phone," not "Home Phones"). Following this rule will make it easy for you to differentiate between table names and field names in any documentation you create for the database. (As you rename your tables, remember that the plural form of some words does not end in s or es . For instance, the singular and plural forms of "equipment" are exactly the same.)

Use these guidelines to refine each table name on the preliminary table list. When you're finished, this list becomes your final table list and remains so for the duration of the database-design process. Note that the list is "final" only in the sense that you've accounted for all the tables that you identified throughout the entire analysis process. It's very likely that you'll add new tables to this list based on requirements imposed by relationships, data integrity, or other information that you develop.

Indicating the Table Types

As you learned earlier in this chapter, you indicate each table's type on the final table list. Recall that the four classifications you can use to identify the table type are data , linking , subset , and validation .

When you first create your final table list, every item on the list is a data table because it represents a subject that is important to the organization and serves as the primary foundation of the information that the database provides. There will be no linking tables or validation tables on the list because you have not yet defined relationships or imposed data integrity. (You'll address these issues later in the design process.) The list will not contain subset tables because you define them after you assign fields to the data tables.

For the moment, designate each table on the final table list as a data table. You'll assign other table types later as the database-design process continues to unfold.

Composing the Table Descriptions

The table description is another aspect of a table that you record on the final table list. A table description is crucial because it helps everyone understand why a given table exists and why the organization is concerned with collecting the data for that table. In fact, the description must explicitly define the table and state its importance to the organization. It doesn't matter whether the definition comes first or you use more than one sentence to convey this informationboth the definition and the explanation of the table's importance must be in the description. The table description also provides a means of validating the need for a tableif you are unable to explain why a table is important to the organization, then you need to determine when and how the table was identified and whether it really is necessary at all.

Just as you had guidelines to help define table names, you also have a set of guidelines to help you compose a table description that is focused, concise, unambiguous, and clear.

Guidelines for Composing a Table Description

• Include a statement that accurately defines the table. Anyone should easily be able to determine the identity of the table from its description without any confusion or ambiguity. Here's an example of a poor definition for a table named "Suppliers" in a bakery database. As you can see, it's not very accurate.

  Suppliersthe companies that supply us with ingredients and equipment

  What if the bakery receives some of its ingredients from local farmers? The farmers certainly don't qualify as "companies." What type of equipment do these suppliers supply? Cooking utensils? Hand trucks ? Delivery racks? Here's a much better definition of suppliers.

  Suppliersthe people and organizations from whom we purchase ingredients and equipment

  This statement can be used in the table description as the table definition .

• Include a statement that explains why this table is important to the organization. A table contains data that is collected, maintained , manipulated, and retrieved by the organization for a particular reason. Your statement should explain why the data is important to the organization. Keeping in mind that this statement becomes part of your table description, you might be tempted to construct a statement such as this:

  We need the Suppliers table to keep track of the names, addresses, phone numbers , and contact names of all our suppliers.

  This statement is inadequate because it emphasizes only what needs to be stored in the Suppliers table instead of amplifying why the data is important to the business. The next example conveys a better sense of why the information is important.

  Supplier information is vital to the bakery because it allows us to maintain a constant supply of ingredients and ensure that our equipment is always in working order.

  This is a more effective statement because it conveys the importance of the data by identifying the services the suppliers provide to the bakery. It also implies that the bakery could run out of in gredients or have a hard time keeping its equipment in top shape without the suppliers' services. This statement now reflects why the table is important to the organization.

• Compose a description that is clear and succinct. Avoid the common mistake of restating or rephrasing the table name in your table description, as in this example:

  Student Schedulethe class schedule of the student

  Don't be too brief or too verbose. You want to make sure that everyone can identify the table and understand its importance to the organization, but you also want to avoid furnishing too much information. Here's an example of a description that is quite lengthy and provides more information than is necessary:

  Student ScheduleAll the classes that a student will attend (including the days, times, and the faculty conducting the class) during the course of the school year. The data in this table is important because it will let the student know the name of the class and when and where he's supposed to be. Also the student will know the duration of the class, as well as the name of the teacher who is teaching the class.

  This can be recast more clearly and succinctly as follows:

  Student ScheduleThose classes that the student is scheduled to attend during this school year. The information provided by this table helps the student implement effective time management and enables the school to figure class loads and student loads.

  The first sentence in this example provides the definition of the table, and the second sentence states why the table is important to the academic organization.

• Do not include implementation-specific information in your table description, such as how or where the table is used. Avoid statements that indicate how you will specifically use this table, or how you will physically access it. This type of information is germane to the database implementation process, which is wholly separate from the database-design process you're learning in this book. Here is an example of a description containing this type of inappropriate information:

  Student ScheduleThose classes that the student is scheduled to attend during this school year. This information is used by the registrar and is accessed from the Student Admissions menu in the Registration Program.

• Do not make the table description for one table dependent upon the table description of another table. Each table description should be self-explanatory and independent from every other table description; it should be absolutely unnecessary for you to cross-reference one table description against another. This is the type of statement you're trying to avoid:

  Dependentsthe spouse, children, or wards of a given employee. (See description of Employee table for further information.)

  Here's a much better description:

  Dependentsthe spouse, children, or wards of a given employee. This information allows us to make the appropriate tax deductions for the employee, and is necessary for the benefits programs in which the employee is enrolled.

• Do not use examples in a table description. An example is a valuable communication tool that helps you convey a particular meaning or concept and is very effective when you use it wisely. But an example depends on supplemental information (and, in some cases, further examples) to complete the idea it's supposed to convey. For instance, just think of the number of examples you would have to use in order to define fully what a table represents. A well-defined description is clear, succinct, and self-explanatory; therefore, it does not require an example to convey its meaning.

Interviewing Users and Management

Now you'll define table descriptions for the tables on the final table list. You'll conduct interviews with both users and management, and enlist their aid in establishing each table's definition and importance to the organization. (This is one of the few times that you'll actually interview both groups together.) Your main objective is to get a consensus on general descriptions for the tables. When your interviews are complete, take your notes and compose final table descriptions, making sure to follow the guidelines outlined above. Then confer with both parties once more to make certain that the descriptions are acceptable and easily understood by all. The final table list is complete when everyone has agreed on the descriptions.

Consider this example: Assume you're developing a database for a local software training organization. Your assistant, John, is conducting an interview with some of the people from the organization. Specifically, he's speaking to Mark from the administration department; Frits, the instructor coordinator ; Sara, the vice president of sales; and Caroline, the head of the organization. The dialogue on the next page is a partial transcript of John's interview. John is currently discussing the Clients table.

Note

Unlike the interviews you conducted during the analysis and requirements review stages of the design process, you no longer need to involve everyone in the organization. But you will work with a representative group of users and management for the interviews you'll conduct throughout the remainder of the design process.

As you can see, conducting this type of interview is a fairly straightforward affair. Notice how John attempts to get a consensus as he recognizes that no one has anything else to say about the topic at hand. He then makes note of the points that will help him compose the description and moves on to his next topic.

After John has finished conducting the interview, he uses his notes to develop a table description for each table on the final table list. He'll have to interpret and study the participant's responses in order to develop a suitable table description. Based on his examination, John writes the following description:

Studentsthose individuals who attend our classes. The information provided by the data in the Students table allows our organization to further promote our classes and supports proper communications with the students.

John then writes a description for each table on the final table list. When he's finished, he'll speak with Mark, Frits, Sara, and Caroline once more to make sure the descriptions are acceptable and that everyone understands them without any difficulty.