Prices, addresses and tiers change. A report on last year must use last year's values, so the history has to be kept. The common design, type 2, adds a row per version with the dates it was valid:
| product_id |
price |
valid_from |
valid_to |
| 1 |
13.50 |
2024-10-01 |
2024-12-31 |
| 1 |
14.00 |
2025-01-01 |
(NULL) |
The current version has an empty valid_to. To get the value on a date, join with a range: valid_from <= d AND (valid_to IS NULL OR d <= valid_to). Recording a change takes two statements in one transaction: close the current row (set its valid_to) and insert the new one.
Type 1 simply overwrites the old value and keeps no history; use it for corrections, such as a misspelled name. Ranges must not overlap: two versions valid on the same day make an as-of join return two rows, which is the first thing to check in any history table.