Relational modeling and normalisation

From entities to tables

A relational database stores data in tables: rows of the same shape, with typed columns. Modelling is deciding which tables exist, what each row means, and how rows refer to each other. For Snip:

CREATE TABLE users (
  id          bigint PRIMARY KEY,
  email       text NOT NULL UNIQUE,
  plan        text NOT NULL DEFAULT 'free',
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE links (
  code        text PRIMARY KEY,                       -- 'x7Kp'
  long_url    text NOT NULL,
  owner_id    bigint NOT NULL REFERENCES users (id),  -- many links per user
  created_at  timestamptz NOT NULL DEFAULT now(),
  disabled    boolean NOT NULL DEFAULT false
);

CREATE TABLE clicks_daily (
  code        text NOT NULL REFERENCES links (code),
  day         date NOT NULL,
  clicks      bigint NOT NULL,
  PRIMARY KEY (code, day)                              -- one row per link per day
);

The vocabulary, using these tables:

• Primary key: the column(s) that identify a row. A link is identified by its code; a daily count by (code, day). The database guarantees primary keys are unique.
• Foreign key: a column that points at another table's primary key. links.owner_id points at users.id. The database refuses a link whose owner doesn't exist.
• Relationship: one user has many links (one-to-many). If links could belong to many teams and teams had many links, that's many-to-many, stored as a third table of (link, team) pairs.
• Constraints: rules the database enforces for you: NOT NULL, UNIQUE, foreign keys, CHECK (clicks >= 0). Every rule enforced here is a bug your application can't write.

Store each fact once

Normalisation is a formal name for a simple habit: each fact lives in exactly one place. Here's a table that breaks it:

Ana's plan is stored once per link, so it can disagree with itself, as it already does. Changing her email means updating thousands of rows, and missing one leaves a contradiction. Splitting owners into their own users table and pointing at them by ID fixes it: one row per fact, and a change happens in one place.

The textbook "normal forms" (1NF, 2NF, 3NF…) formalise this. In practice, the test is: if one real-world fact changes, how many rows must change? If the answer is more than one, ask whether it should be.

You'll sometimes copy a fact on purpose, to make a hot read cheaper. That's denormalisation, and it has a cost you choose deliberately (04.12 Denormalisation).

The table that shouldn't be a table

Notice there's no clicks table with a row per click. From the estimate in 01.05 Back-of-the-envelope estimation, Snip serves about 350 redirects a second on average: around 30 million clicks a day, over 10 billion a year. A row per click in the main database would soon dwarf everything else, and every owner's chart would have to add up millions of rows.

Instead, Snip keeps daily counts in clicks_daily (the stats workers from 02.06 Async workers and queues add to them in batches), and sends the raw click events to cheaper storage built for huge append-only data (04.11 Choosing a database). Modelling isn't just tables and keys; it's deciding what level of detail each store keeps, based on the queries it has to answer.

Model for the queries

Write down the questions the system asks most, and check the model answers each cheaply:

That last row is a hint of what's to come: a query the model can answer, but slowly. Indexes, query plans and denormalisation are the tools for it.

Try it

Recap

Interview questions