Putting a Database in Third Normal Form (3NF)

Third normal form (3NF) is a database principle that supports the integrity of data by building upon the database normalization principles provided by first normal form (1NF) and second normal form (2NF).

There are two basic requirements for a database to be in 3NF:

• The database must already meet the requirements of both 1NF and 2NF.
• All database columns must depend on the primary key, meaning that any column's value can be derived from the primary key only.

Let's explore further what we mean by the fact that all columns must depend on the primary key. If a column's value can be derived from both the primary key and another column in the table, it violates 3NF. Consider a table of employees with these columns:

• EmployeeID
• FirstName
• LastName

Do both LastName and FirstName depend only on the value of EmployeeID? Well, could LastName depend on FirstName? No, because nothing inherent in LastName would suggest the value of FirstName.

Could FirstName depend on LastName? No again, because the same is true: whatever a LastName might be, it could not provide a hint as to the value of FirstName. Therefore, this table is 3NF compliant.

But consider this Vehicles Table:

• VehicleID
• Manufacturer
• Model

The Manufacturer and the Model could derive from the VehicleID, but the Model could also derive from the Manufacturer because a vehicle model is made only by a particular manufacturer. This table design is non-3NF compliant, and could, therefore, result in data anomalies. For example, you might update the manufacturer without updating the model, introducing inaccuracies.

Moving the additional dependent column to another table and referencing it using a foreign key would make it compliant. This would result in two tables:

In the Vehicles Table below, the ModelID is a foreign key to the Models Table:

• VehicleID
• Manufacturer
• ModelID

This new Models Table maps models to manufacturers. If you want to update any vehicle information specific to a model, you would do it in this table, rather than in the Vehicles Table.

• ModelID
• Manufacturer
• Model

A table might contain a derived field, which is one that is computed based on other columns in the table. For example, consider this table of widget orders:

• Order number
• Customer number
• Unit price
• Quantity
• Total

The total breaks 3NF compliance because it can be derived by multiplying the unit price by the quantity, rather than being fully dependent upon the primary key. Total must be removed from the table to comply with the third normal form.

In fact, since it is derived, it's better to not store it in the database at all but rather simply compute it on the fly when performing database queries. For example, we might have previously used this query to retrieve order numbers and totals:

Now use the following query to achieve the same results without violating normalization rules: