Some values change over time, and you need to know what they were, not only what they are now: a product’s price, an employee’s salary, a customer’s plan. Overwriting the old value loses the past. A history table keeps it.
The model: one row per period
CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(30) NOT NULL);
INSERT INTO products VALUES (1, 'Notebook'), (2, 'Pen');
CREATE TABLE product_prices (
id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
price DECIMAL(8,2) NOT NULL,
valid_from DATE NOT NULL,
valid_to DATE NULL,
FOREIGN KEY (product_id) REFERENCES products (id),
KEY idx_product_period (product_id, valid_from),
CONSTRAINT chk_period CHECK (valid_to IS NULL OR valid_to > valid_from)
);
INSERT INTO product_prices (product_id, price, valid_from, valid_to) VALUES
(1, 70.00, '2025-01-01', '2025-07-01'),
(1, 80.00, '2025-07-01', '2026-01-01'),
(1, 90.00, '2026-01-01', NULL),
(2, 10.00, '2025-01-01', NULL);Query OK, 0 rows affected
Query OK, 2 rows affected
Records: 2 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0Each row says: “from valid_from (including) up to valid_to (excluding), the price was X.” valid_to IS NULL means “still valid”. Using half-open periods (start included, end excluded) means the end of one period is exactly the start of the next, with no gap and no overlap.
The price on a given date (as-of query)
SELECT p.name, pp.price
FROM products p
JOIN product_prices pp ON pp.product_id = p.id
WHERE pp.valid_from <= '2025-09-15' AND (pp.valid_to IS NULL OR pp.valid_to > '2025-09-15')
ORDER BY p.name;+----------+-------+
| name | price |
+----------+-------+
| Notebook | 80.00 |
| Pen | 10.00 |
+----------+-------+
2 rows in setThe same query with another date gives the price at that moment. This is how you re-create an old invoice, or answer “what was the price when the customer ordered?” (If an invoice needs to keep the price forever, the order line also stores a snapshot, see Denormalization.)
The current price
SELECT p.name, pp.price AS current_price
FROM products p JOIN product_prices pp ON pp.product_id = p.id AND pp.valid_to IS NULL
ORDER BY p.name;+----------+---------------+
| name | current_price |
+----------+---------------+
| Notebook | 90.00 |
| Pen | 10.00 |
+----------+---------------+
2 rows in setMake it a view, so that application code reads current_prices and never has to remember the valid_to IS NULL rule (Views).
CREATE VIEW current_prices AS
SELECT product_id, price, valid_from FROM product_prices WHERE valid_to IS NULL;
SELECT * FROM current_prices ORDER BY product_id;+------------+-------+------------+
| product_id | price | valid_from |
+------------+-------+------------+
| 1 | 90.00 | 2026-01-01 |
| 2 | 10.00 | 2025-01-01 |
+------------+-------+------------+
2 rows in setChanging the price: close the old row, open a new one
Both steps must happen together, so use a transaction, and lock the current row first so that two changes cannot interleave (Transactions):
START TRANSACTION;
SELECT id FROM product_prices WHERE product_id = 1 AND valid_to IS NULL FOR UPDATE;
UPDATE product_prices SET valid_to = '2026-03-01' WHERE product_id = 1 AND valid_to IS NULL;
INSERT INTO product_prices (product_id, price, valid_from, valid_to) VALUES (1, 95.00, '2026-03-01', NULL);
COMMIT;
SELECT price, valid_from, valid_to FROM product_prices WHERE product_id = 1 ORDER BY valid_from;+----+
| id |
+----+
| 3 |
+----+
1 row in set
+-------+------------+------------+
| price | valid_from | valid_to |
+-------+------------+------------+
| 70.00 | 2025-01-01 | 2025-07-01 |
| 80.00 | 2025-07-01 | 2026-01-01 |
| 90.00 | 2026-01-01 | 2026-03-01 |
| 95.00 | 2026-03-01 | NULL |
+-------+------------+------------+
4 rows in setThe history is continuous: each valid_to equals the next valid_from.
Finding gaps and overlaps
The database does not stop two overlapping periods for the same product by itself. Check with a query (and run it in tests or a scheduled job):
INSERT INTO product_prices (product_id, price, valid_from, valid_to) VALUES (2, 11.00, '2025-06-01', '2025-08-01'); -- a mistake: overlaps the open period
SELECT a.product_id, a.id AS first_id, b.id AS second_id
FROM product_prices a
JOIN product_prices b ON b.product_id = a.product_id AND b.id > a.id
AND a.valid_from < IFNULL(b.valid_to, '9999-12-31') AND b.valid_from < IFNULL(a.valid_to, '9999-12-31')
ORDER BY a.product_id, a.id;+------------+----------+-----------+
| product_id | first_id | second_id |
+------------+----------+-----------+
| 2 | 4 | 6 |
+------------+----------+-----------+
1 row in setTwo periods overlap when each starts before the other ends. IFNULL(valid_to, '9999-12-31') treats an open period as “forever”. To make the database refuse overlaps outright, use a trigger that runs this check in a BEFORE INSERT, or lock the product row and check in the application (Triggers). MySQL has no built-in exclusion constraint.
Find gaps (a period that does not start where the previous one ended):
DELETE FROM product_prices WHERE product_id = 2 AND price = 11.00;
SELECT product_id, valid_from, LAG(valid_to) OVER (PARTITION BY product_id ORDER BY valid_from) AS previous_end
FROM product_prices
ORDER BY product_id, valid_from;+------------+------------+--------------+
| product_id | valid_from | previous_end |
+------------+------------+--------------+
| 1 | 2025-01-01 | NULL |
| 1 | 2025-07-01 | 2025-07-01 |
| 1 | 2026-01-01 | 2026-01-01 |
| 1 | 2026-03-01 | 2026-03-01 |
| 2 | 2025-01-01 | NULL |
+------------+------------+--------------+
5 rows in setOn each row after the first of a product, previous_end should equal valid_from. A different value is a gap (or an overlap). This uses a window function (LAG).
Other history patterns
| Pattern | How | Use when |
|---|---|---|
| Valid-time table (above) | valid_from / valid_to columns on the table | The business needs to know what was true when (prices, plans, contracts) |
| Audit table with triggers | Copy old values to a log table on every change | You need who and when changed, for compliance (Auditing) |
| Append-only events | Never update; insert a new row per event; derive the state | Ledgers, stock movements, payments |
| Soft delete | A deleted_at column | Undelete, but not a full history (Soft Delete) |
MySQL has no built-in system-versioned (temporal) tables like some other databases. The patterns above are how it is done.
An append-only ledger
For money and stock, the strongest pattern is to never update a balance, only to add movements, and to compute the balance from them:
CREATE TABLE stock_movements (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
change_qty INT NOT NULL,
reason VARCHAR(20) NOT NULL,
moved_at DATETIME NOT NULL
);
INSERT INTO stock_movements (product_id, change_qty, reason, moved_at) VALUES
(1, 100, 'purchase', '2026-02-01 09:00:00'),
(1, -3, 'sale', '2026-02-02 11:15:00'),
(1, -2, 'sale', '2026-02-03 15:40:00'),
(1, 1, 'return', '2026-02-04 10:05:00');
SELECT product_id, SUM(change_qty) AS in_stock FROM stock_movements GROUP BY product_id;
SELECT moved_at, reason, change_qty, SUM(change_qty) OVER (ORDER BY moved_at) AS running_stock FROM stock_movements WHERE product_id = 1 ORDER BY moved_at;+------------+----------+
| product_id | in_stock |
+------------+----------+
| 1 | 96 |
+------------+----------+
1 row in set
+---------------------+----------+------------+---------------+
| moved_at | reason | change_qty | running_stock |
+---------------------+----------+------------+---------------+
| 2026-02-01 09:00:00 | purchase | 100 | 100 |
| 2026-02-02 11:15:00 | sale | -3 | 97 |
| 2026-02-03 15:40:00 | sale | -2 | 95 |
| 2026-02-04 10:05:00 | return | 1 | 96 |
+---------------------+----------+------------+---------------+
4 rows in setEvery number can be explained by the rows that made it. A correction is a new row (a negative one), not an edit.
Half-open periods
BETWEEN-style closed ranges, the same instant belongs to two periods, and every boundary needs a special case.