Linking Tables

Last lesson ended with a promise: stop copying facts, start pointing at them. Here's the whole trick, and it fits in two small grids.

The split

The one big table was hiding two kinds of thing, so we give each its own drawer. Classes get their own table, where each class — its name, its teacher, its room — is written down once, with a primary key like any respectable record:

classes
id | name  | teacher    | room
---+-------+------------+-----
1  | Art   | Mr. Petrov | 30
2  | Music | Ms. Danso  | 12

And the students table slims down to facts that are truly about each student — plus one new column:

students
id | name   | age | class_id
---+--------+-----+---------
1  | Sara   | 14  | 2
2  | Marcus | 15  | 1
3  | Amara  | 14  | 2

The foreign key

That class_id column is the star of this unit. It holds no new fact about the world — it holds another table's primary key. Sara's card says class_id: 2, which reads: "for my class, see card 2 in the classes drawer." On a member's card in the library, it's the line that says "see card #2 in the blue drawer" instead of a hand-copied description.

A column that stores another table's primary key is called a foreign key — a key from a foreign drawer. Primary key: "this is card 2." Foreign key: "go look at card 2." Same number, opposite directions. This is why last lesson insisted IDs never change or get reused: every pointer aimed at card 2 trusts that card 2 stays card 2.

And with that, the tables have a relationship. One class has many students; each student points at one class. Designers call this shape one-to-many, and it is everywhere once you look: one customer, many orders. One author, many books. One playlist, many songs. Each is two drawers and a pointing column.

Watch the anomalies die

Replay last lesson's disaster. Music moves to room 15. Before: hunt down thirty copies, miss one, contradict yourself. Now: the fact lives on exactly one card —

classes
2  | Music | Ms. Danso  | 15

One change. Every student pointing at class 2 is instantly, automatically current, because none of them ever carried a copy — only the pointer. Disagreement between rows isn't merely unlikely anymore; there is nothing left to disagree. And if every Music student graduates, the class calmly remains in its own drawer, no longer a passenger on anyone's card.

One habit protects all this: the pointer must hold the id, never the name. A class column holding the word "Music" is a copy wearing a pointer's clothes — rename the class and every row lies. Point at the id; names may wobble, card numbers don't.

You now hold every piece of database structure: tables, typed columns, primary keys, foreign keys. Next lesson — the unit's finale — we zoom out and read a whole database's floor plan in one picture.