Normalisation

Normalisation is the process of organising tables so each fact is stored once. The formal definitions are less useful than the problems they exist to prevent, so this lesson leads with the problems.

What goes wrong without it

Suppose orders are stored like this:

Three problems, and they have names:

• Update anomaly — Acme changes email. Three rows to update, and if one is missed the database now holds two contradictory answers to the same question.
• Insert anomaly — a new customer who has not ordered cannot be recorded. There is nowhere to put them.
• Delete anomaly — delete the last order and the customer's details disappear with it.

Every normal form below removes one class of these.

First normal form (1NF)

Each column holds a single value; no repeating groups.

Violations look like phone_numbers containing '0123, 0456', or columns named product_1, product_2, product_3.

The comma-separated version cannot be searched (LIKE '%0456%' matches the wrong things and cannot use an index), cannot be constrained, and cannot be joined. The numbered-columns version breaks the moment someone has four.

The fix is a separate table with one row per value.

Modern caveat: JSON and array columns technically violate 1NF and are sometimes the right choice for genuinely irregular data. The rule still holds for anything you filter, join or aggregate on.

Second normal form (2NF)

1NF, and every non-key column depends on the whole primary key.

Only relevant with a composite key. Given order_items keyed on (order_id, product_id):

quantity depends on both columns — correct. product_name depends only on product_id — a partial dependency. So the product name is repeated in every line item that mentions it, and renaming a product means updating them all.

The fix: product_name belongs in products.

Third normal form (3NF)

2NF, and no non-key column depends on another non-key column.

dept_name depends on dept_id, which is not the key — a transitive dependency. The department name is duplicated across every employee in it, and renaming a department means updating every one of their rows.

The fix is the schema this course actually uses: dept_name lives in departments, and employees holds only dept_id.

SQL▶ Run
SELECT e.first_name, d.name AS department
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
ORDER BY e.emp_id
LIMIT 5;

The join is the cost of normalisation, and it is what lesson 19 was teaching. The department name exists in exactly one row; change it there and every query sees the change at once.

3NF is where most schemas should live. The informal statement is the one worth remembering: every non-key column depends on the key, the whole key, and nothing but the key.

Beyond 3NF

BCNF tightens 3NF for the unusual case of overlapping candidate keys. 4NF and 5NF address multi-valued and join dependencies. You will meet these in an exam far more often than in a schema, and 3NF plus good judgement covers almost all real work.

Denormalisation

Deliberately duplicating data, for a reason, with the cost accepted:

• A comment_count on a post, rather than counting comments on every page view.
• A daily rollup table, so a dashboard does not aggregate a billion rows live.
• A read model duplicating fields to avoid a five-table join on a hot path.

The rule: normalise first, denormalise when you have measured a problem. Denormalised data can drift, and every duplicate must be kept in step by something — a trigger, a scheduled job, application code. That maintenance is the price, and it is only worth paying against a real, measured cost.

The failure mode is denormalising by default "for performance" on a schema that was never slow, and inheriting the drift for nothing.

A worked example

Unnormalised:

Applying the rules in order:

• 1NF — the numbered course columns become rows in a student_courses table.
• 2NF — anything depending on only part of (student_id, course_id) moves out.
• 3NF — tutor_email depends on tutor, not on the student, so tutors get their own table.

Result: students, courses, tutors, and a student_courses join table carrying (student_id, course_id) as its composite primary key — which also prevents the same student being enrolled twice, for free.

Common mistakes

• Comma-separated values in a column — unsearchable, unconstrainable, unjoinable.
• Numbered columns — the count is always wrong eventually.
• Copying a name alongside its foreign key — two sources of truth, guaranteed to diverge.
• Denormalising before measuring — cost paid, benefit unproven.
• Normalising to 5NF on principle — joins nobody needs, for anomalies that cannot occur.

Interview question

What is 3NF, and give an example of a violation.

Every non-key column depends on the key, the whole key, and nothing but the key. The classic violation is storing dept_name beside dept_id in an employees table — the name depends on the department, not the employee, so it is duplicated and can be updated inconsistently. Naming the update anomaly it causes is what turns a definition into an answer.

Check yourself

1. Which normal form does a comma-separated phone_numbers column violate?
2. What is a transitive dependency?
3. When is denormalisation justified?