To normalize the example table, you will create three tables. They are:
A table for the customer information
A table for the item information
A table for the line-item order information
This is done because you do not want to duplicate information.
The following three tables will be created:
A table will be created to hold a single copy of the customer information;
a table that has your inventory, or item information; and
a table that contains the order information.
The tables that contain customer and item information will become reference tables for the line-item order information.
This is what you will end up with:
1) Customer information table
Customer ID
Customer Name
Customer Address Information
2) Item information table
Item ID
Item Description
Item Cost
Item Price
3) Order information table
Order line ID
Item ID
Customer ID
Quantity
The graphic below shows the Customer Table being split into the three tables
1) Customer, 2) Item, and 3) Order tables.
Customer Table is split into Customer, Item, and Order tables
Now when you enter an order for an item, you are storing only the item ID, customer ID, and quantity ordered in the order information table.
The other information, the specifics that are referenced by their ID, is stored separately. This leads to one other very significant benefit. If you change the customer's address, the new address is reflected systemwide. Any record that references it will have the correct address, without the need to update each individual reference.
Table Normalization - Exercise
Click the Exercise link below to practice on a different table. Table Normalization - Exercise
In the next several lessons, we will look at different forms of normalization.