Database Normalization

Database normalisation is the habit of splitting data across tables so that every fact is stored exactly once. It is what stops a customer's address from quietly disagreeing with itself across a hundred order rows, and it is the starting point for almost every relational schema you will design.

The problem with one big table

Picture a small online shop that keeps everything in a single orders table: the customer's name and address, what they bought, the price and the date. It is easy to start with, and every question you might ask sits in one place.

The trouble begins when the same customer orders again. Their name and address are copied into the new row. Order ten times and the address exists ten times. That is wasted space, but the real cost is correctness. Duplicated data leads to three classic problems, usually called anomalies:

• Update anomaly: the customer moves house. You have to change every row that holds their address. Miss one and the database now holds two addresses for the same person, with no way to tell which is right.
• Insert anomaly: you want to add a new product to the catalogue, but a product only exists as part of an order. You can't record it until somebody buys it.
• Delete anomaly: a customer's only order is cancelled and you delete the row. Their name and address vanish with it, even though you wanted to keep them.

Normalisation removes all three by giving each kind of thing (customers, products, orders) its own table, and linking the tables with keys.

Keys, the glue between tables

Two ideas make normalisation work.

A primary key is a column, or set of columns, whose value identifies exactly one row. A customer id is a typical example: two customers can share a name, but never an id.

A foreign key is a column that holds another table's primary key. Instead of copying a customer's name and address into an order, the order stores customer_id, which points at the one row in customers that holds those details. The database can enforce the link, refusing an order whose customer_id doesn't exist.

When you need the full picture back, a JOIN follows those pointers and reassembles it at query time.

The normal forms

Normalisation is usually taught as a series of normal forms, each a rule the tables must follow. Each form includes the one before it: a table in third normal form is also in first and second. There are higher forms too, such as Boyce-Codd normal form (BCNF), but the first three are the ones most day-to-day schema design is about.

The rules are phrased in terms of dependency: column B depends on column A when knowing A tells you B. Knowing a customer id tells you the customer's address, so the address depends on the customer id.

First normal form (1NF)

A table is in 1NF when every cell holds a single value and there are no repeating groups. A repeating group is either a list packed into one cell, like 'Bluefin x2, Skipjack x1', or a run of columns such as item1, item2, item3.

Lists in a cell are hard to query (how do you find every order that included Skipjack?) and impossible to constrain. The fix is one row per value: one row for each item on each order.

Second normal form (2NF)

A table is in 2NF when it is in 1NF and every non-key column depends on the whole primary key, not just part of it. This only matters when the key is made of more than one column.

After the 1NF fix, the order lines table has a two-part key: the order id plus the product. The quantity depends on both: it is the quantity of this product on this order. But the order date depends only on the order id, and the product's price depends only on the product. Those columns depend on half the key, so they belong in their own tables.

Third normal form (3NF)

A table is in 3NF when it is in 2NF and every non-key column depends only on the key, not on another non-key column. A dependency that goes through another column is called a transitive dependency.

In an orders table keyed by order id, the customer's address depends on the customer, and the customer depends on the order. The address reaches the key only through the customer. That is the sneaky extra dependency: move the address into a customers table and let the order keep just the customer's id.

A common way to remember all three: every non-key column must depend on the key, the whole key, and nothing but the key.

A worked example: normalising the shop

Here is the shop's starting point, with a list in one cell:

-- One big table: not even in 1NF
CREATE TABLE orders (
  order_id         INT,
  order_date       DATE,
  customer_name    TEXT,
  customer_address TEXT,
  items            TEXT  -- 'Bluefin x2, Skipjack x1'
);

Step 1, 1NF. Give each item its own row. The key becomes the order id plus the product, and every column holds one value. Customer details are still copied onto every line, and the price is copied onto every sale of that product.

Step 2, 2NF. Pull out the columns that depend on only part of the key. The date and customer go to an orders table keyed by order id. The price goes to a products table keyed by product. What remains, the quantity, depends on both parts and stays in order_items.

Step 3, 3NF. The customer's address depends on the customer, not the order, so customers get their own table with an id of their own.

The finished schema has four tables, each describing one kind of thing:

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        TEXT NOT NULL,
  address     TEXT NOT NULL
);
 
CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name       TEXT NOT NULL,
  price      NUMERIC(8, 2) NOT NULL
);
 
CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL
    REFERENCES customers (customer_id),
  order_date  DATE NOT NULL
);
 
CREATE TABLE order_items (
  order_id   INT REFERENCES orders (order_id),
  product_id INT REFERENCES products (product_id),
  quantity   INT NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Here are the four tables and how their keys connect:

Now a customer who moves house is one UPDATE on one row, and every past and future order sees the new address. A new product can be added before anyone buys it. Cancelling an order no longer deletes a customer.

Getting the combined view back is a join:

SELECT o.order_id, c.name, p.name AS product,
       i.quantity
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items i ON i.order_id = o.order_id
JOIN products p ON p.product_id = i.product_id;

When to stop, and when to denormalise

Normalisation trades storage and update safety for joins. For most applications that write data regularly, such as shops, booking systems and back offices, 3NF is the right default: the joins are cheap with proper indexes, and the database guards consistency for you.

There are good reasons to step back from it deliberately, which is called denormalisation:

• Reporting and analytics. Data warehouses often use wide, flattened tables because they are read far more than written, and scanning one table beats joining six.
• Hot read paths. A page that shows an order count on every profile might keep a cached count on the customer row rather than counting orders each time. You then own the job of keeping it in step.
• Historical facts. An order should record the price the customer actually paid. If the invoice reads the current price from products, raising a price rewrites every old invoice. Storing unit_price on order_items is not duplication: the price at the time of sale is a different fact from today's price.

The rule of thumb is to normalise first, then denormalise a specific spot when you have measured a real need, and write down why.

Common mistakes

• Comma-separated lists in a column. Tags, roles or item lists squeezed into one text field break 1NF. They belong in a separate table with one row per value.
• Numbered columns. phone1, phone2, phone3 is a repeating group in disguise, and the fourth phone number has nowhere to go.
• Copying descriptive data into a linking table. If order_items carries the product's name and category, you have brought back the update anomaly.
• Skipping foreign key constraints. Splitting tables without declaring the keys leaves orders pointing at customers that no longer exist.

Key takeaways

• Normalisation splits data into tables so each fact is stored once, which prevents update, insert and delete anomalies.
• Primary keys identify rows; foreign keys link tables without copying data.
• 1NF: one value per cell, no repeating groups. 2NF: depend on the whole key. 3NF: depend only on the key.
• Aim for 3NF by default, and denormalise on purpose when reads or history demand it.