Schema Design

Design the spreadsheet before the receipts pile up.

▶ the lesson · narrated sketch video · tap for sound

the intro

Schema design decides what tables exist, what columns they have, and how they relate. Good schemas make features easy; bad ones make every query a fight. The fundamentals: entities become tables, relations become keys, and duplication gets a very good reason.

fun fact ✦

Every many-to-many relationship in history has been solved by the same move: a junction table.

the docs

the deep dive

the ideas behind the video — concepts, diagrams, a walkthrough and the traps to avoid

Changing code is cheap; changing a schema with ten million rows in production is surgery. So the design conversation happens first: which nouns become tables, which verbs become relations, and which rules the database itself should enforce. Every feature you build later walks through the doors you make here.

the schema is the contract

shop schemausersordersorder_items
entities become tables; key columns connect them

key terms

primary key

The unique identity of a row. One per table, generated by the database, never reused.

id bigint primary key

foreign key

A column that points at another table’s primary key. The database rejects pointers to rows that do not exist.

orders.user_id points at users.id

junction table

A small table of id pairs that turns a many-to-many into two one-to-many relations.

enrollments(student_id, course_id)

third normal form

The workhorse rule: every column depends on the key, the whole key, and nothing but the key.

author name stored once, not per post

how it works

  1. 1
    List the entities

    Nouns in your product are tables: users, posts, comments. The verbs between them are relations.

  2. 2
    Give each table a key

    An id column, generated, never reused. Every relationship will point at it.

  3. 3
    Wire the relations

    One-to-many: foreign key on the many side. Many-to-many: a junction table holding pairs.

  4. 4
    Add constraints early

    NOT NULL, UNIQUE, and CHECK rules cost nothing now and are far cheaper than cleaning bad data later.

  5. 5
    Start normalized

    Keep every fact in one place. Duplicate a column only when a measured, slow query demands it.

the code, explained

a table that states its own rules

1CREATE TABLE orders (
2 id bigint GENERATED ALWAYS AS IDENTITY,
the database assigns ids, not you
3 user_id bigint NOT NULL,
4 total_cents int NOT NULL DEFAULT 0,
money as integers: floats lie
5 created_at timestamptz DEFAULT now(),
6 PRIMARY KEY (id),
7 FOREIGN KEY (user_id) REFERENCES users(id)
an order must point at a real buyer
8);

side by side

normalized

  • each fact lives in one place
  • updates touch a single row
  • joins reassemble the picture
  • the default you earn your way out of
VS

denormalized

  • copies of data in many places
  • reads skip joins, writes must sync
  • one stale copy lies quietly
  • break it only for measured speed
1one big sheet2split tables3keys rejoin
normalization: split the sheet, rejoin with keys
💡 tip

Put id, created_at, and updated_at on every table from day one. Retrofitting timestamps onto a table with ten million rows is a migration nobody enjoys. Boring columns now save debugging later.

quick answers

UUID or auto-increment ids?

Both work. UUIDv4 sorts randomly, which is index-unfriendly; UUIDv7 embeds a timestamp and stays sortable. Auto-increment bigints remain the simplest default.

ORM or raw SQL?

Learn the SQL first. ORMs save boilerplate, but every real schema decision happens in your head, not in the ORM.

When is denormalizing okay?

When a measured read justifies it: cached counters, materialized views. Write the sync logic and monitor it, or the copies will drift.

quick hits about Schema Design

short answers · interviews, tips, trivia & hidden gems

🎤 interview

PRIMARY KEY vs UNIQUE vs INDEX?watch ↗

🎤 interview

What is normalization?watch ↗

📚 tutorial

The three relationship shapeswatch ↗

💎 deep

How many-to-many actually workswatch ↗

🔧 useful

Name tables like a prowatch ↗

🆕 update

UUIDv7 for primary keyswatch ↗