Normalization
A Socratic walk-through of normalization — reasoned out one step at a time, not lectured.
The question we started with
THE QUESTION #Why split one sensible-looking table into six that nobody would draw by hand?
Sketch a table of orders on paper and you get something readable: order number, date, customer name, customer city, product name, unit price, quantity. One row, one order line, everything you need in front of you. Anyone could read it.
A database designer takes that and returns six tables, and now no single row means anything on its own. The obvious explanation is that this saves space by not repeating the customer's city on every line. That is true, and it is much the least interesting reason. Storage is cheap. So what is the expensive thing being bought?
Reasoning it through
REASONING #Do not reason about the shape of the table. Reason about what happens when the world changes.
A customer moves city. In the flat table, that fact lives on every row where they appear — two hundred rows, say. To record one change in the world you must make two hundred changes in the database, and they must all succeed. Miss three and the database now asserts two different cities for one customer. Ask yourself: which of those rows is right? Nothing in the data can tell you. There is no fact of the matter recorded anywhere; the truth has been shredded into copies with no original.
That is the first anomaly, and it is worth naming plainly: an update anomaly. Now look for its siblings.
A customer signs up but has not ordered yet. Where do you put them? Every row in the flat table is an order line, so a customer with no orders has nowhere to exist — unless you invent a row with empty product fields, which is a fiction the schema now contains. That is an insertion anomaly.
And the reverse. A customer's only order is cancelled and the row is deleted. Their address goes with it, though nothing about the world says the address stopped being true. That is a deletion anomaly. Three symptoms, and notice they all have the same cause: the table is recording facts about more than one kind of thing at once. Customer facts and order-line facts are tangled in one row, so an operation about one is forced to be an operation about the other.
That gives the actual principle, and it is sharper than "avoid duplication". Each fact should be stated once, in the place determined by what it is a fact about. The customer's city is a fact about the customer, so it belongs in a row that represents the customer, and nowhere else. Splitting into six tables is not decomposition for tidiness; it is sorting facts to their subjects.
The formal machinery makes this precise. A functional dependency says one set of columns determines another: customer identifier determines city. Trouble arises when a table contains a dependency whose left-hand side is not the table's key — the flat table is keyed by order line, yet carries a dependency from customer. The normal forms are a ladder of increasingly strict statements of that rule. Third normal form, the one most working schemas aim at, is reached when every non-key column depends on the key, the whole key, and nothing but the key. Boyce-Codd normal form tightens it slightly; fourth and fifth handle rarer multi-valued cases and are seldom the deciding factor in practice.
And what does that buy? This is the part worth being precise about. It means a whole class of contradiction is impossible by construction rather than prevented by careful code. If the city is stored once, no sequence of updates can ever make the database hold two cities for one customer. You have not written a rule that must be enforced everywhere; you have removed the ability to express the contradiction at all. That is the expensive thing being bought, and it is much more valuable than the saved bytes.
The cost is real too, and it should not be waved away: a question that was one row is now a join across several tables, and both the query and the schema are harder for a human to read at a glance.
The analogy
THE ANALOGY #Think of an address book kept as a stack of envelopes, each one carrying the recipient's full address written out again. When someone moves, correcting the record means finding and rewriting every envelope — and the day you stop halfway, the stack disagrees with itself and no envelope is more authoritative than another. A proper address book writes the address once and lets each envelope carry only a name pointing to it.
An envelope with a stale address still reaches the old house, so the error is visible on delivery, whereas a database holding two cities for one customer answers both queries confidently and reports nothing wrong at all.
Clarifying the model
THE MODEL #The most common misreading is that normalization is about saving space. Follow that reading and you conclude, correctly, that storage is cheap and therefore normalization is optional — and you lose the actual benefit, which is that certain wrong states cannot be represented. Space is a side effect.
The second misreading runs the other way: that more normal forms are always better and a schema should be pushed as far up the ladder as it will go. The forms are a diagnostic vocabulary, not a score. They tell you precisely which anomalies a given design admits; whether you accept those anomalies is a separate judgement, and there are good reasons to accept some — which is the whole subject of denormalization.
Worth adding: a normalized schema and a normalized set of stored bytes are not the same commitment. You can design to third normal form to think clearly about the facts, then deliberately keep a redundant copy somewhere for speed — as long as you know which copy is derived and which is the original. Trouble begins when nobody can say which is which.
A picture of it
THE PICTURE #How to readEach box is one kind of thing, and every column sits in the box whose key determines it — the city with the customer, the list price with the product. Trace the crow's feet to see the cardinality: one customer places zero or more orders, each order contains one or more lines. The flat table we started with was all four boxes flattened into the bottom one, which is exactly what made the customer's city repeat.
What became clearer
WHAT CLEARED #Normalization is not tidiness and it is not compression. It is the practice of putting each fact in the one place its subject determines, so that entire classes of contradiction — two cities for one customer, a customer who cannot exist without an order — become unrepresentable rather than merely discouraged. The joins are what you pay for that guarantee.
Where to go next
ONWARD #- Working through the ladder concretely: what exactly first, second and third normal form each rule out.
- Boyce-Codd normal form, and the small cases where third normal form is not quite enough.
- Denormalization: when accepting a known anomaly is the right trade, and how to keep it honest.
Key terms
TERMS #| Term | What it means |
|---|---|
| Functional dependency | a rule that one set of columns uniquely determines another. |
| Update / insertion / deletion anomaly | a contradiction or loss caused by one row mixing facts about several subjects. |
| Third normal form | every non-key column depends on the key, the whole key, and nothing but the key. |
| Foreign key | a column referring to another table's key, replacing a repeated copy of that row's facts. |
Every term the collection defines is gathered in the glossary.