Schema design: normalize, then denormalize on purpose

Normalize first: one fact, one place

Normalization is the default, and the rule of thumb that captures most of it: every fact lives in exactly one place. A customer’s email is stored once, on the users row; an order references the user by id and never copies the email. When the email changes, you update one row and every order automatically reflects the truth, because it was never duplicated. (The databases track’s relational-model chapter covers the normal forms formally; here we focus on the engineering judgment.)

The payoff is update integrity: there’s no way for two copies of a fact to disagree, because there’s only one copy. The cost is that reading a complete picture requires joins — to show an order with its customer’s current name you join orders to users. For the overwhelming majority of OLTP workloads, that join is cheap (an indexed lookup) and the integrity is priceless. Start normalized. Always.

Model with Postgres types, not strings

The throughline of this whole unit: a schema that leans on Postgres’s type system is enforced by the engine; a stringly-typed schema pushes that work into fragile application code. Concretely, prefer:

• a status order_status enum (or CHECK ... IN) over a free-text status text that can hold 'Paid', 'paid', and 'PAID';
• a tstzrange with an EXCLUDE constraint over two text columns holding ISO strings the app must parse and compare;
• numeric/integer cents over a text “amount”; timestamptz over a text date; a tags text[] (or join table) over a comma-joined string;
• a jsonb document over a serialized blob the database can’t index or validate;
• a domain (CREATE DOMAIN email AS text CHECK (value ~ '^[^@]+@[^@]+$')) to attach a reusable constraint to a semantic type.

Each of these moves a rule from “the app remembers to check” to “the engine guarantees.” A stringly-typed column is a deferred bug: it accepts garbage today and surfaces it as a production incident later.

Surrogate vs natural keys

A natural key is a column that’s already a real-world identifier (an ISO country code, a SKU, an email). A surrogate key is a meaningless engine-generated id (bigint identity or uuid). The judgment:

• Surrogate is the safe default for the primary key. It’s stable (a customer can change their email; their id never changes), compact, and uniform. Foreign keys point at the surrogate, so a natural-value change doesn’t cascade through the whole database.
• Natural keys earn their place as UNIQUE constraints (you still want users.email UNIQUE), and occasionally as the primary key when the value is genuinely immutable and externally meaningful — a currencies(code) table keyed on 'USD', 'EUR'. Even then, beware: “immutable” identifiers have a habit of changing (companies rename, countries split).

The pragmatic rule: surrogate primary key + natural unique constraints. You get a stable join target and still enforce the real-world uniqueness rule.

Denormalize deliberately — and keep it consistent

Denormalization is a performance optimization you reach for after measuring, not a starting posture. It’s right when a read is hot, the join is genuinely expensive at your scale, and the duplicated fact rarely changes — a precomputed order_total, a cached comment_count, a search-friendly copy. The non-negotiable: a denormalized copy must be kept consistent by the engine, not by hope. Two clean ways:

-- 1) STORED generated column: derive within the same row, always correct, no trigger
ALTER TABLE order_items
  ADD COLUMN line_total bigint GENERATED ALWAYS AS (qty * unit_price_cents) STORED;

-- 2) trigger: maintain a cross-row aggregate (e.g. orders.item_count) on item change
CREATE FUNCTION bump_item_count() RETURNS trigger AS $$
BEGIN
  UPDATE orders SET item_count = item_count + 1 WHERE id = NEW.order_id;
  RETURN NEW;
END $$ LANGUAGE plpgsql;

A STORED generated column handles same-row derivations for free (no trigger, can’t drift). Cross-row aggregates need a trigger or a periodically-refreshed materialized view — and that’s a real maintenance cost you take on knowingly. The cardinal sin is the Hook’s mistake: copying a fact and then trusting the application to update every copy. Application-maintained denormalization drifts; engine-maintained denormalization doesn’t.