One Fact, One Place: Normalization Without the Fear

The bookshop you’ve queried for three series is split across five tables, and that split is not an accident. It is the result of a design discipline called normalization. Here’s why it’s there. A table that stores the same fact in more than one place will, eventually, store two different versions of that fact — with no way to tell you which is right. Normalization is the set of rules that stops that from happening. It sounds like theory. It is actually the most practical thing in schema design, and the fastest way to feel why is to build a table that gets it wrong.

The flat table, and the anomaly it invites

Imagine we’d designed the bookshop as one wide table instead of five. Every order line carries everything about that line: the order, the customer, the book, the author, all in one row. We can build exactly that from the real data:

CREATE TABLE scratch.orders_flat AS
SELECT o.order_id, o.order_date, o.status,
       c.customer_id, c.name AS customer_name, c.email AS customer_email, c.city,
       oi.book_id, b.title, b.genre, oi.unit_price, oi.quantity,
       a.author_id, a.name AS author_name, a.country AS author_country
FROM orders o
JOIN customers c    ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN books b        ON b.book_id = oi.book_id
JOIN authors a      ON a.author_id = b.author_id;

SELECT 149942

One hundred and forty-nine thousand rows, and look at what’s now repeated in them. Customer 1’s email is not stored once. It’s stored in every row for every line of every order they ever placed:

SELECT customer_id, customer_email, count(*) AS rows_holding_this_email
FROM scratch.orders_flat
WHERE customer_id = 1
GROUP BY customer_id, customer_email;

 customer_id |    customer_email     | rows_holding_this_email
-------------+-----------------------+-------------------------
           1 | [email protected] |                      13

Thirteen copies of one fact. Now the customer changes their email, and you write the update. But your WHERE clause is a shade too narrow — it misses the returned orders:

UPDATE scratch.orders_flat
SET customer_email = '[email protected]'
WHERE customer_id = 1 AND status <> 'returned';

UPDATE 8

Eight rows updated. Five left behind. Ask the table what customer 1’s email is now, and it gives two different answers:

SELECT customer_email, count(*) AS rows
FROM scratch.orders_flat
WHERE customer_id = 1
GROUP BY customer_email ORDER BY rows DESC;

     customer_email      | rows
-------------------------+------
 [email protected] |    8
 [email protected]   |    5

That is an update anomaly, and it’s the whole argument in one screen. The table now holds a contradiction, no constraint was violated, no error was raised, and there is no correct answer to “what is this customer’s email.” It has two, because the fact was stored in two kinds of place at once. The flat design carries two more anomalies from the same flaw. There’s an insertion anomaly: you can’t record a new customer until they’ve bought something, because a customer only exists as part of an order line. And a deletion anomaly: delete a customer’s last order line and you erase their email and city along with it. Same root cause every time: one fact living in more than one place.

Functional dependencies, plainly

The tool that makes this precise is the functional dependency. Write A → B and read it as “A determines B”: if you know A, there is exactly one B that goes with it. In the flat table, customer_id → customer_email — a given customer has one email. Likewise book_id → title, book_id → author_id, author_id → author_country. And the whole row is identified by the pair (order_id, book_id), because that’s what makes a line unique: one book, one order.

Normalization is just the exercise of lining up every functional dependency and insisting that each one live in a table whose key is its left-hand side. When a dependency’s left-hand side is not the table’s key, the fact on the right gets duplicated — once per row that repeats the left. That duplication is the anomaly, waiting.

The normal forms, as one idea

The normal forms are a graded checklist for exactly this. They read as jargon and mean something simple.

First normal form (1NF): every value is atomic. No arrays in a cell, no "comma,separated,list" pretending to be one field, no repeating phone1, phone2, phone3 columns. The flat table is already in 1NF, and so is anything the relational model lets you build cleanly, because a column holds one typed value by definition.

Second normal form (2NF): no column depends on only part of a composite key. Our key is (order_id, book_id). But customer_email depends on order_id alone (through the customer), and title depends on book_id alone. Those are partial dependencies, and they’re why the email repeats. It’s pinned to order_id, but the row’s key drags book_id along too, so it copies once per book in the order. The fix is to split off each partial dependency into its own table.

Third normal form (3NF): no column depends on another non-key column. In the flat table author_country depends on author_id, which depends on book_id. That’s a transitive dependency — book_id → author_id → author_country — and it’s why an author’s country repeats in every row for every book they wrote. The fix, again, is a table: authors get their own, keyed by author_id.

Boyce-Codd normal form (BCNF): a stricter 3NF. Every determinant — every left-hand side of a dependency — must be a candidate key of its table. In practice, if you’ve chased every dependency into a table whose key is that dependency’s left side, you’re already at BCNF. The higher forms (4NF, 5NF) exist for multi-valued oddities you’ll rarely meet; BCNF is the working ceiling.

You do not memorize these. You do the one thing they all describe: put every fact in a table keyed by the thing it’s a fact about. Facts about a customer go in customers, keyed by customer_id. Facts about a book go in books. Facts about a line go in order_items, keyed by (order_id, book_id). That decomposition is the five-table bookshop. The normal forms are the receipts.

The fix, and why it can’t go wrong

Once the email lives in exactly one place, the anomaly is not fixed, it’s impossible. There is no second row to fall out of sync with. The real bookshop is already there:

SELECT count(*) AS rows_holding_email FROM customers WHERE customer_id = 1;

 rows_holding_email
--------------------
                  1

One row. So changing the email is a single-row write, and no WHERE clause slip can leave two versions behind:

UPDATE customers SET email = '[email protected]' WHERE customer_id = 1
RETURNING customer_id, email;

 customer_id |            email
-------------+------------------------------
           1 | [email protected]

The 149,942-row flat table needed a careful, error-prone bulk update to change one fact. The normalized schema needs one row. That gap is the entire return on normalizing, and it compounds with every fact you store.

When to denormalize on purpose

So why does anyone ever store a fact twice? Because normalization optimizes for writes and for truth, and sometimes you’re optimizing for reads. A normalized query for “revenue per genre per month” has to join order_items to books to orders and aggregate, every single time it runs. If that query powers a dashboard a thousand people load an hour, re-deriving it from normalized tables is wasteful.

That’s when you denormalize deliberately: you copy or pre-aggregate a fact so reads get cheaper, and you accept the cost — now you own keeping the copy in sync. The disciplined version of this is a star schema, where a central fact table carries foreign keys out to dimension tables and pre-joined, pre-cleaned attributes sit ready for slicing. That’s a design choice with its own rules, not an accident of a lazy schema, and the whole Dimensional Modeling series is about doing it well. The line to hold in your head: normalize until it hurts, then denormalize until it works. And only ever with your eyes open, knowing exactly which fact you’ve chosen to duplicate and who’s now responsible for the copies. The flat table above duplicated by accident, and that’s the version that corrupts.

Final thoughts

Normalization has a fearsome reputation and one honest rule underneath it: store every fact once, in a table keyed by the thing it describes. The update anomaly we triggered — one customer, two emails, no error — is what you’re buying insurance against. The normal forms from 1NF to BCNF are just increasingly precise ways of naming the same insurance. Decompose along your functional dependencies and the bookshop’s five tables fall out on their own. Denormalize later if reads demand it, but do it on purpose, in the open, with a plan for the copies. And the moment you split one fact across many tables, you inherit a new obligation: the pointers between them have to stay honest. That’s the job of a key, and it’s next.

Next: Keys that keep their word — primary and foreign keys, and the referential actions that decide what happens to a child when its parent is deleted.