rows examined
:
The relational model (Codd, 1970) makes one radical bet: store facts, not objects. Every fact lives in exactly one place, as a row in a table; anything composite: an order with its customer and its items: is assembled at query time by joining facts back together. The payoff is that the database can guarantee things about your data that no application code has to enforce. To feel why that matters, first build the disease the model was designed to cure:
Left: one denormalized table, customer data copied into every order (how a spreadsheet or a naive design does it). Right: normalized: orders reference the customer by id. Mia changes her email. Press the button on each design.
| order | item | customer | |
|---|---|---|---|
| #101 | keyboard | Mia | mia@old.fi |
| #102 | monitor | Mia | mia@old.fi |
| #103 | usb hub | Mia | mia@old.fi |
| #104 | cable | Rui | rui@post.pt |
| customers.id | name | |
|---|---|---|
| c1 | Mia | mia@old.fi |
| c2 | Rui | rui@post.pt |
| orders.id | item | customer_id |
|---|---|---|
| #101 | keyboard | c1 |
| #102 | monitor | c1 |
| #103 | usb hub | c1 |
Design A's failure has a name: an update anomaly: and two siblings: the insertion anomaly (can't record a new customer until they order something; there's no row to put them in) and the deletion anomaly (delete Mia's last order and Mia's email vanishes from the universe). Normalization is the discipline of decomposing tables until every fact is stored once, so anomalies become structurally impossible rather than carefully avoided. The famous mnemonic for third normal form: every non-key column depends on "the key, the whole key, and nothing but the key."
Normalization scatters the facts; JOIN gathers them. The query below doesn't care that the data lives in two tables: the relational engine matches rows on the fly:
This is the model's superpower and its tax. Superpower: you can ask questions you didn't anticipate when designing the schema: any table joins to any table, and tomorrow's report needs no migration. Tax: joins cost CPU and, at scale, become the thing everyone tunes. (And when the data is spread across shards: Lesson 3 of the distributed systems course: cross-shard joins get so expensive that the models in the rest of this course start to look attractive. That's not a coincidence; it's the plot.)
The users table has 1,000,000 rows. Find email = 'x9042@cyberdesk.io': first without an index, then through a B-tree.
The B-tree is a sorted, shallow tree of pages: root → branch → leaf, each hop narrowing the range, reaching any of a million keys in three or four page reads. Every relational database is, mechanically, two things: a B-tree library and a query planner deciding which B-trees to use. The catch you just didn't see: every index is a second copy of part of the table that must be updated on every write. Ten indexes = every INSERT does eleven writes. Read-heavy tables want many indexes; write-heavy tables want few: a dial you'll see the wide-column lesson turn all the way in one direction.
The relational world's crown jewel is the transaction: Atomic (all or nothing: the WAL machinery from the replication course's mechanics lesson), Consistent (constraints: foreign keys, uniqueness, checks: hold before and after), Isolated (concurrent transactions can't see each other's half-done work), Durable (committed means fsynced). The practical consequence: entire categories of bugs live in the database instead of in your code. No application-level "check then insert" races for uniqueness; no half-transferred money; no orphaned order items: the schema itself is an always-on test suite. This is why the boring default answer to "which database?" has been PostgreSQL for a decade: you give up ACID and joins only when a specific access pattern forces you to, which is what the next five lessons are about.