Modelingentitiesaccess patternser diagramschema designrequirements

From Requirements to Tables

A schema is derived from access patterns, not from nouns: list the questions the system must answer and how often, then design the tables and indexes that answer the frequent ones cheaply.

▶ Interactive Interview question

Progress

Start with the queries

The tempting way to design a schema is to underline the nouns in the requirements — user, order, product — and make each a table. That produces a correct schema and often a slow one, because it never asked what the system *does* with the data. The disciplined way starts one step earlier: what questions will be asked, how often, and how fast must they be answered?

For an online shop: "log in by email" (constant, must be instant), "show my recent orders" (constant), "what is in this order" (constant), "revenue this month" (hourly, may take seconds), "which products sell together" (weekly, may take minutes). The first three decide the primary keys, foreign keys and indexes. The last two decide whether you need a rollup table. The nouns come out the same; the indexes do not.

Write each access pattern as a sentence with a frequency and a latency budget.
Group them: point lookups, lists for one parent, aggregates over time, full-text or similarity search.
Every frequent pattern should map to an index or a precomputed value. Every rare one may scan.

Entities, then relationships

An entity is something with identity that persists independently: a user exists whether or not they have ordered. A relationship connects entities and has a cardinality: one user, many orders (1-N); one order, one shipping address (1-1); many orders, many products (N-M). An attribute is a fact about an entity or a relationship — quantity is a fact about the (order, product) pair, not about either alone.

Draw it. An ER diagram with boxes, arrows and cardinalities finds design errors faster than any amount of DDL. The e-commerce, SaaS, social, banking, analytics and messaging databases in the playground each have one; click any entity to see why it exists and what it should be indexed on.

The shop, as entities and relationships

UserLLMAgentToolDataDecisionHumanGuardrail

Turning the diagram into DDL

Each entity becomes a table with a surrogate primary key. Each 1-N relationship becomes a foreign key column on the "many" side. Each N-M relationship becomes a junction table with two foreign keys and its own primary key (or a composite of the two). Each 1-1 relationship is either folded into one table or split with a UNIQUE foreign key. Every column gets a type, and NOT NULL unless absence is a real state.

Then the part most people skip: for every frequent access pattern, name the index that serves it. "My recent orders" is orders(user_id, created_at DESC). "Log in by email" is the unique index on users(email) you already get from the constraint. "What is in this order" is order_items(order_id). Write those CREATE INDEX statements in the same migration as the tables.

Schema plus the indexes the access patterns demand

1CREATE TABLE users (
2  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
3  email      text NOT NULL UNIQUE,            -- login: unique index, free
4  name       text NOT NULL,
5  created_at timestamptz NOT NULL DEFAULT now()
6);
7CREATE TABLE orders (
8  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
9  user_id    bigint NOT NULL REFERENCES users(id),
10  status     text NOT NULL,
11  total      numeric(12,2) NOT NULL,
12  created_at timestamptz NOT NULL DEFAULT now()
13);
14CREATE INDEX orders_user_recent ON orders (user_id, created_at DESC);  -- "my recent orders"
15 
16CREATE TABLE order_items (
17  order_id   bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
18  product_id bigint NOT NULL REFERENCES products(id),
19  quantity   int NOT NULL CHECK (quantity > 0),
20  unit_price numeric(12,2) NOT NULL,          -- price at purchase: history, not redundancy
21  PRIMARY KEY (order_id, product_id)          -- also the index for "what is in this order"
22);
23CREATE INDEX order_items_product ON order_items (product_id);  -- "which orders had product X"

Key points

Design from access patterns with frequencies and latency budgets, not from nouns.
Entities have identity; relationships have cardinality; attributes belong to one or the other — including to the relationship itself.
Draw the ER diagram before writing DDL.
Ship the indexes for the frequent patterns in the same migration as the tables.

ER diagram explorer

ER diagram

Boxes are tables, arrows are foreign keys pointing from parent to child. Click a table to see why it exists, which keys it carries, and what to index.

users

One row per customer account. The identity table every other table points at.

Why it exists: Separated from `orders` so a customer exists before and between purchases, and so changing an email does not rewrite order history.

Keys and constraints

id PRIMARY KEY
email UNIQUE

Relationships

users 1-N orders
One customer places many orders; an order belongs to exactly one customer.
users 1-N reviews
The review’s author.

Indexes present

UNIQUE users_pkey (id)
UNIQUE users_email_key (email)

Worth considering

If every query filters on is_staff, a partial index WHERE is_staff = false is smaller than a full one and never stores rows you always discard.

Column notes

id Surrogate primary key. Meaningless on purpose: it never has to change when business facts change.
email Natural key. UNIQUE here is enforced by a unique index — that index is also what makes login lookups fast.
country Low cardinality (18 values). An index on country alone rarely pays off; as the leading column of a composite index it does.
last_login_at NULL means "never logged in" — a real fact, not missing data.
is_staff 3% true. A partial index WHERE is_staff is tiny and answers staff-only queries.

7 tables · 8 foreign keys

Data Modeling Lab

0 entities · 0 rels · 0 idx

What are the core entities?

Add them one at a time. For each candidate ask: is it an entity (has identity and a lifecycle), an attribute (describes an entity), or an event (something that happened)?

try:

The full lab — case studies, challenge mode, scale and security views — lives at /db/modeling →

When to use — and when not

Use it when

Any new system or new bounded context.
Before a rewrite — the access patterns have usually changed since the original design.

Avoid it when

A throwaway prototype; but write the access patterns down anyway, they are the requirements.

Failure modes

Tables designed from nouns with no indexes because no one asked what would be queried.
A relationship attribute stored on the wrong entity.
Foreign keys without indexes on the referencing side.

Relationships, Keys and Constraints →