Relational Modelling That Holds Up

1One fact, one place

Normalisation has an intimidating vocabulary and one idea underneath it: store each fact exactly once, so that changing it is one write and nothing can disagree with anything.

The failure it prevents has a name — an update anomaly — and it looks like this. A table holds a customer’s name on every invoice row. The customer changes name. Now some rows say one thing and some say another, and there is no way to tell which are stale, because both are just values in a column.

2Keys

A key is the answer to “how do I refer to this row for the rest of time?” Getting it wrong is not fatal and is unusually annoying to fix, because every other table points at it.

A natural key is a value from the domain: an email address, an ISBN, a VAT number. It has real meaning and it removes a join, because the referring table already holds something readable. Its problem is that domain values change. People change email addresses; countries revise identifier formats; companies merge and reissue registration numbers. When a natural key changes, every referring row must change with it.

A surrogate key is a value with no meaning — an integer or a UUID — that exists only to identify the row. Because it means nothing, nothing can make it wrong. That is its entire advantage and it is a large one.

The practical answer for most systems is a surrogate primary key and a unique constraint on the natural key. You get stable references and the database still refuses to store two customers with the same email. That is not a compromise; it is recognising that identity and uniqueness are two different questions.

One more distinction worth having: the id in your database and the id in your URLs need not be the same thing. Sequential integers in public URLs tell every visitor how many customers you have and let them walk the range. A separate public identifier — a UUID or a short opaque code — costs one column and removes that.

3Normalise, then stop on purpose

The three normal forms people actually use can be stated in three sentences, and the fourth sentence — where to stop — is the one that matters.

• First normal form: one value per cell. No comma-separated lists, no phone1, phone2, phone3. The moment you write a column ending in a number, you have a child table.
• Second normal form: every non-key column depends on the whole key. Only bites when the key is composite — if a table keyed on (order, product) holds the product’s name, that name depends on half the key and belongs elsewhere.
• Third normal form: no non-key column depends on another non-key column. If a row holds a postcode and the city that postcode implies, the city is a fact about the postcode, not about this row.

Then stop. Third normal form is where the returns flatten, and the well-worn advice — normalise until it hurts, denormalise until it works — is honest about what comes next. Every denormalisation after that point should be a written decision with a reason, and the reason should be a measured query, not an intuition.

4Constraints are executable documentation

A comment describing a rule decays. A validation function is skipped by the migration script, the admin tool and the data fix somebody ran by hand at midnight. A constraint is checked on every write, from every source, with no exceptions.

The five that carry almost all the weight:

Try it. The schema below has the constraints; the statements underneath try to violate them.

5Nullable is a decision, usually a bad one

A nullable column is a column with an extra state, and that state has to mean exactly one thing. When it means three, every query about it is wrong in a way nobody can see.

Consider paid_on, nullable. What does null mean? Not paid yet, plausibly. But over time it also comes to mean: paid before we started recording dates; written off; paid in a way the importer could not parse. Now WHERE paid_on IS NULL silently answers a different question from the one you asked, and no error is ever produced.

Three defences, in order of preference:

1. Make it NOT NULL with a sensible default where one exists. An empty string is usually a lie; a zero is usually a lie; but a status of 'unknown' is honest and forces the reader to handle it.
2. Split the table when the nullable columns arrive in groups. If paid_on, paid_amount and payment_reference are all null together or all present together, that is a payment, and it is a row in another table.
3. Write down what null means when you keep it, in a comment on the column, and add a CHECK that stops the impossible combinations — a paid_on with no paid_amount, for instance.

Money deserves its own paragraph, because the mistake is universal and permanent. Never store money in a floating-point column. A float cannot represent 0.1 exactly, so sums drift, comparisons fail, and the drift is invisible until an accountant finds a penny. Store an integer number of minor units — pence, cents — and divide only when you display. Every currency in this course is total_pence INTEGER for that reason, and retrofitting it later means rewriting every row and every query that touches it.

6Make the impossible states impossible

The highest form of this chapter is a schema in which the bad state cannot be written at all — not checked for, not caught, not reported. Simply not representable.

Two techniques get you most of the way.

A partial unique index enforces “at most one of these”. Suppose each customer may have one primary contact. The obvious approach is a boolean and a rule that only one row may set it, enforced somewhere in application code. The better approach is a unique index that only applies to the rows where the flag is set:

CREATE UNIQUE INDEX one_primary_contact
    ON contacts (customer_id)
 WHERE is_primary = 1;

-- A second primary contact for the same customer is now
-- rejected by the engine. A second NON-primary contact is
-- fine, and so is a primary contact for another customer.

A transitions table makes an illegal state change unrepresentable. Instead of validating in code that an invoice may go from sent to paid but not from paid back to draft, put the legal pairs in a table and make the event log reference them with a composite foreign key. An illegal transition then fails to insert, because the pair it names does not exist:

CREATE TABLE transitions (
  from_status TEXT NOT NULL,
  to_status   TEXT NOT NULL,
  PRIMARY KEY (from_status, to_status)
);
INSERT INTO transitions VALUES
  ('draft','sent'), ('draft','void'),
  ('sent','paid'),  ('sent','void');

CREATE TABLE invoice_events (
  id INTEGER PRIMARY KEY,
  invoice_id  INTEGER NOT NULL,
  from_status TEXT NOT NULL,
  to_status   TEXT NOT NULL,
  FOREIGN KEY (from_status, to_status)
    REFERENCES transitions (from_status, to_status)
);

-- 'paid' -> 'draft' is not in transitions, so the insert fails.
-- The state machine is now data, and it is enforced by the engine.

The second technique has a property worth naming: the rules became data. Adding a legal transition is an insert rather than a deploy, and the current rules can be queried, which means they can be shown in an interface and reviewed by someone who does not read code.

✓Checkpoint

Five questions. Commit before you read the explanation.

▶Playground

The full pattern in one schema: a partial unique index, a composite-key state machine, and constraints on everything. Try to break it.

✓Exercise set

Twelve problems, mostly DDL. Several of them ask you to write a schema and are then checked by trying to insert the data that should be impossible — if your constraint is missing, the bad row goes in and the test tells you which one. Your work is saved in this browser.