Keys and relationships

A database is grids pointing at other grids, and the pointing is done with keys.

Each row needs one column whose value names it uniquely, and that column is the primary keyConcept · lights on your mapprimary keyA column whose value uniquely identifies each row in its table. Two rows can never share one. Names and emails make poor primary keys. People change them, and two people can share them.. For runners, it is the bib number. Not the name: Harborview has four people called the same thing this year. Not the email: Case might finally retire the embarrassing one mid-season. 11208 identifies Case the way nothing else can, one row, forever, no opinions, and the number will go on meaning Case long after the embarrassing email is retired and the name has three more namesakes. That is the job.

When another table needs to say whose, it stores that key. The results table will not copy Case’s name and birthday — it will carry a foreign keyConcept · lights on your mapforeign keyA column that holds another table’s key: the result row storing runner 11208 means this result belongs to that runner. A pointer to the record, never another copy of it., runner: 11208, a pointer instead of a copy. One fact, stored once, pointed at from everywhere. Copies drift, as 6:04 taught you; pointers cannot.

The pointing has a name: relationshipConcept · lights on your maprelationshipThe connection a foreign key creates between records: one runner has many results, each result belongs to one runner. Modelling these is why the relational database is called relational.. One runner, many splits. One race, many runners. The whole relational idea is that the shape of the business, who belongs to what, lives in the data itself.

Changing the constitution

In week six, the race director asks for something small: collect an emergency contact for every runner. One new column. It takes the team most of a sprint, and the reason is that changing a schema is a migrationConcept · lights on your mapmigrationA controlled change to a database’s structure or contents: adding a column, changing a type, filling a new field for existing rows. Written as scripts, reviewed and rehearsed. It alters the rules governing the company’s memory., and a migration is surgery on the company’s memory while the patient stays awake. Twenty-five thousand existing rows need the new column filled or allowed empty. Every piece of code reading the table must survive the new shape. The scripts get written, reviewed like code, rehearsed on a copy.

It goes fine. It goes fine the way sixteen weeks of training go fine, which is to say: on purpose. Next, the language you ask the cabinet questions in.