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 third normal form:

• 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 an Employees table 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.

To make it compliant, we would need to move the additional dependent column to another table and reference it using a foreign key. This would result in two tables:

Vehicles Table

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

• VehicleID
• Manufacturer
• ModelID

Models Table

This new 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 — 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. We must remove it 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.

We can 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:

 SELECT OrderNumber, Total
FROM WidgetOrders

We can now use the following query:

 SELECT OrderNumber, UnitPrice * Quantity AS Total
FROM WidgetOrders

to achieve the same results without violating normalization rules.