Database normalization: the first three normal forms

Normalization is the process of organising a relational database so that each piece of information is stored only once and in the right place. The term sounds academic, but the goal is very practical: preventing inserts, updates and deletes from producing contradictory data. The best way to understand it is to start from a badly designed table and fix it one step at a time.

The starting table

Imagine a small online shop that records everything in a single table:

It works while the data is small, but it hides three kinds of problems, known as anomalies.

• Update anomaly: if Rossi changes email, it has to be changed on every one of his orders. Missing one creates an inconsistency.
• Insert anomaly: you cannot record a new customer until they have placed an order, because every row is an order.
• Delete anomaly: if you delete Bianchi's only order, you also lose the fact that Bianchi exists and lives in Bari.

First normal form (1NF)

A table is in first normal form when each cell holds an atomic value, that is a single one, and there are no repeating groups. The «products» column breaks this rule: it holds a list, with quantities mixed into the name as well.

To fix it, you create one row for each product ordered:

Now you can count how many pens were sold with a simple query, which was impossible while products were free text. The key of the table becomes the pair (order, product).

Second normal form (2NF)

Second normal form concerns tables with a composite key. It requires every non-key column to depend on the whole key, not on part of it.

In the previous table, quantity depends on order and product together: that is fine. But date, customer, email and city depend only on the order number. They are repeated for every product row for no reason.

The solution is to split:

• orders (order, date, customer, customer_email, customer_city)
• order_lines (order, product, quantity)

Likewise, if the product's list price were in the order lines table, it would have to move to a products table, because it depends only on the product.

Third normal form (3NF)

Third normal form removes transitive dependencies: a non-key column must not depend on another non-key column. In the orders table, email and city do not depend on the order itself but on the customer. The order determines the customer, and the customer determines email and city.

So you create a customers table and the orders table keeps only a reference:

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT NOT NULL,
  email       TEXT NOT NULL UNIQUE,
  city        TEXT
);

CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  order_date  DATE NOT NULL,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE order_lines (
  order_id    INTEGER REFERENCES orders(order_id),
  product_id  INTEGER REFERENCES products(product_id),
  quantity    INTEGER NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id)
);

The three anomalies are gone: the email is changed in one place, a customer can exist without orders, and deleting an order does not delete the customer.

An easy way to remember them

A phrase often used to sum up the three forms says that every attribute must depend «on the key, the whole key and nothing but the key». The first part corresponds to 1NF, the second to 2NF, the third to 3NF. There are further normal forms, such as Boyce-Codd normal form, but in everyday practice reaching the third solves the vast majority of problems.

When to denormalize

Normalizing does not mean splitting everything endlessly. There are cases where duplicating a value is the right choice:

• Historical data. The price paid should be stored in the order line even though it exists in the products table, because the price list will change and the order must remember that day's price. This is not redundancy: it is a different piece of information.
• Shipping address. In the same way, the address used for an order should be frozen in the order.
• Reports and analysis. Systems built for analysis often use deliberately denormalized tables to make queries simpler and faster.

The practical rule is to start from a normalized model and denormalize only for a specific reason, and to document it.

In short

• Normalization removes update, insert and delete anomalies.
• 1NF: atomic values, no lists inside a cell.
• 2NF: no column depends on only part of a composite key.
• 3NF: no column depends on another non-key column.
• Denormalize only for clear reasons, such as preserving historical data.