RelationalDBDesign   Tweet

Basic SQL   «Prev  Next»
Lesson 6 An example of a customer table
Objective Understand what a table looks like if it is not normalized.

Customer Database Table Example

Let us take a look at an example table that contains customer data:

CustID Name City Item_ID Description Qty Total
000001 Smith Tucson 100101 Green Widgets 1 $50.00
000001 Smith Tucson 100102 Blue Widgets 2 $100.00
000001 Smith Tucson 100103 Yellow Widgets 1 $50.00
000002 Jones L.A. 100101 Green Widgets 2 $100.00
000002 Jones L.A. 100106 Orange Widgets 1 $50.00

Because this is a basic order table, you can easily see that the table contains the
  1. customer ID, (CustID)
  2. name, (Name)
  3. city, (City)
  4. ordered item ID, (Item_ID) and
  5. description. (Description)
For each item, the quantity and extended total are included.
You can quickly see that many elements are duplicated, making the table redundant to read through and requiring additional storage space.
The way the data is currently displayed in the table, you would need to add two new columns to include the additional address information and the individual cost per item before this becomes a useful set of information.
Normalization, in simple terms, says that there will be one and only one reference to information in a given database, and that tables that use that information will use a pointer (primary key to be introduced later).
For example, customer ID (CustID) and item ID above.
We will normalize this table in the next lesson, so click on the Next» link located at the top or bottom of this page.