Designing a Table to Live With
Say Out Loud What One Row Is Claiming
A table is a sentence with holes in it and a row fills the holes. Write that sentence down and most arguments about table boundaries answer themselves.
Most schemas are the shape the data arrived in, lightly tidied. The repair is
one habit, and it costs a minute per table: before you write any columns, write
the sentence that each row of the table is claiming is true.
A row is a claim
A table is not a container of objects. It is a sentence with blanks in it, and
a row is an assertion that the sentence is true when the blanks are filled with
those particular values.
| step | table | the sentence, with blanks | one row per | how many rows | what happened |
|---|---|---|---|---|---|
| 1 | customers | customer _ is named _ and signed up on _ | customer | 1200 | The subject is a customer and everything else in the sentence is a property of that customer. Nothing about orders can be stated here without changing the subject. |
| 2 | orders | customer _ placed order _ on _ for _ | order | 94000 | The subject has changed. This sentence mentions a customer but it is not about the customer, it is about the order, which is why the customer name does not belong in it. |
| 3 | order_lines | order _ contains _ of product _ at price | order and product | 310000 | Two blanks are needed to pin the claim down, which is the first sign that the key has two parts. The grain is finer than an order. |
| 4 | prices | product _ cost _ from _ until _ | product and period | 8400 | A sentence with a period in it. The same product appears many times and none of the rows contradict each other, because each claims something about a different stretch of time. |
| 5 | stock_counts | on _ we counted _ of product _ at wareho | day, product and warehouse | 2100000 | Three blanks in the key. Writing the sentence is how you find that out before building the table rather than after the duplicates appear. |
Two things to notice about the sentences in that figure. They use the words of
the business, not of the system. And none of them contains the word data, or
record, or entity, which are words that appear when somebody is describing a
table rather than the fact it holds.
Writing it down
The sentence has a shape worth copying. One subject, a verb, and the blanks
that make the claim specific.
Customer _ placed order _ on date _ for amount _.
The test of a good sentence is whether somebody who does not work on the system
would agree it is either true or false for a given set of values. If they would
have to ask what the column means, the sentence is not finished.
Two common failures are worth naming because they appear in nearly every
schema. The first is a sentence that needs a technical term: user _ has a row
in the sessions table with status _. That is a sentence about the system and it
will not survive the system being rewritten. The second is a sentence with no
verb: customer _ , name _ , region _ , last login _. A list of columns is not a
claim, and the absence of a verb is the sign that nobody has decided what the
table is for.
The test for one table
Here is where the sentence earns its minute. Read it aloud. If it contains an
and that joins claims about two different subjects, there are two tables.
BEFORE, one table
orders(order_id, placed_on, amount,
customer_id, customer_name, customer_region)
sentence: customer _ named _ in region _ placed order _
on _ for _
^^^ two subjects joined by an and
what goes wrong
- a customer who moves region has to be updated on every
order they ever placed, and a missed row is now a
disagreement with no tiebreaker
- a customer with no orders yet cannot be stored at all
- deleting the last order of a customer deletes the
customer
AFTER, two tables
customers(customer_id, name, region)
sentence: customer _ is named _ and lives in region _
orders(order_id, customer_id, placed_on, amount)
sentence: customer _ placed order _ on _ for _
and the original question is still one join
select o.order_id, c.name
from orders o join customers c using (customer_id)What the sentence settles
Three decisions that teams usually argue about separately are all consequences
of the sentence, which is the practical reason to write it.
The grain is settled. One row per true instance of the claim, which is why the
third row of the first figure is one row per order and product rather than one
per order. Arguments about whether to store a total on the order or on the line
are arguments about which sentence is being asserted, and they end as soon as
somebody says both sentences out loud.
The key is settled. The smallest set of blanks that makes the claim unique is
the key, and if two different sets of blanks both do it, you have found the
alternate keys the third lesson is about.
| the sentence mentions it | it is a fact about an or | it belongs somewhere els | |
|---|---|---|---|
| placed_on | 1 | 1 | 0 |
| amount | 1 | 1 | 0 |
| customer_name | 0 | 1 | 0 |
| line_item_price | 0 | 0 | 1 |
| shipped_on | 1 | 0 | 0 |
| customer_lifetime_value | 0 | 1 | 0 |
And the empty cells are settled, which is the single most useful consequence. A
blank the sentence requires cannot be empty, because a row with it missing is
not making the claim at all. A column the sentence does not require is
optional, and now you know why it is optional, which is exactly the information
the fifth lesson needs when it asks what an empty cell means.
The next lesson takes the merged table from the figure above and shows what the
duplication does over time, which is where normal form comes from.
Recap
- A table asserts one sentence with blanks in it, and every row is a claim that the sentence is true with those blanks filled in.
- If the sentence needs an and joining two claims about different subjects, there are two tables and somebody has merged them.
- The sentence fixes the grain, the key and which columns can be empty, so writing it first settles three decisions that are otherwise argued about separately.
This 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