Normalisation and schema design

The idea in one sentence

Normalisation means organising data so that each fact is stored in exactly one place. When a fact is stored in many places, changes must be made in many places, and sooner or later they disagree.

The anomalies it prevents

Imagine one big orders table with the customer's name and email on every row.

• Update anomaly: the customer changes email; you must update every order row, and missing one leaves two different emails.
• Insert anomaly: you cannot record a new customer until they place an order.
• Delete anomaly: deleting a customer's only order deletes the only record of the customer.

The first three normal forms, in plain words

100%

Figure SDA-DDV-01-1 · Normal forms as three questions · © forcsguy.com

1. 1NF: no repeating groups or lists in a cell (not tags = "a,b,c"; use a related table); each row has a primary key.
2. 2NF: relevant when the key has several columns (like order_id, product_id). A column such as product_name depends on only product_id, so move it to a products table.
3. 3NF: a column must depend on the key, "the whole key and nothing but the key". If zip_code determines city, store city with the zip code table, not on every customer row.

Most application schemas aim for 3NF and stop there.

Keys and relationships

• Primary key: unique row identity. Prefer a stable surrogate id (for example a number or UUID) over data that can change, such as an email.
• Foreign key: a column referencing another table's key; the database then guarantees the referenced row exists.
• One-to-many: put the foreign key on the "many" side (orders.customer_id).
• Many-to-many: use a join table with two foreign keys (book_authors).
• Constraints (not null, unique, check) are free correctness: let the database enforce what it can.

CREATE TABLE customers (id BIGINT PRIMARY KEY, email TEXT NOT NULL UNIQUE, name TEXT NOT NULL);
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE order_items (
  order_id BIGINT NOT NULL REFERENCES orders(id),
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity INT NOT NULL CHECK (quantity > 0),
  unit_price_cents INT NOT NULL,            -- copied on purpose: the price at purchase time
  PRIMARY KEY (order_id, product_id)
);

Notice unit_price_cents is deliberately duplicated: the order must remember what was charged even if the product price changes. That is a snapshot, not an anomaly; the distinction is whether the value should change when the source changes.

Denormalisation: breaking the rules on purpose

Normalised data needs joins to read. Sometimes reads dominate and joins are too costly. Common, deliberate denormalisation:

• Precomputed counts and totals (likes_count on a post) updated with each change.
• Copies of frequently read fields (author name on a post row) to avoid a join.
• Summary or reporting tables rebuilt periodically.
• Document stores that embed related data inside one record.

The price is keeping copies in sync: update both in one transaction, rebuild from the source on a schedule, or accept eventual consistency. Rule of thumb: normalise first; denormalise a specific, measured hot path, and document it.

OLTP and analytics

Transactional systems (OLTP) favour normalised schemas for correct writes. Analytical systems (OLAP) favour wide, denormalised tables, often a star schema (a central fact table of events with dimension tables around it), because they scan lots of data and rarely update it.

Habits that prevent pain

• Model the real-world facts first, then tables.
• Use clear, consistent names; pick one convention.
• Store money as integers (minor units) or exact decimals, never floating point.
• Store timestamps in UTC with time zone awareness.
• Prefer soft deletes (a deleted_at column) only where you need history, and index accordingly.
• Plan changes as migrations (small, reversible, tested), not manual edits.

Real-world reading