This chapter is from the book
This chapter is from the book
This chapter is from the book
Steps to Normalize Your Data Model
Just as achieving an optimal database design is a multistep process, so, too, is the process of normalization. At a minimum, you will want to normalize to the third normal form. Quite possibly, you might need to go one step further by normalizing to the Boyce-Codd Normal Form. Fourth and fifth normal forms do exist, but their use is far less common. For informational purposes, the fourth and fifth normal forms are described briefly at the conclusion of this section.
To illustrate the normalization process, consider the following list of unnormalized data items:
- Customer ID (primary key)
- Customer Name
- Customer Type
- Contact Name (one to many)
- Category Name (one to many)
If you have ever worked with data that comes from a nonrelational data source, this scenario might be familiar to you. In this example, a customer record length is variable because each customer can have a different number of contacts and each customer can be part of multiple categories. Remember that in its raw form, information produced by your analysis efforts is nonrelational. All the attributes you have identified for a given entity are probably grouped together in a flat structure. This is where the process of normalization comes into play—to organize the attributes. Let's begin by taking the previous structure and placing it into the first normal form.
First Normal Form: Eliminating Repeating Groups
The first normal form (1NF) involves the removal of repeating groups. The question remains, "What is a repeating group?" The previous example has two repeating groups: contacts and category. Remember, for a given customer, one or more contacts and one or more categories can exist.
For each repeating group you encounter, the repeating group is moved to a separate table. In this case, you end up with two new tables that store the contact and category data. The following outlines the new structure and entities:
-
Customer table:
- Customer ID
- Customer Name
- Customer Type
-
Contact table:
- Contact ID
- Customer ID
- Contact Name
-
Category table:
- Category ID
- Customer ID
- Category Name
Before going any further, it is important that you see the benefits derived by moving contacts and categories to separate tables. Imagine how difficult the task of managing contacts would be if contacts were kept in the Customer table. If a customer could have only one contact, the argument could be made that contact data, such as name, phone number, and so on, could be stored in the Customer table. After all, in this case, you would be dealing with a finite set of columns. But what if somebody makes a database enhancement request that involves the support of multiple contacts per customer? You have two choices:
-
Add more columns to the Customer table to support multiple contacts.
-
Add a child table that allows for any number of contacts.
Clearly, choice number two is the easier, more flexible, and more cost-effective alternative. With choice one, your database design could continually be changing whenever a specific customer has exceeded the number of contacts the Customer table can support. If there is one thing that can spell doom for a database application, it is a continually changing database structure and model. A solid and stable database model is the foundation on which everything else rests. After an application has been developed, changing the database structure can be quite expensive and time-consuming. The entire application has to be tested thoroughly to ensure bugs have not been introduced as a result of the database modifications. Attending to these issues at the beginning avoids many problems that would otherwise occur.
The various normal forms are cascading. In other words, before you can tackle the job of applying the second normal form, the first normal form must be applied. The same prerequisites apply for the succeeding normal forms. With the first normal form out of the way, let's proceed to the second normal form, which involves eliminating redundant data. How will this affect each table? The answer follows in the next section.
Second Normal Form: Eliminating Redundant Data
To get tables into the second normal form, you must analyze the fields in relation to the primary key. The question of primary keys was raised in the previous section. The Contact and Category tables' primary key is a multivalued key, so you can't look at a single field that can uniquely identify a record. Looking at the Category table, the primary key is a combination of the Category ID and Customer ID fields. Many customers can use the same category, and a customer can be part of many categories. A customer, however, can't be part of the same category more than once. This makes sense. Because of this rule, the combination of Category ID and Customer ID uniquely identifies a record in the Category table. Let's turn our attention to the Contact table. As you will see, this scenario presents an ambiguous situation.
Does Contact ID uniquely identify a contact record? It might uniquely identify a record; then again, it might not. How is that for an answer? The answer depends on the context. Is the Contact table purely about contacts? Or, is it more accurately named a Customer Contacts table? Again, the answer depends on the context. Can a contact be associated with multiple customers? Or, can a contact be linked with one and only one customer? The analysis and design efforts on your part can answer this question. Sometimes, large corporations can exist as several entries in a customer table. It is quite possible that a contact person could be linked to several customer records. It is very important that your analysis and design efforts uncover details such as this. After a system has gone into production, it can be very expensive to correct this mistake.
How Much Does It Cost to Correct That Mistake?
Using $1 as a base, correcting a mistake at the earliest possible time costs $1. The earliest possible time is when you are engaged in the process of analysis and design and modeling your database. After your database has been created, the cost to correct that same mistake is $10. After your system has begun the process of beta testing, that same mistake costs $100. If the mistake gets through beta testing and is part of the product released into production, that mistake costs $1,000 to fix.
If you think about it, this makes sense. After you get into the process of beta testing, if a mistake is found, you have to start the process over. Imagine if your application is installed in a few hundred sites. The cost of having to redistribute software and update a database can be immense. The idea to take away from this is that as you go through the various stages of development, the cost of fixing bugs increases by a factor of 10. The more effective your analysis and design efforts are, the more likely you are to stay within the budget and deliver your applications at or ahead of schedule.
For the purposes of this illustration, let's assume that a contact can be associated with a single customer. With this in mind, the Contact table is in the second normal form because attributes do not depend on part of a multivalued key, meaning the contact name depends on the contact ID alone. In this case, the Contact ID field does uniquely identify a contact record. Also in this case, no redundant data exists. What about the Category table? Is it in the second normal form? In this case, the answer is no.
Whereas a contact is specific to a customer, a category is a generic item. To illustrate, let's assume some of the valid categories are medical, marketing, and financial. A customer might be a member of both the financial and marketing categories. Each of these categories likely is associated with multiple customers. To illustrate, consider the following entries that might be found in the Category table:
|
Category ID |
Customer ID |
Category |
|
1 |
1 |
Medical |
|
2 |
1 |
Marketing |
|
3 |
2 |
Financial |
|
2 |
2 |
Marketing |
There are two questions to ask:
-
What is the primary key?
-
Does a category depend on all or part of the primary key?
In this case, the primary key is a multivalued key that combines the Category ID and Customer ID fields. The category depends on part, not all, of the primary key. If a field exists that does not depend entirely on the primary key, a table is not in the second normal form. To get this table into the second normal form, the categories must be removed to a separate table. This increases the table count from three to four. The following illustrates the new structure of the Category table:
-
Category table:
- Category ID (primary key)
- Category Name
The following shows the structure of the new Customer Category table:
-
Customer Category table:
- Customer Category ID (primary key)
- Customer ID
- Category ID
Nonsurrogate Versus Surrogate Primary Keys
Do you need a Customer Category ID field in the Customer Category table? The answer is no. The combination of Customer ID and Category ID would serve as a valid multivalued primary key. This type of primary key is also known as a compound primary key. In a table like Customer Category, which serves the role of being a many-to-many resolver table only, a compound key is a perfectly acceptable alternative. For one thing, it is highly unlikely the Customer Category Table will ever act as a parent; that is, the Customer Category PK is unlikely to ever be carried in a child table. Whether you choose to keep a single-value PK or a multivalue compound PK is a matter of personal preference.
The reason the field is included is a matter of consistency and preference for a specific methodology. The two methodologies in question are whether you use surrogate or nonsurrogate keys.
Two schools of thought exist on how to produce primary keys. One group says that the keys should be produced in sequential order; another group says that primary keys should be a combination of existing fields. Surrogate keys are produced in sequential order by way of some incrementing device. In Access, the autonumber field type is used for this purpose. Sometimes, surrogate keys are called meaningless keys because, when viewing the value of the key, it says nothing about the record it identifies. The other type of key, often called a multivalued or meaningful key, is made up of a combination of existing fields. Sometimes, the key is a combination of customer name + address + city + state. Anything that uniquely identifies a record will work. It is easy to see why multivalue keys are meaningful. When viewing the value of the key, you immediately see data that describes the record the key identifies.
Which method is correct? Both methods have merit. Of the two methods, surrogate keys are probably the better approach. To analyze why surrogate keys are a better approach, you need to understand the purpose of a primary key. A primary key serves to uniquely identify a record. Primary keys are carried in child data records for the purpose of linking those child records to the parent. Which is easier, carrying a single integer value in a child table or carrying a character string that might be 100 or more characters in length? What happens when the company name, address, or any other component of the key changes? Not only does the key change, but the key—carried as a foreign key in child tables—must change as well. The maintenance problems multivalued keys create outweigh any advantage they might have.
The purpose of a primary key is twofold: to uniquely identify a record and to serve as the glue between related tables, period. Its value should never change. Primary keys sit in the background and keep order. In some ways, they are similar to a referee in a football game. In good football games, you don't notice the referee. End users should never see, be aware of, or control the makeup of primary keys. With all this in mind, is a primary key the only way to uniquely identify a record? The answer is no. Consider customer number fields.
No two customers would have the same customer number. Otherwise, what use would a customer number have? Often, companies have a systematic way of numbering customers. Part of the number might indicate information about the customer, such as the name. If this is the case, the customer number should not serve as the primary key because it is dependent on characteristics in the customer record. Remember, if some aspect of the customer record changes that the customer number depends on, the customer number must change as well. Of course, there is no reason the primary key could not serve a third purpose of being a customer number as well. If this is indeed the case, users should not have control over the makeup of the customer number.
The following outlines how sample data might appear in the Category table:
|
Category ID |
CategoryName |
|
1 |
Medical |
|
2 |
Marketing |
|
3 |
Financial |
The following outlines how sample data might appear in the Customer Category table:
|
Customer Category ID |
Customer ID |
Category ID |
|
1 |
1 |
1 |
|
2 |
1 |
2 |
|
3 |
2 |
1 |
|
4 |
2 |
2 |
Prior to placing the tables in the second normal form, consider the scenario of changing the description of a category. With redundant data, multiple updates would need to occur. With the second normal form, a category description exists in one location only. After that single entry has been changed, any reports would automatically be up to date. In regard to reports, consider that prior to placing the tables in the second normal form, you must create a report that displays the Category Name field. What would you use as a source for the field? With the second normal form, the answer is much clearer.
Let's take a closer look at the Customer table. Looking at the Customer Type field, it would appear that another repeating group exists. The following illustrates how the data appeaers in the Customer table:
|
Customer ID |
Customer Name |
Customer Type |
|
1 |
Ace Tomato |
Business |
|
2 |
PMH |
Hospital |
|
3 |
Rutgers Law |
School |
|
4 |
Microsoft |
Business |
In order to apply the second normal form, a new Customer Type table must be created. The following outlines the structure of this new table.
|
Customer Type ID |
Customer Type Description |
|
1 |
Business |
|
2 |
Hospital |
|
3 |
School |
Now that a new Customer Type table exists, the Customer table must be modified so that a link between the two tables can be established. The following outlines the new Customer table structure:
|
Customer ID |
Customer Name |
Customer Type |
|
1 |
Ace Tomato |
1 |
|
2 |
PMH |
2 |
|
3 |
Rutgers Law |
3 |
|
4 |
Microsoft |
1 |
Figure 4.2 shows the new data model produced through normalization.
)
Figure
4.2 The tables in this data model conform to the second normal form.
Third Normal Form: Eliminating Columns Not Dependent on Keys
You started with a single flat structure. To get to the first normal form, repeating groups were moved to separate tables. This resulted in three new tables: Contact, Category, and Customer Type. The next step is to get the tables into the third normal form. In order to illustrate this process, some new fields need to be added to the Customer table. The following outlines the new structure of the Customer table:
- Customer ID
- Customer Name
- Customer Type ID
- City
- State
- ZIP Code
The Customer table is in the first normal form because no repeating groups exist, and the Customer table is in the second normal form because a multivalue key does not exist. In this case, a single surrogate primary key exists. The question turns to whether the Customer table is in the third normal form. The third normal form requires that any field that is dependent on anything besides the primary key be moved to another table. The table the field is moved to may be an existing table or a new table. What about fields such as city, state, and ZIP Code—do they depend on the Customer ID Primary Key? The answer is no. These elements are completely independent of the Customer ID Primary Key.
Let's take a few moments to examine the relationship between city, state, and ZIP Code. A ZIP Code is specific to a city and a state. For example, 19301 is specific to Paoli, Pennsylvania. There is, however, a Paoli, Oklahoma as well. Remember the goal of normalization is to remove redundant data. You only want to have to define an element of data one time and reuse that element of data as needed. Consider a city like Philadelphia that has hundreds of ZIP Codes. As you examine the problem, it becomes clear that new tables will be required. In this case, you would create a state, city, and citystatezip table. The following outlines the various table structures:
-
State table:
- State ID
- State Name
-
City table:
- City ID
- City Name
-
City State Zip table:
- ZIP Code
- City ID
- State ID
)
Figure
4.3 The tables in this data model conform to the third normal form.
Most of the time, the third normal form is as far as you will have to go. It should be noted, though, that fourth and fifth normals do exist. Their use, however, is rare and infrequent. In the interest of completeness, the following section outlines these additional normal forms.
Fourth and Fifth Normal Forms
In most situations, the third normal form is as far as you have to go. That is not to say that is the end of the normalization story. Fourth and fifth normal forms exist. The fourth normal form isolates independent multiple relationships, and the fifth normal form isolates semantically related multiple relationships. What the heck does this all mean? It is a mouthful of words, but the concepts are not that difficult to understand. Let's start with the fourth normal form.
As you go through your analysis, you determine that categories of customers fall within certain sales volume ranges. Knowing what you know about normalization, you decide to create a sales range definition table and in turn, link that table to the Customer Category table. The following outlines the revised structure of the affected tables:
- Customer Category table:
- Customer Category ID (primary key)
- Customer ID
- Category ID
- Sales Range ID
- Sales Range table:
- Sales Range ID (primary key)
- Sales Range Description
The question is whether a meaningful relationship exists between the Customer Category and Sales Range tables. Most likely, the answer is no. The customer itself has sales, but sales ranges are not associated with a customer by way of the categories the customer might be associated with. To be in the fourth normal form, the Sales Range ID field needs to be moved to the Customer table. In the current hypothetical case, the Customer, Contact, Category, and Customer Category satisfy the fourth normal form already.
The fifth normal form concept is a bit harder to grasp. The fifth normal form involves breaking tables in the fourth normal form into separate tables for the purpose of reducing the amount of rows that must be inserted, modified, or deleted during various update operations. The question is whether maintaining a few extra rows of data is easier than maintaining an extra table. If you don't really need another table, it is best to avoid creating the table. To illustrate the fifth normal form, consider the following example.
Let's add a Salesperson table to the hypothetical database you have been working on. The database can record which categories are associated with a customer. In addition, the database also records which salesperson is assigned for a given customer category combination. The implication is that a customer category salesperson table would have to exist to support the association between the three entities. Because a meaningful relationship exists between categories and salespeople, the customer category salesperson is in the fourth normal form.
Now, let's assume a new rule is put into place that after a salesperson is associated with a given customer category combination, the salesperson has access to the other categories with which the customer might be associated. If a customer is associated with three salespersons, every time you associated the customer with a new category, you must add three records in the customer category salesperson table—one for each salesperson. The same situation would occur if the customer were associated with three categories and a change in salesperson were made. In this case, three records would have to be updated. The fifth normal form involves breaking up the table to reduce the number of records.
To accomplish the goal, a Customer Salesperson table and a Customer Category table would be established. This explanation assumes that a Customer Category table did not exist. Rather, the analyses lead directly to a Customer Category Salesperson table. Figure 4.4 shows the data model and new entities that are in the fifth normal form.
)
Figure
4.4 The tables in this data model conform to the fifth normal form.
Normalization: Some Final Thoughts
As you gain experience developing databases, you will find that normalization becomes an implicit and less of an explicit process. In other words, as you organize and model the information acquired in the requirements-gathering activities, you will immediately create tables that conform to the third normal form so long as you use single-value surrogate keys. If you have a many-to-many relationship, as is the case between customers and categories, you might have to take the additional step of going to the fourth normal form. As long as the table that acts as the many-to-many resolver—the Customer Category table in this case—contains only the minimum data required for supporting the many-to-many relationship, you don't have to worry about getting to the fourth normal form because the table will already conform. Finally, if you don't have to be concerned about getting to the fourth normal form, you don't have to be concerned with the fifth normal form. Again, the table will already conform. It can also be argued that if you don't have to be concerned with getting to the fourth and fifth normal forms, those normal forms don't apply. As long as you get to the third normal form and as long as you have been thorough in your analysis and design efforts, your chances of success are very good.