Showing Page:
Database design theory includes standards and guidelines to help us efficiently design our tables
and create a normalized database.
These are referred to as normal forms. Let's begin with a summary of the normal forms.
First Normal Form (1NF):
• Break each field down to the smallest meaningful value
• Remove repeating groups of data
• Create a separate table for each set of related data
Second Normal Form(2NF)
• Create new tables for data that applies to more than one record in a table
• Add a related field (foreign key) to the table
Third Normal Form (3NF)
• Remove fields that do not relate to, or provide a fact about, the primary key
The normal forms are cumulative and there are actually a total of five normal forms starting with
the first normal form (1NF) through the fifth normal form (5NF).
However, the fourth and fifth normal forms are rarely applied. Most databases achieve
normalization by the third normal form (3NF) so the first three normal forms will be the focal point
of this article.
First Normal Form
A table is in its 1NF when each field contains the smallest meaningful value and does not contain
repeating groups of data.
What does this mean exactly? Let's break it down step-by-step.
Take the following table for example:
Showing Page:
If we apply the first portion of 1NF, the Name, and Spouse\Children fields do not contain the
smallest meaningful value. Instead, the first name, last name, and the children's names should be
placed in separate fields:
Of course there are still flaws in the table, but keep in mind there is more than one step in the
normalization process.
Once the first step is accomplished then we proceed to the second portion of 1NF: the table
should not contain repeating groups. Spouse, Child 1, Child 2, and Child 3 would be considered
repeating groups. They are dependants of the employee.
The dependants need to be moved to another table and each dependant should be added as
records in the table:
Also consider that Lenny and Carl do not have dependants so there are no related records for
them in the Dependants table. However should either add a dependant in the future we simply
add a related record. There is no need to modify the database design.
Second Normal Form
Create new tables for data that applies to more than one record in a table and add a related field
(foreign key) to the table:
Showing Page: