Designing a Table to Live With
Seventy-Eight Copies and No Tiebreaker
Last timeWhat One Row Means
Three failures follow from storing one fact twice, and all three are derivable rather than memorable. Normal form is the name for the repair, not a rule to obey.
The previous lesson split a table because its sentence had two subjects in it.
This lesson is the argument for why that mattered, and the argument is three
specific failures that can be derived from the duplication rather than taken on
authority.
Correcting it in every place
Start with the merged table. Every order row carries the region of the customer
who placed it, so a customer with three hundred orders has three hundred copies
of their region.
| step | step | what happened | rows carrying the old region | state of the table | what happened |
|---|---|---|---|---|---|
| 1 | 1 | customer 4471 moves from north to west | 312 | consistent and now wrong | The world has changed and the table has not. Every one of the 312 rows says north, which is at least an agreed answer even though it is the wrong one. |
| 2 | 2 | an update runs over the order rows | 0 | consistent and right | If it completes, all is well. The update touched 312 rows to record one fact about one customer, which is the cost of the duplication but not yet the danger. |
| 3 | 3 | a second update runs with a date filter | 46 | disagreeing with itself | This is the real failure. The table now says north on 46 rows and west on 266, and no column anywhere records which of those is the current truth. |
| 4 | 4 | a report groups orders by region | 46 | wrong, with no error raised | The report runs, produces plausible numbers, and attributes 46 orders to the wrong region. Nothing in the system had an opinion about this. |
| 5 | 5 | somebody tries to find the correct value | 46 | unanswerable from the data | And there is no way to tell from the table which answer is right. The only recoverable truth is outside the database, which is where this stopped being a storage problem. |
- the chance that any single row is missed or mis-updated
- the number of rows carrying a copy of the fact
- the chance that at least one copy ends up disagreeing with the others
The row that cannot exist
The second and third failures are quieter and they come from the same defect
seen from a different angle: a fact that can only be stored as a passenger on
another fact.
A customer who has not ordered anything yet has nowhere to live. There is no
customer row, only order rows, and the customer has no order. The usual
workaround is to insert an order row with the order fields left empty, which
means every query about orders now has to remember to exclude the ones that are
not orders, and the ones that forget are wrong by a small amount that nobody
notices.
And deleting the last order of a customer deletes the customer. Not as a
cascade anybody chose, but because the only place the customer existed was
inside that row. A tidy-up of orders older than seven years silently removes
every customer who has not ordered since, and the deletion is invisible because
no constraint was violated.
Both are the same thing. The customer fact has no independent existence, so it
appears when an order appears and vanishes when the last one goes.
The lesson stops here
3 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