My LibraryBuy PDF
Part 5/Data and Databases

Data Modelling and Normalisation

Pull up any production database that’s a few years old and you’ll find a column that lies. Maybe it’s a city copied onto every order row, so when a customer moves house, half their old orders still show the old address and a report cheerfully claims one person lives in two places. Maybe it’s a plan column holding free text, and after three years there are fourteen spellings of “premium” in it because nothing ever forced it to be one of a fixed set.

The schema looked fine in week one. That’s the trap. The tables you sketch on day one are the ones you fight for the next three years, because data outlives code. You can rewrite a function this afternoon. Migrating ten million rows that already encode a bad assumption is a project with a rollback plan.

Data modelling is the practice of deciding what your tables are, what lives in each one, and how they connect, so that the database physically cannot hold a contradiction. Get it roughly right and features slot in cleanly. Get it wrong and every query grows a special case. The spine of the whole thing is one rule, and it’s almost embarrassingly simple: store each fact in exactly one place. Nearly everything below is a consequence of taking that seriously.

This builds on SQL and Postgres for JavaScript developers, which covers the query side. Here we’re designing the tables those queries run against. Examples use Postgres 18, but the ideas are database-agnostic.

Entities, attributes, relationships

Three words carry the whole vocabulary.

An entity is a kind of thing you keep records about: a user, an order, a product. Each entity becomes a table, and each row is one instance of it. An attribute is a fact about an entity: a user’s email, an order’s total, a product’s price. Attributes become columns. A relationship is how two entities connect: a user places orders, an order contains products.

That last one is where all the interesting decisions live. Attributes are easy. Relationships are where a schema is won or lost, and there are only three shapes to learn.

Here’s a small commerce app, which we’ll use for the rest of the article:

usersidPKemailcitycreated_atordersidPKuser_idFKstatustotal_centscreated_atproductsidPKnameprice_centsskuorder_itemsorder_idFKproduct_idFKquantityunit_cents1N1N1Norders and products are many-to-many, resolved through the order_items join table
A small commerce schema. Users own orders, products appear on orders, and the order_items table sits between orders and products to resolve their many-to-many link.

The three shapes of a relationship

Every connection between two tables is one of these. Learn them cold and half of modelling is done.

ONE-TO-MANYusers1NordersFK orders.user_id points back at users.idMANY-TO-MANYordersorder_itemsorder_id + product_idproductsONE-TO-ONE (usually a smell)users11user_profiles
One-to-many hangs a foreign key on the child. Many-to-many needs a join table in the middle. One-to-one puts a key on both sides and usually means the two tables should be one.

One-to-many: a foreign key on the child

One user has many orders. One order belongs to one user. This is the workhorse, and it’s the easiest to get right: put a foreign key column on the “many” side that stores the id of the “one” it belongs to.

CREATE TABLE orders (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users(id),
  status  text   NOT NULL DEFAULT 'pending'
);

The REFERENCES users(id) clause is doing real work. It tells the database that user_id must match an existing user, and the database enforces it on every insert and update. Try to create an order for a user that doesn’t exist and the insert is rejected. Try to delete a user who still has orders and, by default, that’s rejected too. This is referential integrity, and it’s free once you declare it.

You get to decide what happens to the children when a parent is deleted:

user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE
-- or ON DELETE RESTRICT (the default), or ON DELETE SET NULL

CASCADE deletes the orders along with the user. RESTRICT blocks the delete until the orders are gone. SET NULL orphans them. Pick deliberately. A stray ON DELETE CASCADE on the wrong foreign key is how someone deletes one test account and takes a thousand real orders with it.

Many-to-many: a table in the middle

An order contains many products. A product appears on many orders. Neither side can hold a single foreign key, because “many on both ends” doesn’t fit in one column. The answer is a third table, a join table (also called a junction or bridge table), whose whole job is to record which pairs go together.

CREATE TABLE order_items (
  order_id   bigint  NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id bigint  NOT NULL REFERENCES products(id),
  quantity   integer NOT NULL CHECK (quantity > 0),
  unit_cents integer NOT NULL CHECK (unit_cents >= 0),
  PRIMARY KEY (order_id, product_id)
);

Each row links one order to one product. The composite PRIMARY KEY (order_id, product_id) says a given product appears at most once per order, which is exactly what you want. And notice quantity and unit_cents live on the join. That’s the tell that a join table earns its keep: the relationship itself has facts. How many of this product, at what price on the day it was bought. Those belong to the pairing, not to the order and not to the product.

One-to-one: usually a smell

One user has one profile. One profile belongs to one user. You can model this as two tables with a shared key, and the diagram above shows it dashed for a reason: most of the time it’s a sign you split something that wanted to stay together.

If user_profiles holds bio, avatar_url, and timezone, ask the obvious question. Why aren’t those just columns on users? A 1:1 split adds a join to every read for no benefit. There are honest reasons to do it, and they’re specific:

  • Optional, rarely-read bulk. A giant settings jsonb or a kyc_documents blob that 95% of queries never touch. Splitting it keeps the hot users row small so more of them fit in a page.
  • Different access rules. Payment details or government IDs that you want in a separate table with tighter permissions, so a broad SELECT * FROM users can’t leak them.
  • Different write cadence. A user_stats row hammered by counters you don’t want contending with the main record on every update.

If none of those apply, collapse the two tables into one. A 1:1 you can’t justify is just columns you made yourself join to.

Keys: how a row is found

Every table needs a primary key: a column (or set of columns) that uniquely identifies a row and never repeats. Foreign keys point at it, indexes are built on it, your whole app refers to rows by it. Two questions decide it.

Natural or surrogate?

A natural key is a real-world attribute that’s already unique: an email, an ISBN, a country code. A surrogate key is a synthetic value with no meaning outside the database, like an auto-incrementing id.

Natural keys are tempting because they feel honest, but they bite. People change their email. A country gets renamed. A “unique” SKU turns out to be reused across two suppliers. When a natural key changes, that change has to ripple through every foreign key that copied it, everywhere. Surrogate keys never change because they mean nothing, so nothing about the real world can invalidate them.

The pattern that wins almost always: a surrogate primary key, plus a UNIQUE constraint on the natural key. You get a stable identifier for relationships and the database still enforces that no two users share an email.

CREATE TABLE users (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- surrogate
  email text NOT NULL UNIQUE                              -- natural, still enforced
);

Sequential, uuid, or something in between?

For the surrogate itself you’ve got choices, and they trade off differently than most people assume. This is one of the few schema decisions that’s genuinely hard to change later, so it’s worth understanding.

bigint GENERATED ALWAYS AS IDENTITY is the modern sequential integer. Use it over the older serial / bigserial: identity columns are the SQL standard, and GENERATED ALWAYS blocks the app from accidentally supplying an id and colliding with the sequence later. Use bigint, not int. A 4-byte serial caps at about 2.1 billion, and running out of ids in production is a genuinely bad day. The extra four bytes per row is nothing next to that.

Sequential ids are compact, fast, and they sort by creation order. Their downside is that they’re guessable and they leak volume. If your API exposes /orders/1042, a competitor knows you’ve had roughly 1042 orders, and anyone can walk /orders/1043.

uuid fixes the guessing. A random version-4 UUID like 9f1c... tells an attacker nothing. But gen_random_uuid() (the v4 generator) has a real cost that doesn’t show up until your table is large, and it’s about how indexes are built.

Sequential key (bigint identity, uuid v7, ULID)page 1page 2page 3page 4page 5every insert appends to the tail; one hot page serves nearly every write from cacheRandom key (uuid v4)page 1page 2page 3page 4page 5each insert hits a random page; the working set blows out of cache and pages split
A sequential id appends to the tail of the index, so one page stays hot in cache. A random v4 uuid lands on a different page every time, forcing cache misses and page splits.

Postgres stores an index as a B-tree of fixed-size pages. Insert rows with ever-increasing keys and every new row lands on the rightmost page, which stays hot in memory. Insert random uuids and each one targets a random page. Once the table is bigger than RAM, most inserts become a read-a-page-then-write-it-back to disk, pages fill unevenly and split, and the index bloats. On a 50-million-row bulk load the difference is dramatic, measured in the tens of minutes rather than a couple.

The fix keeps the un-guessable property while restoring locality: time-ordered ids. Both share the same trick, a millisecond timestamp in the high bits so values sort roughly by creation time.

  • UUID v7 (standardised in RFC 9562, 2024) puts a 48-bit timestamp up front. Postgres 18 ships uuidv7() natively, no extension needed. It also added uuidv4() as a friendlier alias for gen_random_uuid().
  • ULID is the same idea encoded as a 26-character Crockford base-32 string instead of the 36-character hex uuid. There’s no native Postgres type, so you typically generate it in your app and store it as uuid or text.
CREATE TABLE events (
  id  uuid PRIMARY KEY DEFAULT uuidv7(),   -- Postgres 18+, time-ordered
  ...
);

Rough guidance that holds up: reach for bigint identity by default, it’s the simplest thing that works. Move to uuid v7 / ULID when you need ids that are safe to expose publicly, or when you generate ids on the client / across shards before the row hits the database. Avoid uuid v4 as a primary key on large, insert-heavy tables unless you truly need the randomness, and even then know what it costs the index.

Normalisation, without the exam vocabulary

“Normalisation” sounds like a database-theory exam, and it’s usually taught like one. Strip the jargon and it’s the rule from the top of this article: store each fact once, and derive everything else. The reason this matters isn’t tidiness. It’s that duplicated facts drift out of sync, and a database that disagrees with itself is worse than useless.

Here’s the concrete failure, the update anomaly. Say you skipped the users table and stashed the customer’s city directly on every order:

DUPLICATED · city on every order
order
customer
city
#1001
Ada
Mumbai
#1002
Ada
Delhi
#1003
Ada
Delhi
one row updated, two forgotten. Which city does Ada live in?
NORMALISED · city stored once
customer
city
Ada
Mumbai
order
customer_id
#1001
→ Ada
#1002
→ Ada
#1003
→ Ada
change one row; every order agrees
Left: the city is copied onto every order, so moving a customer means finding and changing every row, and missing one leaves the table contradicting itself. Right: the city lives once in customers, and orders point at it.

Ada moves to Mumbai. Your code updates the order it happens to be looking at and moves on. Now one row says Mumbai and two say Delhi, and no query can tell you which is true, because the schema let two answers exist. There are sibling failures with the same root cause: the insertion anomaly (you can’t record a new customer until they’ve placed an order, because the city only exists on order rows) and the deletion anomaly (delete their last order and you forget where they live). All three vanish the moment the city has exactly one home.

Play with it directly. The flat table on the left lets the anomaly happen; normalising splits it so it can’t:

interactiveWatch the update anomaly happen, then design it away

The normal forms, demystified

The “normal forms” are just named checkpoints on the road to one-fact-one-place. You rarely cite them by number at work, but knowing what each one forbids makes the goal concrete.

1NF · one value per cell
No arrays crammed into a column, no phone1 / phone2 / phone3. A cell holds one value; repeating things become their own rows.
2NF · depend on the whole key
Only bites tables with a composite key. If the key is (order_id, product_id), product_name depends on just the product half, so it drifts. Move it to products.
3NF · depend on nothing but the key
On orders, user_email depends on user_id, another non-key column, not on the order. It belongs on users. Store the id; look up the email.
Each normal form removes one way a table can duplicate a fact: repeating groups, dependence on part of a key, then dependence on a non-key column.

There’s a one-line mnemonic that covers all three, and it’s worth memorising because it’s the whole game: every non-key column must depend on the key, the whole key, and nothing but the key. If a column depends on something other than the row’s own identity, it’s living in the wrong table. That’s it. Third normal form is where most healthy schemas sit, and you can get there by instinct once the sentence is in your head.

Denormalisation, on purpose

Now the honest caveat. Perfectly normalised schemas can be slow, because answering a question means joining five tables and recomputing an aggregate every single read. Sometimes the right move is to deliberately duplicate data. The word for that is denormalisation, and the important word is deliberately.

The two most common cases:

  • Counters and rollups. Showing a comment_count on every post by running SELECT count(*) across a huge comments table on each page load is wasteful. Keeping a comment_count integer on posts and bumping it on insert is a denormalised cache of a fact you could compute. You trade a cheap read for the work of keeping the counter honest.
  • Read-heavy report paths. A dashboard that reads the same expensive join thousands of times a minute might justify a precomputed summary table (or a materialized view) refreshed on a schedule.

The cost you accept is drift, the exact enemy from the last section, now invited in on purpose. A counter can get out of step with the rows it counts. A summary table goes stale between refreshes. So denormalise only when you have the read pressure to justify it, keep the derived copy updated in one well-tested place (a trigger, a single service function, a scheduled rebuild), and treat the normalised tables as the source of truth you can always recompute from.

jsonb: the escape hatch and its abuse

Postgres jsonb lets you store a whole JSON document in a column, indexed and queryable. It’s genuinely useful, and it’s the single most abused feature in modern schemas. (For the JSON format itself, see JSON.)

It’s the right tool when the data is genuinely variable and you never filter on its internals: a third-party webhook payload you keep verbatim for audit, a per-record bag of feature flags, sparse attributes that differ wildly per row. Things where inventing a column per key would be absurd.

It becomes a mess when it does a relational column’s job. The classic path: a team dumps entire API responses into a data jsonb column, and six months later a query that filters on a value buried inside it takes twelve seconds, because the database has to open and parse the JSON of every row to check.

status hidden in jsonbid: 42data: {status: paid, note: …}filter on status inside jsonbSeq Scan: parse every rowa typo in the key name justreturns null; nothing complainsstatus as a typed columnid: 42status: paidWHERE status = ‘paid’Index Scan: jump to matchesa CHECK constraint keeps it toknown values; typos are rejected
A value you filter on, hidden inside jsonb, forces the database to parse every row's document. The same value as a typed, indexed column is a direct lookup, and it can be constrained.

You can index inside jsonb. A GIN index makes containment queries fast, and it indexes every key and value in the document:

-- broad: indexes the whole document, good for @> containment queries
CREATE INDEX events_data_gin ON events USING gin (data);

-- narrow: index just the one key you actually filter on
CREATE INDEX events_source ON events ((data->>'source'));

But a full GIN index over a fat document can end up larger than the data itself, and it makes every write more expensive because each insert updates the index for every key. If you find yourself building expression indexes on data->>'this' and data->>'that', that’s the schema telling you those keys wanted to be columns. The rule of thumb: anything you filter on, join on, or constrain should be a real column. Reserve jsonb for the genuinely shapeless remainder.

Constraints are the cheapest bugs you’ll ever prevent

Every constraint you declare is a category of bug the database refuses to let into the table, checked on every write, forever, for free. Skipping them to “keep things flexible” means re-implementing each one in application code, imperfectly, in more places than you’ll remember to.

CREATE TABLE users (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text        NOT NULL UNIQUE,
  age        integer     CHECK (age IS NULL OR age >= 0),
  plan       text        NOT NULL DEFAULT 'free'
                         CHECK (plan IN ('free', 'pro', 'team')),
  created_at timestamptz NOT NULL DEFAULT now()
);

Each one earns its place:

  • NOT NULL says the fact is required. Half of “undefined is not a function” at 3am traces back to a null nobody expected because the column allowed it.
  • UNIQUE enforces no duplicates. This is where your natural key lives, and it’s the difference between “two accounts with the same email” being impossible versus a support ticket.
  • CHECK enforces a rule about the value: non-negative money, an age that isn’t negative, a status from a fixed set. That last CHECK (plan IN (...)) is what stops the fourteen-spellings-of-premium disaster from the intro.
  • Foreign keys (from earlier) enforce that references point at rows that exist.

Cheap to add on day one. Expensive to bolt on later, because by then the table already contains the rows that violate them, and you have to clean the data before the constraint will even apply.

Enums vs lookup tables

For a fixed set of values like plan or status, you’ve got three options, and the choice is about how often the set changes.

A CHECK ... IN (...) constraint (above) is the lightest. Great for a truly stable, tiny set. Changing the allowed values means altering the constraint.

A Postgres native enum type is similar but reusable across columns. Its catch is rigidity: adding a value is a quick ALTER TYPE ... ADD VALUE, but you can’t easily remove or reorder values, and you can’t attach any metadata to them.

A lookup table is a real table of the allowed values, referenced by a foreign key:

CREATE TABLE plans (
  code       text PRIMARY KEY,          -- 'free', 'pro', 'team'
  label      text NOT NULL,             -- 'Free', 'Pro', 'Team'
  price_cents integer NOT NULL,
  sort_order integer NOT NULL
);

CREATE TABLE users (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  plan text NOT NULL REFERENCES plans(code) DEFAULT 'free'
);

It’s the most flexible by a distance. You add, rename, deprecate, or annotate values with plain INSERT/UPDATE, no schema migration, and you can hang extra facts (a price, a display label, a sort order) off each value. The rule I’d give: CHECK or enum for a set that’s genuinely fixed and value-only (a coin flip, a yes/no/maybe), a lookup table the moment the set changes over time or wants any metadata of its own. Most “status” and “type” columns in real apps turn out to want the lookup table.

Soft deletes and timestamps

Two habits worth building in from the start.

Timestamps on everything. A created_at and usually an updated_at, and use timestamptz (timestamp with time zone), never plain timestamp. The plain type silently drops the offset and hands you an ambiguous value that breaks the day your users or servers cross a time zone. timestamptz stores an unambiguous instant.

created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()

Soft deletes, when you can’t truly delete. Instead of a DELETE, you set a deleted_at timestamp and treat a null as “alive”. Handy for undo, audit, and not cascading a mistake into oblivion. But be clear-eyed about the cost, because soft deletes are not free:

ALTER TABLE users ADD COLUMN deleted_at timestamptz;

-- "delete" the row:
UPDATE users SET deleted_at = now() WHERE id = $1;

-- every read now has to remember the filter:
SELECT * FROM users WHERE deleted_at IS NULL;

The catch that surprises people: a plain UNIQUE (email) now blocks a user from ever re-registering an email that a soft-deleted row still holds. The fix is a partial unique index that only applies to living rows:

CREATE UNIQUE INDEX users_email_live
  ON users (email) WHERE deleted_at IS NULL;

And every single query against the table has to remember WHERE deleted_at IS NULL, forever. Forget it in one report and you’re showing deleted data. Soft deletes are a real tool, but they tax every query that touches the table, so add them where you need recoverability, not reflexively everywhere.

Where this leaves you

Modelling well isn’t about knowing exotic tricks. It’s a handful of defaults applied consistently: one fact one place, a surrogate key with the natural key kept unique beside it, the right relationship shape, and constraints declared up front so the database guards its own integrity. Denormalise and reach for jsonb as considered exceptions, not starting points, and write down why each exception exists.

The schema is never truly finished, though. Requirements shift and the tables have to move with them, on live data, without downtime. Doing that safely is its own discipline, covered in database migrations.

Summary

  • Store each fact once. Duplicated facts drift out of sync, and a database that contradicts itself is worse than no data. This single idea is what “normalisation” actually means.
  • Relationships come in three shapes: 1:N (foreign key on the child), M:N (a join table in the middle, which can carry facts of its own like quantity and paid price), and 1:1 (usually a sign the two tables should be one).
  • Prefer a surrogate primary key with a UNIQUE constraint on the natural key. Default to bigint GENERATED ALWAYS AS IDENTITY; use uuid v7 / ULID for publicly exposed or client-generated ids; avoid uuid v4 as a large-table key because random values wreck index locality.
  • The normal forms boil down to one sentence: every non-key column depends on the key, the whole key, and nothing but the key. Third normal form is where healthy schemas sit.
  • Denormalise deliberately for counters and read-heavy paths, accepting drift as the price, and keep the derived copy updated in one place. Consider a cache before you complicate the schema.
  • jsonb is for genuinely shapeless data. Anything you filter, join, or constrain on should be a real column, or it forces full scans and loses type safety.
  • Constraints (NOT NULL, UNIQUE, CHECK, foreign keys) are the cheapest bug prevention there is. Use a lookup table over an enum once the set of values changes or needs metadata.
  • Use timestamptz for time, and treat soft deletes as a deliberate trade: recoverability in exchange for a filter on every query and a partial unique index.