Advancing Database Normalization
Learn the third and other database normalization forms.
Third normal form (3NF)
The third noraml form (3NF) is the third step in the normalization process. To achieve this state, we must ensure that the 2NF and the 1NF must be satisfied.
The condition for 3NF is as follows:
- The 2NF must be satisfied.
The goal of the 3NF is to:
- Eliminate any transitive functional dependencies.
Eliminating transitive functional dependencies
A table that explains the 3NF is as follows:
Product Table
ProductID | ProductName | CategoryID | CategoryName |
1 | Protein power | 1 | Sports and fitness |
2 | Tea | 2 | Food and drink |
3 | Amino acids | 1 | Sports and fitness |
4 | Coffee | 2 | Food and drink |
In the table above, we can see that the “ProductName” column is defined by the “ProductID” column, which is correct. However, we can see that based on the “ProductID” and its respective “ProductName,” this also determines its category (“CategoryID” and “CategoryName”).
Since this is the case, the “ProductID” determines the “CategoryID.” Therefore, determining the category’s name. The “CategoryID” and its name should not depend on the “ProductID.” We have to split these tables into two to satisfy the 3NF.
Looking at the table given below, we can see that the “Products” table has the “ProductID” and “ProductName,” as well as “CategoryID.” The “CategoryID (FK)” merely references the “CategoryID (PK)” in the “Categories” table.
ProductID (PK)
ProductID (PK) | ProductName | CategoryID (FK) |
1 | Protein power | 1 |
2 | Tea | 2 |
3 | Amino acids | 1 |
4 | Coffee | 2 |
To further expand this, we could create a “Mapping” table that holds the “ProductID” and “CategoryID.” Both would be foreign keys that reference the ...