General Rules
First normal may be the basic or first level, but that doesn't mean that all of the data is dumped into one table.
Each type of related data should be set up in its own table. All of the tables will contain a primary key, a unique value for each record in the table. Additionally, each data element will be granular, which means it cannot be split into smaller chunks. One example would be an employee's name, which can be broken into first and last names.
The general rules for first normal form are:
- All tables have a primary key
- Fields have unique names
- Data is not repeated across fields and
- There's no redundant data, like a field that is a combination of other fields
A primary key is a requirement for any table in a relational database. It's a unique identifier that corresponds to each row of data. Typically, it's a numerical value. Database systems automatically assign the number (usually sequential) to new entries in the table.
Field and column names must be unique. For instance, you can't have three fields named Color in a Product table. Along those same lines, having a field called Zip code and another called Postal code isn't any better, because the data being stored is the same. And this violates the next rule of 1NF: don't repeat data.
This means that a table shouldn't have fields that store similar data that isn't part of the table. The Employee table, for example, may have benefit information, such as data for Benefit Plan A and Benefit Plan B. If we added a Plan C, it would violate the normalization rules by jamming all of the benefits data into the Employee table. A better approach would be to move the benefits info to another table and join the two tables.
Examples of 1NF
Let's take a look at some examples of how to implement the first normal form (1NF).
Example 1: Primary Key and Redundant Data
The following table is not in 1NF. There isn't a primary key, the Emp_Full_Name field is repetitive because we already have name fields.
| Emp_First |
Emp_Last |
Emp_Full_Name |
Job_Title |
Department |
| Jane |
Austen |
Jane Austen |
CFO |
Administration |
Now let's convert this to 1NF. We'll get rid of the full name field (a full name can be created using a simple SQL query) and add a primary key:
| Emp_ID |
Emp_First |
Emp_Last |
Job_Title |
Department |
| 124002 |
Jane |
Austen |
CFO |
Administration |
Example 2: Redundant Field Names
This example isn't a 1NF either. Yes, there is a primary key, but the field name Benefit is repeated.
| Emp_ID |
Emp_First |
Emp_Last |
Benefit |
Benefit |
Job_Title |
| 19994 |
Poe |
Edgar |
Health |
Dental |
Author |
In order to include all of the employee benefit plans, we'll need to create another table that holds this information and then join the two tables. Instead, we'll recreate the benefit fields to only hold the ID's to the other tables:
| Emp_ID |
Emp_First |
Emp_Last |
Health_Plan |
Dental_Plan |
| 19994 |
Poe |
Edgar |
1024 |
2048 |
Lesson Summary
The first normal form (1NF) is the first step in normalizing a table by reducing confusion and redundancy. In 1NF, we remove the redundant columns (columns with the same name and/or data) and redundant fields (such as a full name field when we already have first and last names), and add a primary key. The primary key ensures that each record can be uniquely identified. It also helps to join it to other tables needed during the normalization process. In 1NF, each data element will be granular, which means it cannot be split into smaller chunks.
