Designing a Table to Live With
The Report Was Right in March and Is Wrong Now
Last timeDuplicating on Purpose
An update destroys the past. Keeping history means storing when each value was true, and the harder half is separating when something happened from when you found out.
An update is the most destructive statement in a database. It is also the most
common, and what it destroys is not visible in the result.
What an update destroys
A customer changes plan from standard to premium. One update, one row, done.
The row now says premium, and nothing anywhere says it ever said standard.
Consider what that costs. How much revenue came from standard plans last
quarter cannot be answered, because every customer who has since upgraded now
looks as though they were premium all along. How long does a customer stay on
the first plan cannot be answered either. And a report run in March and rerun
in June gives different numbers from the same query over the same period, which
is the symptom people notice first and usually blame on the query.
SHAPE 1: overwrite
customers id, plan
Answers one question: what is it now. The past is gone.
SHAPE 2: a history table beside it
customers id, plan
customer_changes customer_id, plan, changed_on
Keeps the past, and answering what was true in March means
walking the changes. Two places that must agree.
SHAPE 3: one row per period
customer_plans customer_id, plan, valid_from, valid_to
The current value is the row whose valid_to is open. Now is
a special case of a general question, and there is one
place for the truth rather than two.
SHAPE 4: both clocks
customer_plans customer_id, plan,
valid_from, valid_to,
recorded_at, superseded_at
Also answers what the system said in March, which is a
different question from what was true in March.Giving each value its span
Shape three is the one to understand properly. Each row carries a value and the
period that value was true for.
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