Normalize your data to design tables with less redundancy

Normal forms are rules for organizing the data in a database with the goal of reducing redundancy.  There are five levels of normal form, from one to five, plus an additional normal form for the third level.

The benefits of normalization include less redundancy, cleaner, better organized and more efficient databases.  Those benefits become more and more apparent as the database size increases. On the other hand, there are cases when normalization can create more complexity than the benefits it offers.

Each level assumes that the requirements of the previous level have been met.

First Normal Form (1NF) eliminates  all duplicate columns in a table. Separate tables for the related data are created and primary keys identified.

Second Normal Form (2NF) creates relationships between tables using foreign keys.

Third Normal Form (3NF) removes all columns that are not dependent on the primary key as well as derived attributes.

Fourth Normal Form (4NF) requires that independent plural attributes are implemented in separate tables. 

Fifth Normal Form (5NF) requires that  join dependencies and associated redundancy are eliminated.

As a practical matter, 3NF is the most important to be achieved.  4NF and 5NF are rarely enforced. 

Here’s a tip to hep you remember the first 3 forms: “The key, the whole key, and nothing but the key” correspond to 1NF, 2NF, and 3NF.

Leave a Reply

Your email address will not be published. Required fields are marked *