Recording a change without losing the present

How can a table show both what a customer's details are now and what they were when they ordered?

A customer moves from the Northern region to the Southern one. The obvious thing to do is update the row: set region = 'South', done. The table now tells the truth.

But last year's report, which said that the North sold four million, will now say something different when you re-run it — because every order that customer ever placed has quietly relocated. Nothing was deleted. No fact about any order changed. Yet the past moved. How can a table be truthful about the present without rewriting the past, when it has only one row per customer?

Look closely at what the update destroyed. It did not destroy the region — the region is still there. It destroyed the pairing of a region with a stretch of time. region = 'North' was never simply true; it was true from 2019 until March. Storing it as a bare attribute throws away the interval, and once the interval is gone, there is no way to ask what was true in June.

So if the interval is the missing information, the fix must be to store it. And notice what that immediately implies: a single row cannot hold two intervals. If the customer was North and then South, that is two facts, and two facts need two rows.

Follow that through. Instead of updating, we close the existing row — stamping it with the date its truth ended — and insert a new one that begins where the old one stopped. The customer now occupies two rows: one describing them as they were, one as they are, each carrying the window in which it applied.

Two things follow that are worth pausing on. First, the customer's business identifier is now duplicated across rows, so it can no longer identify a row. Something else must — which is exactly the job a meaningless minted key does, one per version. Second, "which row is the current one?" has become a real question. It is answerable from the intervals alone — the row whose end date is still open — but because that question is asked constantly, tables usually carry an explicit flag as well, accepting the redundancy for the sake of a simple filter.

Now the payoff. An order recorded in February does not merely reference the customer; it references the version of the customer that was current in February. The order carries that version's key. Re-run last year's report in any future year and the North still sold four million, because the order is bolted to the row that described the customer at the time. Meanwhile a report on customers as they are today joins on the current row and sees the South. Both questions are answerable, from the same table, without either answer disturbing the other.

Is that always what you want, though? Consider a different change: someone had misspelled the customer's name, and it has been corrected. Should there be a version of the customer where the name was wrong? Almost certainly not — that is not a change in the world, it is a repair of the record, and preserving it would only invite the question of which spelling last year's report should use. So a correction is overwritten in place, everywhere, while a genuine change of state is versioned. The difficult part in practice is that the table cannot tell the two apart. Deciding which columns represent facts that changed and which represent facts that were wrong is a modelling judgement, made per column, in advance — and getting it wrong is discovered years later when a report cannot be reproduced.

There is a third arrangement, less used and worth knowing: keeping a previous value column beside the current one. It answers "what was it before?" without creating rows, which keeps the table small and the joins simple, but it remembers exactly one step back and knows nothing of when the change happened. It suits a case where one recent prior value is genuinely all anyone asks for.

A caution on cost. Versioning multiplies rows, and a column that changes often can produce so many versions that the description outgrows the facts it describes. The usual remedy is to move such columns somewhere designed for high-frequency change, not to give up versioning the rest.

Three refinements.

First, the misconception that this is an audit log. An audit log records that a change was made, by whom and when, and is read by humans investigating. Versioned rows exist so that ordinary queries land on the right version automatically. They serve analysis, not accountability, and a system may well want both.

Second, this technique does not, by itself, get facts pointed at the right version. It only makes the right version exist. Something must decide, at the moment an order is recorded, which version key to attach — and for data arriving late, that decision is not the current row but the row that was current when the event occurred.

Third, the interval and the flag must agree. Two independent representations of the same truth is a duplication that drifts: a load that inserts a new version but forgets to close the old one produces two overlapping current rows, and every downstream join silently doubles. It is worth constraining or checking rather than trusting.

• How a fact arriving late is matched to the version that was current when it happened.
• Which columns deserve versioning, and where fast-changing attributes should live instead.
• What breaks when two versions of one entity are both flagged current.

Every term the collection defines is gathered in the glossary.