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

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.

In the context of database design, normalization is a systematic approach for organizing tables and relationships in a database to reduce redundancy and improve data integrity. Recognizing when a table is normalized involves understanding the various normal forms, a set of criteria or rules that a database can conform to, each building upon the previous one to further reduce data redundancy and dependency issues. Here's how you can identify the level of normalization within a table:

1. First Normal Form (1NF):
   • A table is in 1NF if all its values are atomic (indivisible) and each column contains values of a single type. This means there should be no repeating groups or arrays within a single column, and each row-column combination contains a unique, single value. If your table adheres to these rules, it has achieved 1NF.
2. Second Normal Form (2NF):
   • To reach 2NF, a table must first be in 1NF. Additionally, it must ensure that all non-key attributes are fully functional and dependent on the primary key. This means that there should be no partial dependency of any column on a part of the primary key in case of composite primary keys. If your table has no composite primary keys or if all non-key attributes depend entirely on the full set of the primary key, your table is in 2NF.
3. Third Normal Form (3NF):
   • A table is considered to be in 3NF if it is already in 2NF and all of its attributes are not only fully functionally dependent on the primary key but also non-transitively dependent. In other words, no non-key attribute should depend on another non-key attribute. Each non-key attribute must directly depend on the primary key. This eliminates transitive dependency, further reducing redundancy.
4. Boyce-Codd Normal Form (BCNF):
   • A stricter version of 3NF, a table is in BCNF if, and only if, every determinant is a candidate key. This means that for every functional dependency (X → Y), X should be a superkey. This form addresses certain types of anomalies not covered by 3NF.
5. Fourth Normal Form (4NF):
   • To achieve 4NF, a table must be in BCNF, and it must not have any multi-valued dependencies that are not trivial. This means an attribute in a table should not be dependent on another attribute that is itself independent of the primary key.
6. Fifth Normal Form (5NF):
   • A table is in 5NF, also known as "Projection-Join Normal Form" (PJNF), if it is in 4NF and every join dependency in the table is a consequence of the candidate keys. Essentially, the table cannot be decomposed into smaller tables without loss of data.

Knowing when a table is normalized involves examining its structure, dependencies, and relationships to ensure compliance with these normal forms. It's a process of progressively applying these rules to eliminate redundancy, prevent anomalies, and ensure data integrity. As you design and review tables, consider each of these criteria to determine the level of normalization and make adjustments as necessary to achieve the desired level of normalization for your database design objectives.

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.