Lesson 5 — Normalisation, Constraints and Good Design

Normalisation

Organising tables so each fact is stored once.

Update anomaly

Inconsistency caused by the same fact living in many rows.

3NF

No non-key column depends on another non-key column.

CHECK constraint

A rule the database enforces on column values.

Denormalisation

Deliberately duplicating data to speed up reads.

One table holds order_id, customer_name, customer_email, product_name, product_price, qty.

1. 1Spot repetition: Customer email repeats on every order; product price repeats on every line.
2. 21NF: Split 'products: pen, pad, ruler' in one cell into separate order_line rows.
3. 32NF: product_price depends only on product, not on (order_id, product_id) — move it to product.
4. 43NF: customer_email depends on customer_name, not the order — move it to customer.
5. 5Result: customer, product, orders and order_line — each fact once.

sqlCREATE TABLE order_line (
  order_id   INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id INTEGER NOT NULL REFERENCES product(id),
  qty        INTEGER NOT NULL CHECK (qty > 0),
  unit_price NUMERIC(8,2) NOT NULL CHECK (unit_price >= 0),
  PRIMARY KEY (order_id, product_id)
);

CREATE INDEX idx_order_line_product ON order_line(product_id);

unit_price is copied onto the line on purpose — a historic invoice must not change when the product's price changes later. That is denormalisation with a reason.