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:1) A table will be created to hold a single copy of the customer information;
2) a table that has your inventory, or item information; and 3) 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 Address Information
2) Item information table
3) Order information table
Order line ID
The graphic below shows the Customer Table being split into the three tables
1) Customer, 2) Item, and 3) 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.