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). The purpose of 3NF is to improve database processing while also minimizing storage costs.

Third Normal Form Requirements

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

• The database must 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.

Primary Key Dependence

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.

Then, consider this table of vehicles:

• VehicleID
• Manufacturer
• Model

The Manufacturer and the Model could derive from the VehicleID, but the Model could also derive from the Manufacturer because only one particular manufacturer makes a vehicle model. 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.

Putting a Database in Third Normal Form (3NF)

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, which we'll call “Vehicles” and “Models.”

In the “Vehicles” table, the ModelID is a foreign key to the “Models” table:

• VehicleID
• Manufacturer
• ModelID

The 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

Derived Fields in the 3NF 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. You must remove Total from the table to comply with the third normal form.

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

SELECT OrderNumber, Total
FROM WidgetOrders

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

SELECT OrderNumber, UnitPrice * Quantity AS Total
FROM WidgetOrders