Normalization: one fact, one place

A spreadsheet-shaped orders table, with the customer's email copied onto every order:

It looks harmless, and reading it is easy. Writing to it is where it breaks — in three ways, traditionally called anomalies:

• Update anomaly. Ada changes her email. The account page updates the row for her latest order. Now the database holds two emails for customer 7 and no way to tell which is current:

  UPDATE orders SET customer_email = 'ada@new.io' WHERE order_id = 2;
  SELECT DISTINCT customer_email FROM orders WHERE customer_id = 7;
  -- ada@new.io
  -- ada@old.io
  

• Insert anomaly. A customer signs up but hasn't ordered. There's nowhere to store their email without inventing an order.

• Delete anomaly. Grace cancels her only order and you delete the row. Her email is gone with it.

All three have one cause: a fact about the customer is stored in a table about orders, once per order. The fix is to give each fact one home and point at it:

CREATE TABLE customers (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE
);

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (id),
  total       numeric(10,2) NOT NULL
);

Now an email change is one UPDATE of one row, a customer can exist with no orders, and deleting an order can't lose a customer. Reading the email next to an order costs a join — that's the trade normalization makes: a little more work to read, so that a write can't make the data contradict itself.

That's the whole idea behind the normal forms you'll meet next: they're rules for spotting a fact that's stored in more than one place.

An import from a shop's old system:

The first three normal forms are three questions you ask of every column. In working terms: one value per cell; every column about the whole key; and nothing but the key.

1NF — one value per cell, no repeating groups

items packs a list into one cell. You can't put a foreign key on "PEN", you can't index "how many pads were sold", and LIKE '%PEN%' also matches PENCIL. The same smell appears as numbered columns — sku_1, qty_1, sku_2, qty_2 — which cap the list at however many columns someone guessed. Give each item its own row; the key becomes (order_id, sku):

2NF — every non-key column depends on the whole key

This only bites when the key has more than one column. Go column by column and ask "to know this, do I need the whole (order_id, sku), or only part of it?"

• qty — needs both: how many of this product on this order. Stays.
• product_name — needs only sku. It's repeated on every line that sells a pen → products.
• ordered_at, customer_id, customer_email — need only order_id, and are repeated on every line of the order → orders.

3NF — and nothing but the key

Now look inside orders(id, ordered_at, customer_id, customer_email). customer_email doesn't depend on the order at all; it depends on customer_id, which isn't the key. That's the customer's fact stored once per order — exactly the update anomaly from the simple tier. Move it to customers.

The result

CREATE TABLE customers (
  id    bigint PRIMARY KEY,
  email text NOT NULL UNIQUE
);

CREATE TABLE products (
  sku  text PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  ordered_at  timestamptz NOT NULL,
  customer_id bigint NOT NULL REFERENCES customers (id)
);

CREATE TABLE order_items (
  order_id bigint NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  sku      text   NOT NULL REFERENCES products (sku),
  qty      int    NOT NULL CHECK (qty > 0),
  PRIMARY KEY (order_id, sku)
);

Every fact now lives in one place: an email in customers, a product name in products, when an order was placed in orders, and how many of what in order_items. The original wide table is one query away — a join across the four tables — and can be a view if a report wants that shape.

Microsoft's normalization guide makes the practical point worth keeping: third normal form is the level most applications need, and if you break one of the first three rules on purpose, make sure the application anticipates the redundant data that follows.

Not every repeated value is duplication. The test is whether the two values describe the same fact.

A snapshot is a different fact

CREATE TABLE order_items (
  order_id   bigint NOT NULL REFERENCES orders (id),
  sku        text   NOT NULL REFERENCES products (sku),
  quantity   int    NOT NULL,
  unit_price numeric(10,2) NOT NULL,   -- what this customer was charged
  PRIMARY KEY (order_id, sku)
);

order_items.unit_price starts out equal to products.price, but they answer different questions: "what does a pen cost today?" vs "what did order 1001 charge for a pen?". When the catalogue price changes, last month's invoices must not. "Normalizing" unit_price away by joining to products would rewrite history. The same goes for a shipping address copied onto an order (the customer may move), and a tax rate applied at checkout.

Ask: if the source changes later, should this value change too? No → it's a snapshot; store it. Yes → it's a copy; derive it.

A cache is the same fact, stored again on purpose

posts.comment_count is the same fact as SELECT count(*) FROM comments WHERE post_id = …. You might store it anyway because a feed shows 50 posts with their counts on every page view. That's a legitimate trade, but you now own the job normalization was doing for you: keeping two copies in agreement. Three rules keep a cache honest:

1. Update it in the same transaction as the source. If the comment insert commits and the counter update doesn't, they disagree forever. A trigger does this for every writer — including the ones that don't go through your API:

   CREATE FUNCTION bump_comment_count() RETURNS trigger LANGUAGE plpgsql AS $$
   BEGIN
     IF TG_OP = 'INSERT' THEN
       UPDATE posts SET comment_count = comment_count + 1 WHERE id = NEW.post_id;
     ELSE
       UPDATE posts SET comment_count = comment_count - 1 WHERE id = OLD.post_id;
     END IF;
     RETURN NULL;
   END $$;
   
   CREATE TRIGGER comments_count AFTER INSERT OR DELETE ON comments
     FOR EACH ROW EXECUTE FUNCTION bump_comment_count();
   

   (A comment that can move between posts also needs an UPDATE branch.)

2. Change it relative to itself — comment_count = comment_count + 1, never "read the count in the app, add one, write it back". Two concurrent comments would both read 5 and both write 6. The transactions unit covers why.

3. Keep a rebuild. The source is the truth; the cache must be reproducible from it:

   UPDATE posts p
   SET comment_count = (SELECT count(*) FROM comments c WHERE c.post_id = p.id);
   

When to denormalise at all

Only with a measured read problem that an index or a better query can't fix — and with all three rules above in place. The cost isn't the extra column; it's every future write path that has to know about it. A counter that one UPDATE keeps in step is cheap. A customer's email copied into five tables is five update anomalies waiting for the next feature.