Designing a Table to Live With
The Email Address Was Reused by the New Employee
Last timeThe Same Fact in Two Places
A key is the smallest set of blanks that makes the claim unique. Choosing it badly is the one schema mistake that spreads into every other table you will ever write.
Every repair in the previous lesson ended with the phrase a table whose key it
points at. This lesson is about choosing that key, which is a decision made in
an afternoon and lived with for a decade.
The smallest thing that identifies
Go back to the sentence. The key is the smallest set of blanks that makes the
claim unambiguous, and smallest is doing real work in that definition.
Take: on date _ we counted _ of product _ at warehouse _. The date, the product
and the warehouse together pin it down, and none of the three can be dropped.
That is a key with three parts. Adding the counted quantity to it would still
be unique, and it would be worse, because a key is a constraint and a wider key
constrains less: it would permit two different counts for the same product on
the same day at the same warehouse, which is exactly the thing the key existed
to prevent.
A table can have more than one candidate. A country has a name, a two-letter
code and a three-letter code, and each identifies it. Picking one as the key
does not make the others stop being unique, and they should still be declared
unique so the database enforces what you know.
Meaningful or meaningless
The real question is whether the key should be a fact about the world or a
number your database invented.
The lesson stops here
5 more paragraphs to go
You have read the opening. The rest of the argument, the problems that check whether it landed, and the lines worth keeping at the end all come with a plan.
The first lesson of every course in the library reads the whole way through, free, so you can see exactly what the rest of them are.
See the planThe contentsThis is the reading half
Starting the course gives you your own copy of it. Every idea on every page has problems standing under it, marked with a reason rather than a tick, and any sentence you do not believe can be opened and argued with. None of that can happen on a page nobody owns.
The contents