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
Show field notes toggles a search param the loader reads. With it off, the slow promise is never created, so nothing streams.