Normalisation: 1NF to BCNF

Redundant data drifts out of sync; normalisation is the systematic way to prevent update anomalies.

Normalisation organises tables so each fact is stored in one place. When a fact is copied into many rows, an update that misses one copy leaves the data contradicting itself — an update anomaly.

The normal forms are a checklist, each building on the last:

1NF: every column holds a single value; no repeating groups like phone1, phone2, phone3 or comma-separated lists. Multi-valued attributes become their own table.
2NF: no column depends on only part of a composite key. In order_line(order_id, product_id, product_name), product_name depends on product_id alone, so it belongs in product.
3NF: no column depends on another non-key column. If employee stores dept_id and dept_name, then dept_name depends on dept_id, not on the employee — move it to department.
BCNF: every determinant is a candidate key; a stricter 3NF that matters in rare overlapping-key cases.

The memorable summary for 3NF: every non-key column depends on "the key, the whole key, and nothing but the key".

A normalised schema has more tables and more joins, which relational databases handle very well. Aim for 3NF by default in transactional systems, and denormalise later only for a measured reason.

Example

-- Not normalised: department name repeated on every employee
-- employee(id, name, dept_id, dept_name)   -- dept_name depends on dept_id

-- 3NF: each fact once
CREATE TABLE department (
    id   int PRIMARY KEY,
    name text NOT NULL UNIQUE
);
CREATE TABLE employee (
    id      int PRIMARY KEY,
    name    text NOT NULL,
    dept_id int NOT NULL REFERENCES department(id)
);
INSERT INTO department VALUES (1, 'Sales');
INSERT INTO employee VALUES (10, 'Ani', 1), (11, 'Narek', 1);
UPDATE department SET name = 'Sales EMEA' WHERE id = 1;  -- one row changes
SELECT e.name, d.name AS dept
FROM employee e JOIN department d ON d.id = e.dept_id;
-- Ani   | Sales EMEA
-- Narek | Sales EMEA

Remember

Normalise so every fact lives in exactly one place
1NF: atomic values; 2NF: whole key; 3NF: nothing but the key
3NF is the sensible default for OLTP schemas

Watch out for

Treating normalisation as optional and patching inconsistencies with application code later.

Check yourself

Table order_line(order_id, product_id, quantity, product_name) has primary key (order_id, product_id). Which normal form does product_name violate?

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks