Tables, rows, and relationships
The relational model in plain language, including the join everyone pretends to understand.
A relational database stores data in tables. A table is a spreadsheet with a strict header: every row has the same columns, and each column has a declared type. That strictness is a feature: it is what lets the database refuse bad data.
- A row is one thing: one user, one order, one lesson.
- A column is one fact about that thing.
- A primary key is the column that uniquely identifies a row.
- A foreign key is a column holding another table’s key, and that is the relationship.
A join is what you do when a question spans two tables: "show me orders, but with the customer name attached". The database matches rows using the foreign key. Joins are ordinary and fast when the columns involved are indexed, and catastrophic when they are not, which is the next lesson.
One fact, one place
- One edit updates everything
- No possibility of disagreement
- Reads need a join
Copied everywhere
- Reads are simple and fast
- Updates must find every copy
- Copies will eventually disagree
Real systems do both on purpose. You keep one authoritative copy, and sometimes duplicate it deliberately for speed. That duplication is called denormalisation, and the price is that you now own the job of keeping the copies in sync.
What to remember
- Tables are strict spreadsheets; the strictness is the value.
- Foreign keys are how tables point at each other.
- Duplicating data buys read speed and costs you correctness work.
Terms in this lesson
Field notes
Loaded from a deliberately slow source. The lesson above was already readable while this was still travelling. That is streaming, and it is the same trick a chat interface uses.
Fine at 500 rows, dead at 500,000
A list page loaded posts, then fetched each author separately. Development had eleven posts. The launch had four thousand, and every visitor issued four thousand queries. The database saturated eight minutes after the announcement.
The N+1 problem, at scale
The rename that took the site down
A column was renamed in a single migration during a deploy. For forty seconds the old code was still running and querying a column that no longer existed. Expand-then-contract exists precisely for those forty seconds.
Postmortem, e-commerce team
resolved in 900ms · region iad1
Hide field notes toggles a search param the loader reads. With it off, the slow promise is never created, so nothing streams.