Schema design decides which bugs are possible

A schema is not a container for data; it is a statement about which states the system is allowed to reach. Every constraint you declare removes a class of invalid data permanently, for every writer — the application, a migration script, a background job, an admin console, someone at a psql prompt at 2am. Every rule you leave to application code is a rule that holds only as long as every writer remembers it. That distinction is the whole subject.

Normalisation is about eliminating redundancy

Normal forms are a formal way of saying "store each fact once".

First normal form requires each column to hold a single value from its domain, with no repeating groups. A tags column containing "urgent,billing,escalated" violates it — you cannot index it, constrain it or join to it without string surgery, and a tag containing a comma corrupts the row.

Second normal form requires every non-key column to depend on the whole primary key, not part of it. In a table keyed by (order_id, product_id), a customer_name column depends only on the order, so it belongs elsewhere.

Third normal form requires non-key columns to depend on the key and nothing but the key. If orders stores customer_id, customer_email and customer_city, the last two depend on the customer rather than the order — so a customer changing email requires updating every one of their orders, and any missed row makes the database self-contradictory. Boyce–Codd tightens this to cover the cases where a candidate key other than the primary one is involved.

The informal summary — "every non-key attribute depends on the key, the whole key, and nothing but the key" — is genuinely a fair working definition of third normal form.

What normalisation buys is precise: an update touches one row, so an update cannot leave the database inconsistent. What it costs is joins at read time. Both halves are real.

Denormalisation is a decision, not a failure

Deliberate denormalisation is legitimate when read patterns demand it, and it is not the same thing as an unnormalised design that nobody thought about. The difference is that a deliberate one names what it is trading.

Storing an order's total_amount rather than summing its lines each time is denormalisation. Storing the price at time of purchase on the order line is not — that is a genuinely different fact from the product's current price, and a normalised design must store it, because the product price will change and the historical order must not.

That distinction catches people out constantly. Before duplicating a value, ask whether it is the same fact or a point-in-time snapshot of a fact. Snapshots belong in the child row; copies of a current value are redundancy.

When you do duplicate a current value, you have taken on responsibility for keeping the copies consistent, and you must say how: a trigger, a scheduled reconciliation, an application transaction that updates both. "We'll remember" is not a mechanism. Adding a periodic check that recomputes the derived value and alerts on divergence is what separates a maintained denormalisation from a slowly rotting one. A generated column, where the database computes and stores it, avoids the problem entirely when the value derives from the same row.

Keys

A natural key comes from the data's meaning — an ISBN, a country code, an email. A surrogate key is system-generated with no business meaning.

My default is a surrogate primary key plus a unique constraint on the natural key. That combination gives stable references — business identifiers change, and a changing key must cascade through every referencing row — while still enforcing the real-world uniqueness rule. Dropping the unique constraint because "the id is the primary key" is precisely how duplicate customers appear.

Between sequential integers and UUIDs: a sequence is compact and gives good index locality, but it leaks volume and is guessable in a URL. A random UUID fixes both and can be generated by the client without a round trip, which matters for distributed or offline-capable writers — but it is wider and scatters index inserts across the B-tree, hurting write locality. Time-ordered variants such as UUIDv7 recover most of the locality while keeping client generation.

In a junction table, the composite of both foreign keys usually is the identity, and making it the primary key enforces pair uniqueness for free. Add a reverse index on the second column, because a composite key only serves lookups leading with its first column.

Constraints are the guarantees

NOT NULL for required values. UNIQUE for real-world uniqueness — noting that standard UNIQUE permits multiple NULLs, since two unknowns are not known to be equal. FOREIGN KEY for relationships, with the ON DELETE action chosen deliberately: RESTRICT refuses to orphan rows and is the safe default, CASCADE suits genuine composition where the child has no meaning alone, SET NULL suits optional references.

CHECK encodes row-level invariants — a non-negative quantity, an end date at or after a start date, a status within a known set. A partial unique index enforces uniqueness over a subset, which is how you allow one active record per user while keeping history. An exclusion constraint prevents overlapping ranges, which is the correct way to stop double-booking a room and is almost always attempted incorrectly in application code, where it races.

That racing point generalises. Any rule enforced by "SELECT to check, then INSERT" is a race: two concurrent transactions both see nothing and both insert. Under snapshot isolation neither sees the other's uncommitted row. The database constraint is the only thing that makes such a rule atomic, which is why uniqueness belongs in the schema rather than in a validator.

Modelling choices that recur

Optional attributes: a nullable column is fine for genuinely optional data. Dozens of mostly-null columns usually signal that several entity types have been merged into one table, and either separate tables or a documented single-table pattern would be clearer.

Type over string: use a real date, boolean, numeric or enumerated type rather than text that looks like one. The type is what lets the database validate, compare and index correctly. Money is numeric, never floating point. Timestamps are stored with time zone in UTC and converted at the edges.

Enumerations: a CHECK constraint on a small stable set is simple; a lookup table is better when the set changes, needs attributes, or must be referenced by other data. Native enum types are convenient but adding or removing values is a schema change.

JSON: appropriate for genuinely variable per-row attributes, opaque third-party payloads captured for audit, or sparse optional fields. Fields that are frequently queried, joined, aggregated or strongly constrained are usually better promoted to typed columns, even though jsonb itself is queryable and indexable. The failure mode to look for in review is a JSON blob whose keys are in fact fixed and known: that is a table someone did not want to write a migration for, and it will cost more later than the migration would have.

History: if the question "what did this look like last month" will ever be asked, model it now. Retrofitting history onto a schema that overwrites in place is expensive and the past is unrecoverable. An append-only event table, or validity ranges on a versioned row, both work; overwriting and hoping is what does not.

Evolving a schema safely

Schemas change under load, and the sequence that makes changes safe is expand, migrate, contract. Add the new structure as optional; deploy code that writes both old and new; backfill existing rows in bounded batches; then deploy code that reads the new; then remove the old. Each step is independently reversible, and the reason the write and read steps must be separate deployments is that during a rollout both versions are running at once.

The operational hazards are lock level and rewrite. Adding a nullable column is a fast catalogue change; adding a constraint that must validate every existing row takes a long lock unless added NOT VALID and validated separately. Rehearse on a production-sized copy, watch replication lag during backfills, and never combine a destructive migration with an application release.

Worked example: the paid price is a snapshot, not redundancy

Invoice 4419 sold SKU A at $12.50. Marketing later sets products.price to $19.00.

schema	invoice 4419 line amount	history
line joins products.price	$19.00	rewritten
order_lines.unit_price numeric(12,2) snapshot	$12.50	preserved

A foreign key to the live catalogue is not a historical fact. The snapshot column is the order.