Normalization vs Denormalization

Normalisation: one fact, one place

Here is an unnormalised orders table. Customer details are repeated in every order row:

orders
┌────┬───────────┬───────────────┬───────────┬──────────────┬───────┐
│ id │ customer  │ cust_email    │ cust_city │ product      │ price │
├────┼───────────┼───────────────┼───────────┼──────────────┼───────┤
│ 1  │ Asha      │ asha@ex.com   │ Pune      │ Shoes        │ 1299  │
│ 2  │ Asha      │ asha@ex.com   │ Pune      │ Socks        │ 199   │
│ 3  │ Ravi      │ ravi@ex.com   │ Delhi     │ Shoes        │ 1299  │
└────┴───────────┴───────────────┴───────────┴──────────────┴───────┘

This causes three kinds of anomalies (wrong or lost data caused by the table design):

• Update anomaly: Asha moves to Mumbai. You must update every one of her orders. Miss one and the data disagrees with itself.
• Insert anomaly: you cannot store a new customer until they place an order.
• Delete anomaly: if you delete Ravi's only order, you also lose the fact that Ravi exists.

Normalised version:

customers                  products                orders
┌────┬──────┬─────────┐    ┌────┬───────┬───────┐   ┌────┬─────────┬────────────┐
│ id │ name │ city    │    │ id │ name  │ price │   │ id │ cust_id │ product_id │
├────┼──────┼─────────┤    ├────┼───────┼───────┤   ├────┼─────────┼────────────┤
│ 1  │ Asha │ Pune    │    │ 10 │ Shoes │ 1299  │   │ 1  │ 1       │ 10         │
│ 2  │ Ravi │ Delhi   │    │ 11 │ Socks │ 199   │   │ 2  │ 1       │ 11         │
└────┴──────┴─────────┘    └────┴───────┴───────┘   │ 3  │ 2       │ 10         │
                                                    └────┴─────────┴────────────┘

Each fact now lives in one place. If Asha's city changes, you change one row. Here cust_id and product_id are foreign keys (columns that point to a row in another table).

The normal forms, in plain English

A normal form is a set of rules for a table design. Each higher form adds a rule. A primary key is the column (or columns) that identifies each row.

• 1NF (First Normal Form): each cell holds one value. A list like "Shoes, Socks" in one cell is not allowed. Each row must also be unique.
• 2NF: every non-key column depends on the whole primary key, not just part of it. This matters for tables with composite keys (a primary key made of two or more columns).
• 3NF: non-key columns depend only on the key, not on other non-key columns. For example, cust_city depends on the customer, not on the order. So move it to customers.
• BCNF (Boyce-Codd Normal Form): a slightly stricter version of 3NF.

A popular way to remember 3NF: every column should depend on "the key, the whole key, and nothing but the key". For most apps, 3NF is a good, practical goal.

An important exception: historical facts

Look at price again. Suppose the price of shoes changes tomorrow. Yesterday's order must still show what the customer actually paid. So an order line should store price_at_purchase. This is not bad duplication. It is a different fact: the price at that moment. Good modelling asks: is this the same fact, or a snapshot of it at one point in time? Addresses on invoices and names on tickets work the same way.

Denormalisation: duplicate on purpose

Normalised data needs joins to put the pieces back together. On one database this is usually fine. But at high scale, in NoSQL databases, or across shards (parts of a database split over machines), joins become slow or impossible. So we denormalise. This means we store data in the shape in which it is read.

These are common techniques:

1. Copy a few fields to avoid a join.

-- copy a rarely-changing field to skip a join on the feedALTER TABLE posts ADD COLUMN author_name TEXT;

The feed can now show author names without joining the users table.

2. Precomputed counters.

-- precomputed counters instead of COUNT(*) on every page viewALTER TABLE posts ADD COLUMN comment_count INT NOT NULL DEFAULT 0,                  ADD COLUMN like_count    INT NOT NULL DEFAULT 0;

Do not run COUNT(*) over millions of comment rows on every page view. Instead, change the counter when a comment is added.

3. Embedded documents (NoSQL style). Put the related data inside the main record.

{ "orderId": 7, "customer": {"id": 1, "name": "Asha"}, "items": [...] }

4. Materialised views. A materialised view is a saved copy of the result of a query. The database stores it like a table and you refresh it when you want:

CREATE MATERIALIZED VIEW daily_sales ASSELECT date(created_at) AS day, SUM(total) AS revenueFROM orders GROUP BY 1;REFRESH MATERIALIZED VIEW daily_sales;  -- e.g., every few minutes

5. Read models / CQRS. CQRS (Command Query Responsibility Segregation) means you use separate models for writing and for reading. You keep a normalised "write model". You also keep one or more denormalised "read models" built from it, often through events. Examples are a search index or a feed table for each user (Part 6).

Keeping copies in sync

Every copy is a promise that you must keep. These are your options:

• Update the copies in the same transaction (when they are in the same database). A transaction makes all changes succeed together or fail together (Lesson 21). This is the most correct option.
• Application logic ("when a user renames, also update their posts"). It is easy to forget this in one of the code paths.
• Database triggers. A trigger is code that the database runs by itself when a row changes. It works automatically, but it is hidden and can surprise people.
• Events / Change Data Capture (CDC): send a "user renamed" event (or read the database change log) and let a worker update the copies. This is reliable at scale. But the copies are briefly stale (out of date for a short time). This is called eventual consistency.
• Accept staleness for copies that matter little ("author name on old posts may be a few minutes late").

Also ask: how often does this field change? Copying a user's name (it changes rarely) is low risk. Copying their follower count (it changes all the time) needs a clear update plan.

Where each approach fits