CSE Roadmap

Specialist tier · about 3 hours · 5 parts

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.