Progress
Database Design
Guide


The Third Normal Form

A table is in the third normal form when it contains only independent columns, that is, columns not derived from other columns.

The third rule of normalization is that you must remove columns that can be derived from existing columns.

Table 2–8 shows an Order table with a Total After Tax column that is calculated from adding a 10% tax to the Order Amount column.

Table 2–8: Order Table with Derived Column 
Order
Number
(Primary
Key)
Order
Date
Order
Amount
Total After
Tax
Cust Num
(Foreign
Key)
M31
3/19/91
$400.87
$441.74
101
M98
8/13/91
$3,000.90
$3,300.99
101
M129
2/9/91
$919.45
$1011.39
101
M56
5/14/90
$1,000.50
$1,100.55
102
M37
12/25/90
$299.89
$329.87
103
M140
3/15/90
$299.89
$329.87
103
M41
4/2/90
$2,300.56
$2,530.61
104

To reduce this table to third normal form, eliminate the Total After Tax column because it is a dependent column that changes when the Order Amount or tax change. For your report, you can create an algorithm to obtain the amount for Total After Tax. You need only keep the source value because you can always derive dependent values. Similarly, if you have an Employee table, you do not have to include an Age column if you already have a Date of Birth column, because you can always calculate the age from the date of birth.

A table that is in third normal form gives you these advantages:

Although a database normalized to the third normal form is desirable because it provides a high level of consistency, it may impact performance when you physically implement the database. When this occurs, consider denormalizing these tables. "Database Design Basics" discusses denormalization.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095