P-6.4 Designing a Schema That Holds Up

What this is and why it exists

Normalisation has a reputation for being theoretical. The idea underneath it is entirely practical: store each fact once, so it cannot be updated in one place and left stale in another.

Third normal form is where the returns level off for most applications. Going further is a specialist activity, and stopping there is not laziness.

Denormalising on purpose is legitimate. The difference between that and a badly designed schema is whether you can say what you traded and why.

The vocabulary

• Schema — the tables, columns, keys and rules together.
• Normalisation — organising tables so each fact is stored once.
• Composite key — a primary key made of more than one column.
• Functional dependency — one column's value determining another's.
• Constraint — a rule the database enforces on write.
• Denormalising — deliberately duplicating data to make reads cheaper.

The mental model

Start from the description of the problem. Nouns tend to become tables, and relationships between them become keys. That gets you most of a first draft in half an hour. The draft is then tested against the questions the data must answer. If a question you know is coming needs contortion, the draft is wrong, and it is cheap to change now.

The governing principle is one sentence. Repeated data is a defect waiting to happen. The same fact stored in two rows will eventually be updated in one of them. Then the two disagree, and nothing reports it. Every normal form is a different way of saying that same thing.

Here they are in plain terms. First: one value per cell, not a comma-separated listing. Second: no column depending on only part of a composite key. Third: no column depending on another column that is not a key. Most real schemas stop at the third and are right to.

Then constraints, which is where the design becomes enforcement. Uniqueness, required values, allowed ranges, referential rules — all of these belong in the schema rather than in every application that writes to the table. A rule enforced in one place cannot be forgotten in another. And there will be another: a migration script, an admin tool, a one-off fix at midnight.

Finally, denormalising. Duplicating data to make a common read cheaper is a real trade against update complexity, and sometimes it is the right one. What makes it engineering rather than sloppiness is writing the reason down next to the schema. A duplicated column with a comment explaining what it buys is a decision. The same column with no comment is indistinguishable from a mistake, including to you in a year.

What you should now be able to explain or do

Turn a description of a problem into tables and keys, then test the draft against the questions it must answer. State why repeated data is a defect. Give first, second and third normal form in plain terms and say why most schemas stop there. Put constraints in the schema rather than in applications, and say why. Denormalise deliberately and record the reason.

Check yourself