Designing a Table to Live With
The Query Returned Nothing and the Query Was Correct
Last timeOne, Many and Many to Many
An empty cell can mean three different things, the database treats all three identically, and the result is a comparison that is neither true nor false and an average over a changing denominator.
Every table built so far has cells that might be empty. This lesson is about
what an empty cell means, which is three things, and what the database does
with it, which is one thing.
Three things wearing one face
A customer table with an empty phone column. Three stories produce that cell,
and they are not the same story.
The customer has a phone number and we never asked for it. The value exists in
the world and not in the database, which is the original meaning: unknown.
The customer is an organisation with no single phone number, so there is no
value to hold. Nothing is missing. The column simply does not apply to this
row.
The form has three pages and the customer is on page two. There will be a
value, shortly, and right now the row is incomplete.
| should be excluded from | belongs in a different t | should be visible as inc | needs a record of why | |
|---|---|---|---|---|
| unknown: there is a valu | 1 | 0 | 1 | 0 |
| not applicable: there is | 0 | 1 | 0 | 0 |
| not collected yet: there | 1 | 0 | 0 | 1 |
| withheld: there is one a | 0 | 0 | 1 | 0 |
The database stores one marker for all four. Nothing in the column records
which story applies, so the meaning has to be supplied elsewhere, and if it is
not supplied anywhere then every reader guesses.
The lesson stops here
4 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