What Is Normalization For?

Normalization is a set of rules about where each fact lives. The goal is that every fact is stored in exactly one place, so that changing it means changing it once. The payoff is not tidiness; it is that the data cannot drift out of sync with itself.

-- Unnormalised: the customer city is repeated on every order row.
CREATE TABLE orders_flat (
  id INTEGER PRIMARY KEY,
  customer_name VARCHAR(120) NOT NULL,
  customer_city VARCHAR(80) NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders_flat (id, customer_name, customer_city, amount) VALUES
  (1, 'Ada Lovelace', 'London',   120.00),
  (2, 'Ada Lovelace', 'London',   340.50),
  (3, 'Grace Hopper', 'New York',  75.25);

-- Ada moves to Cambridge. Only one copy gets updated.
UPDATE orders_flat SET customer_city = 'Cambridge' WHERE customer_name = 'Ada Lovelace';

SELECT id, customer_name, customer_city FROM orders_flat ORDER BY id;

Three rows come back, and the data is now wrong: order 1 says Cambridge while order 2 still says London, and both belong to the same person. The city is stored in two places and the two copies disagree. This is an update anomaly, and it is not a hypothetical — a duplicated value that is updated through one path but not the other is one of the most common data-quality bugs there is.

There are two more anomalies that the same duplication causes. An insertion anomaly: you cannot record a new customer without creating an order for them, because there is nowhere to put the customer's details on their own. And a deletion anomaly: if a customer has exactly one order and you delete that order, you lose the customer's name and city entirely, because their only copy was in the row you removed.

Normalization removes all three by separating the facts into their own tables, so that the customer table holds the name and city once and the orders table holds the amounts, joined by an id. The cost is joins: to show a customer's city next to their orders you now need a join on every query, and a report that flattens it back is doing the same work the duplicated column was saving.

When you have measured a specific query that is too slow, and you know exactly which duplicated value is causing the cost. Not before. Denormalization without a measurement is just the first form of the problem, and it trades a clean schema for stale data.

The reasons it is legitimate are specific. A read-heavy workload on a huge table — a news feed, an activity log, an events table with billions of rows — where joining three large tables for every read costs more than the duplication. A reporting workload where the same aggregate is computed over and over and a pre-computed column removes that work. A distributed system where you deliberately copy data into a store shaped for the read, so the read never has to cross a network hop to assemble it. And a very large table where the join is across a shard boundary, so a denormalized copy keeps everything local.

What you accept in exchange is a consistency problem, and the cost of solving it should be part of the decision. Either the copy is updated synchronously, in the same transaction as the original, which means the write path gets slower and more complex. Or it is updated asynchronously, which means the copy can be stale, and you have to decide how stale is acceptable and how anyone finds out. Or it is never updated, which means it is a snapshot and you have to say so.

The strong rule is to denormalize a specific query, not a whole table. If one report needs the customer's city next to each order, add that column to a reporting view or a summary table rather than to the transactional orders table. The transactional schema stays normalised, the read model is shaped for the read, and the synchronisation job is explicit and owned.

The normalised schema stores the customer once and the orders separately, and a join reassembles the flat view for reporting.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  city VARCHAR(80) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (id),
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers (id, name, city) VALUES
  (1, 'Ada Lovelace', 'London'),
  (2, 'Grace Hopper', 'New York');

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25);

-- The city is stored once. Changing it fixes every order at once.
UPDATE customers SET city = 'Cambridge' WHERE id = 1;

SELECT o.id, c.name, c.city, o.amount
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
ORDER BY o.id;

Three rows come back, and Ada's city reads Cambridge on both of her orders — the join reassembles the flat view from the normalised storage, so the report looks exactly like the denormalised table while the underlying data has only one copy of the fact.

The join cost is the price paid for the consistency, and it is worth being able to quantify it. Here it is a primary-key lookup per order row, which is cheap. It would be much more expensive if the join were across a large dimension table with no index, or if the report had to aggregate across millions of rows every time it ran. That is the situation where a denormalised read model starts to make sense.

One nuance worth raising: normalisation does not mean a wide join is always the answer. If the same customer details are needed in a hot path, a covering index on the joined columns lets the engine satisfy the query from the index alone without touching the table, which removes most of the cost without duplicating any data. That is usually the better first move before denormalising.