Database Normalization

Database normalization is a process of database design which eliminates duplicate or redundant data in a relational database. It also removes inconsistent dependencies by ensuring that data is stored in tables in a logical and organized manner. This is achieved for practical purposes through three levels of normalization, referred to as the first, second and third normal forms, or NF1, NF2, and NF3.

A database confirms to the first normal form if there are no repeating elements or groups of elements within each row, or tuple, of data. So for example, in a database tracking customer invoices, a nonconforming database might have each row in a single table listing the order ID, customer name and address details and columns for first item, quantity and description, repeated for second, and third items and so on. The problem is what happens when a customer places a large order. Either the invoice has to be split into two rows, or the database restructured with additional columns to accommodate this. In order to eliminate these repeating columns, the data might be split into two tables, one containing the order ID and customer information, the other the individual order items.

Where data in a relational database is split into two or more tables, it is connected for ease of retrieval and manipulation through primary and foreign keys. This isn't as complicated as it sounds. Each row in the table must be unique. To ensure there are no duplicates, each table is assigned a primary key, or a column in which duplicates are not allowed. This can be an automatically assigned numerical key, or a user entered one, such as OrderID. It can even be made up of a combination of two columns, such as OrderID and ItemID, in which case it is known as a concatenated primary key. A foreign key is simply a column in one table which references the primary key of another table, so in this instance it might be the reference to CustomerID within the orders table, which connects the order to the customer.

The rule of second normal form says that where a concatenated primary key exists, each column within the table must rely on both parts of the key. If some data relies only on one part of the key, it should be moved to its own table and connected to the original table by the use of a foreign key. So for example rather than retaining a single table with both order and ordered items details, these would be split into two separate tables, one organized by a primary key OrderID and containing item and quantity details, the other organized by the primary key ItemID containing description and price details. The two are then linked to through ItemID column.

To be NF3 compliant, any fields that do not depend on the key must be removed. Thus in an orders table which contains both order date and customer details, with the primary key OrderID, the customer details are not dependent on the primary key. These should instead be removed to a customer's table, with the primary key CustomerID, with only the CustomerID column remaining in the orders table as a foreign key reference.