IBM Books

Administration Guide


Consider Normalizing Your Tables

The topic of normalizing tables draws much attention in database design. Normalization helps you avoid redundancies and inconsistencies in your data. The main idea in normalization is to reduce tables to a set of columns where all the non-key columns depend on the entire primary key of the table. If this is not the case, the data can become inconsistent during updating.

This section briefly reviews the rules for first, second, third, and fourth normal forms of tables, and describes some reasons why they should or should not be followed. The fifth normal form of a table, which is covered in many books on database design, is not described here.

Here are brief descriptions of the normal forms presented later:

Form
Description

First
At each row and column position in the table there exists one value, never a set of values. (See "First Normal Form")

Second
Each column that is not in the key provides a fact that depends on the entire key. (See "Second Normal Form")

Third
Each non-key column provides a fact that is independent of other non-key columns and depends only on the key. (See "Third Normal Form")

Fourth
No row contains two or more independent multi-valued facts about an entity. (See "Fourth Normal Form")

First Normal Form

A table satisfies the requirement of first normal form if for each row-and-column position in the table there exists one value, never a set of values. A table that is in first normal form does not necessarily meet the test for higher normal forms.

For example, the following table violates first normal form because the WAREHOUSE column contains several values for each occurrence of PART.

Table 6. Table Violating First Normal Form
PART (Primary Key) WAREHOUSE
P0010 Warehouse A, Warehouse B, Warehouse C
P0020 Warehouse B, Warehouse D

The following example shows the table in first normal form.

Table 7. Table Conforms to First Normal Form
PART (Primary Key) WAREHOUSE (Primary Key) QUANTITY
P0010 Warehouse A 400
P0010 Warehouse B 543
P0010 Warehouse C 329
P0020 Warehouse B 200
P0020 Warehouse D 278

Second Normal Form

A table is in second normal form if each column that is not in the key provides a fact that depends on the entire key.

This means that all data that is not part of the primary key must depend on all of the columns in the key. This reduces repetition among database tables.

Second normal form is violated when a non-key column is a fact about a subset of a composite key, as in the following example. An inventory table records quantities of specific parts stored at particular warehouses; its columns are shown in the following example.

Table 8. Table Violates Second Normal Form
PART (Primary Key) WAREHOUSE (Primary Key) QUANTITY WAREHOUSE_ADDRESS
P0010 Warehouse A 400 1608 New Field Road
P0010 Warehouse B 543 4141 Greenway Drive
P0010 Warehouse C 329 171 Pine Lane
P0020 Warehouse B 200 4141 Greenway Drive
P0020 Warehouse D 278 800 Massey Street

Here, the key consists of the PART and the WAREHOUSE columns together. Because the column WAREHOUSE_ADDRESS depends only on the value of WAREHOUSE, the table violates the rule for second normal form.

The problems with this design are:

To satisfy second normal form, the information shown above, in Table 8, would be split into the following two tables:

Table 9. Part-Stock Table Conforms to Second Normal Form
PART (Primary Key) WAREHOUSE (Primary Key) QUANTITY
P0010 Warehouse A 400
P0010 Warehouse B 543
P0010 Warehouse C 329
P0020 Warehouse B 200
P0020 Warehouse D 278


Table 10. Warehouse Table Conforms to Second Normal Form
WAREHOUSE (Primary Key) WAREHOUSE_ADDRESS
Warehouse A 1608 New Field Road
Warehouse B 4141 Greenway Drive
Warehouse C 171 Pine Lane
Warehouse D 800 Massey Street

However, there is a performance consideration in having the two tables in second normal form. Application programs that produce reports on the location of parts must join both tables to retrieve the relevant information.

To better understand performance considerations, see Part 3. "Tuning Application Performance".

Third Normal Form

A table is in third normal form if each non-key column provides a fact that is independent of other non-key columns and depends only on the key.

Third normal form is violated when a non-key column is a fact about another non-key column. For example, the first table in the following example contains the columns EMPNO and WORKDEPT. Suppose a column DEPTNAME is added. The new column depends on WORKDEPT, whereas the primary key is the column EMPNO; thus the table now violates third normal form.

Changing DEPTNAME for a single employee, John Parker, does not change the department name for other employees in that department. The inconsistency that results is shown in the updated version of the table in the following example.

Table 11. Unnormalized Employee-Department Table Before Update
EMPNO (Primary Key) FIRSTNAME LASTNAME WORKDEPT DEPTNAME
000290 John Parker E11 Operations
000320 Ramlal Mehta E21 Software Support
000310 Maude Setright E11 Operations

The following example shows the content of the table following an update to the DEPTNAME column for John Parker. Note that there are now two different department names used for department number (WORKDEPT) E11:

Table 12. Unnormalized Employee-Department Table After Update
Information in table has become inconsistent.
EMPNO (Primary Key) FIRSTNAME LASTNAME WORKDEPT DEPTNAME
000290 John Parker E11 Installation Mgmt
000320 Ramlal Mehta E21 Software Support
000310 Maude Setright E11 Operations

The table can be normalized by providing a new table, with columns for WORKDEPT and DEPTNAME. In that case, an update like changing a department name is much easier--the update only has to be made to the new table. An SQL query that shows the department name along with the employee name is more complex to write because it requires joining the two tables. This query will probably also take longer to execute than the query of a single table. In addition, the entire arrangement takes more storage space because the WORKDEPT column must appear in both tables. The following tables are defined as a result of normalizing EMPDEPT.

Table 13. Employee Table After Normalizing the Employee-Department Table
EMPNO (Primary Key) FIRSTNAME LASTNAME WORKDEPT
000290 John Parker E11
000320 Ramlal Mehta E21
000310 Maude Setright E11


Table 14. Department Table After Normalizing the Employee-Department Table
DEPTNO (Primary Key) DEPTNAME
E11 Operations
E21 Software Support

Fourth Normal Form

A table is in fourth normal form if no row contains two or more independent multi-valued facts about an entity.

Consider these entities: employees, skills, and languages. An employee can have several skills and know several languages. There are two relationships, one between employees and skills, and one between employees and languages. A table is not in fourth normal form if it represents both relationships, as in the following example:

Table 15. Table Violating Fourth Normal Form
EMPNO (Primary Key) SKILL (Primary Key) LANGUAGE (Primary Key)
000130 Data Modelling English
000130 Database Design English
000130 Application Design English
000130 Data Modelling Spanish
000130 Database Design Spanish
000130 Application Design Spanish

Instead, the relationships should be represented in two tables, as in the following examples.

Table 16. Employee-Skill Table in Fourth Normal Form
EMPNO (Primary Key) SKILL (Primary Key)
000130 Data Modelling
000130 Database Design
000130 Application Design


Table 17. Employee-Language Table in Fourth Normal Form
EMPNO (Primary Key) LANGUAGE (Primary Key)
000130 English
000130 Spanish

If, however, the facts are interdependent--that is, the employee applies certain languages only to certain skills--then the table should not be split.

Any data can be put into fourth normal form. A good rule when designing a database is to arrange all data in tables in fourth normal form, and then decide whether the result gives you an acceptable level of performance. If it does not, you are at liberty to denormalize your design.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]