Database normalization and normal forms
The goal of database normalization is to reduce data redundancy and to improve data integrity. This is done by organizing columns and tables by apply the normal forms. In theoretical database speak, a table is called a relation and a column is called an attribute.
First Normal Form - 1NF
The first normal form involves dividing attributes to their atomic values. For example, an attribute contain cell phone number, work number, and home phone number. 1NF requires us to divide this data into 3 different attributes or three different rows with one phone number, each. 1NF eliminates repetition. It enforces creation on separate tables for each set of related data. It enforces use of a unique primary key for each piece of data.
Second Normal Form
- The database is in 1NF
- All non-key attributes are dependent on the primary key
For example, a table can contain customer id, customer name, order id, and order description. The order description is not dependent of customer id and customer name is not dependent on order id. So there is a partial dependency. This is not allowed in 2NF. It would be best to separate this data into separate tables.
Third Normal Form
- The database is in 2NF
- All attributes in a table are determined only by the candidate key (often the primary key)