Relational Databases  «Prev  Next»

Lesson 7 Normalize the example table
Objective Discuss how a table is normalized.

Example of Table Normalization

To normalize the example table, you will create three tables. They are:
  1. A table for the customer information
  2. A table for the item information
  3. 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

  1. Customer ID
  2. Customer Name
  3. Customer Address Information

2) Item information table

  1. Item ID
  2. Item Description
  3. Item Cost
  4. Item Price

3) Order information table

  1. Order line ID
  2. Item ID
  3. Customer ID
  4. Quantity

Database Modeling
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
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.