Part 3 of 5 · 1 min read
Keeping history instead of overwriting
A history table preserves every past value, instead of losing it on update.
Think of a ledger that never erases an old entry, only ever adds a new line beneath it, so the full sequence of past values stays visible forever.
In plain words: a plain table overwrites a column's old value on UPDATE, losing it forever. A history table instead inserts a new row for every change, each with a validity period, keeping every past value queryable.
A worked example. Querying what a book's price was on a specific past date, using the history table from part 1.
The key lines.
SELECT price FROM book_price_history
WHERE book_id = 42 AND valid_from <= '2026-03-10'
ORDER BY valid_from DESC
LIMIT 1;
History table
A history table records every past value of a changing fact, each tagged with the period it was valid, rather than overwriting old values on update.
Example: book_price_history lets a query ask "what was this book's price on March 10th," which a plain, overwritten price column could never answer.