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
key terms
primary key
The unique identity of a row. One per table, generated by the database, never reused.
id bigint primary keyforeign 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.idjunction 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 posthow it works
- 1List the entities
Nouns in your product are tables: users, posts, comments. The verbs between them are relations.
- 2Give each table a key
An id column, generated, never reused. Every relationship will point at it.
- 3Wire the relations
One-to-many: foreign key on the many side. Many-to-many: a junction table holding pairs.
- 4Add constraints early
NOT NULL, UNIQUE, and CHECK rules cost nothing now and are far cheaper than cleaning bad data later.
- 5Start 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
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
denormalized
- copies of data in many places
- reads skip joins, writes must sync
- one stale copy lies quietly
- break it only for measured speed
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