Normalized Database = Rules for Good Table Design

A database that follows the rules below is normalized to a particular level. There are more levels of normalization, but these first three are enough to deal with for ordinary purposes. Different people express these 'rules' in wildly different ways!

First Normal Form (1NF) - Has a primary key

1. The fields in a table should be logically related to each other.
   

2. Fields are not duplicated inside the table.
   

3. Each field holds a minimum of data.
   

4. Each field appears in only one table.
   (except for the primary key fields which appear as a foreign key in another table.)
   

5. Each record has a unique identifier, the primary key.

Second Normal Form (2NF)- Depend on the primary key

1. Satisfy the rules for First Normal Form.
   

2. Link the tables by defining relationships using foreign keys

3. Values depend on the primary key (the whole key if the key has multiple parts)
   This is part of the 'logically related' rule earlier.

Third Normal Form (3NF) - Depend ONLY on the primay key

1. Satisfy the rules for Second Normal Form.
2. Values depend ONLY on the primary key.
   This keeps out stray bits of data that may be important but which don't below in the current table.

Many databases do not completely follow these rules. Sometimes a database will actually work faster without being completely normalized.

Why bother to normalize? To meet the goals!

• Saves disk space by reducing duplications

• Saves time by reducing data input duplications

• Avoids data input errors by using linked tables and lookup fields

• Speeds up database functions

It is best to design the tables right the first time. Normalize as you go! After you have a lot of records, it can be tricky to fix, as we saw at the beginning of this lesson when you had to separate the data into two tables. So awkward, and there were only a few records with a few fields.