Normalisation in practice (and when to denormalise lightly)

Normalisation in practice: organising columns so you avoid duplication and update anomalies, without turning every schema into an academic exercise.

Why normalise

Normalisation splits data into tables so each fact lives in one place. Change a customer’s email once; every order still points to the same customer row. Store product price on the order line, not repeated on every order header.

Without it you get:

• Update anomalies – the same supplier address copied on 50 product rows; you update 49 and miss one
• Insert anomalies – you cannot add a category until a product uses it
• Delete anomalies – deleting the only order for a customer removes their address because it lived on the order row

First normal form (1NF)

Each column holds atomic values; no repeating groups in one cell. Bad: tags column containing 'sql,mysql,tutorial'. Better: a post_tags junction table or a separate tags table.

-- Avoid
CREATE TABLE posts_bad (
  id INT PRIMARY KEY,
  title VARCHAR(200),
  tag_list VARCHAR(500)
);

-- Prefer
CREATE TABLE tags (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE post_tags (
  post_id INT UNSIGNED NOT NULL,
  tag_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (post_id, tag_id)
);

Second normal form (2NF)

Every non-key column depends on the whole primary key. Matters when you use composite keys. If order_items has primary key (order_id, product_id), then unit_price depends on both – fine. If you stored customer_name on order_items, that depends only on order_id – wrong table.

Third normal form (3NF)

No non-key column depends on another non-key column. Bad: storing city, postcode, and region on customers when region is determined by postcode alone. Better: a postcodes lookup table, or accept the duplication if lookups are rare and simplicity wins.

CREATE TABLE customers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  address_line1 VARCHAR(200),
  postcode VARCHAR(10)
);

CREATE TABLE postcode_regions (
  postcode_prefix VARCHAR(4) PRIMARY KEY,
  region VARCHAR(100) NOT NULL
);

When to denormalise lightly

Normalisation is not a religion. Sometimes you duplicate data on purpose:

• Snapshot columns – unit_price on order lines (price at purchase time)
• Cached counts – comment_count on posts for fast listing (updated by app or trigger)
• Read-heavy reporting – a summary table fed by nightly jobs

Denormalise when you can name the trade-off: faster reads, simpler queries, at the cost of keeping duplicates in sync. Document that decision. Do not denormalise because JOINs feel scary – learn JOIN patterns instead.

Worked example: unnormalised to sensible

Suppose one table holds everything:

CREATE TABLE orders_messy (
  order_id INT PRIMARY KEY,
  customer_name VARCHAR(100),
  customer_email VARCHAR(255),
  product_name VARCHAR(200),
  product_price DECIMAL(10,2),
  quantity INT
);

One order with three products becomes three rows repeating customer details. Split into customers, products, orders, order_items – as in the domain modelling tutorial. Same information, no repeated customer email on every line.

Practical rule of thumb

Aim for 3NF by default. Break out lookup tables when the same string repeats everywhere. Keep snapshots where history matters. Reach for cached aggregates only when measurement proves you need them.

The next tutorial covers index strategy – how to speed up the JOINs and filters your normalised schema produces.