Data Versioning in Database Design

Versioning means keeping a history of how a row changed over time, rather than only ever storing its current state. Many business domains genuinely need this: a contract that was amended three times, a product price that changed across a year, an employee's salary history, or any record where "what was true at this point in the past" is itself a valid and necessary question to answer.

Without a versioning strategy, an UPDATE statement permanently overwrites the previous value, and once that happens the old value is gone unless it happened to be captured somewhere else (an application log, a backup). Designing versioning into the schema up front means the history is a first-class, queryable part of the data model instead of something reconstructed after the fact from incomplete logs.

Why it matters

Prices change, addresses change, and contracts are amended, yet an invoice from last March must still show last March's price. If a row is simply overwritten, the earlier value is gone. Versioning records how data changed over time, which reporting, auditing and dispute resolution all depend on.

When Versioning Is Actually Needed

Not every table needs history — a user's current avatar URL rarely needs a version trail. Versioning earns its complexity when the business genuinely asks "what was X at time T," when past values have legal or contractual significance (pricing at the time an order was placed, terms at the time a contract was signed), or when auditors or regulators require a full change history rather than just a current snapshot.

Pattern 1: A History/Audit Table

The most common approach keeps a "live" table with only the current state, plus a parallel history table that receives a copy of the old row (usually via a trigger) every time the live row is updated. This keeps the common case — reading current data — simple and fast, while still preserving a full history for the less frequent case of needing to look back.

Pattern 2: Row Versioning with an Effective Date Range

An alternative, often used for pricing or contract terms, stores every version as its own row in the same table, each tagged with a valid_from and valid_to (or valid_from and is_current) range, rather than overwriting anything. Querying "what was the price on this date" becomes a straightforward range lookup, and querying "what is the current price" becomes a filter for the currently active row. This pattern avoids a separate history table but requires every query to be conscious of the versioning columns.

• History table pattern: simple current-state table + separate audit table populated by triggers; best when history is read occasionally.
• Effective-dated row pattern: every version is a full row with a validity range in the same table; best when "what was true on date X" is a frequent, first-class query.

Example

CREATE TABLE product_prices (
    id          BIGINT PRIMARY KEY,
    product_id  BIGINT NOT NULL REFERENCES products(id),
    price       DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    valid_from  TIMESTAMP NOT NULL,
    valid_to    TIMESTAMP NULL  -- NULL means "still current"
);

-- The price that was active on a specific past date
SELECT price
FROM product_prices
WHERE product_id = 501
  AND valid_from <= '2026-03-15'
  AND (valid_to IS NULL OR valid_to > '2026-03-15');

-- Closing out the old price and opening a new one (done in one transaction)
UPDATE product_prices
SET valid_to = CURRENT_TIMESTAMP
WHERE product_id = 501 AND valid_to IS NULL;

INSERT INTO product_prices (id, product_id, price, valid_from, valid_to)
VALUES (9021, 501, 24.99, CURRENT_TIMESTAMP, NULL);

Every historical price for product 501 remains queryable by date range.
The "current price" query is simply the row where valid_to IS NULL.
Changing the price never overwrites history — it closes the old row's
validity range and inserts a new row, so an order placed last month can
still be shown with the price that was actually in effect that day.

Exercise

Each row of product_prices is one version of a price with the dates between which it applied; the current version has no end date. Write the query that returns the price of product 1 that was in effect on 15 March 2026.

Expected output: price then 120

CREATE TABLE product_prices (
  id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  price INTEGER NOT NULL,
  valid_from DATE NOT NULL,
  valid_to DATE
);

INSERT INTO product_prices (id, product_id, price, valid_from, valid_to) VALUES
  (1, 1, 100, '2026-01-01', '2026-03-01'),
  (2, 1, 120, '2026-03-01', '2026-06-01'),
  (3, 1, 135, '2026-06-01', NULL);

-- TODO: the price of product 1 in effect on 2026-03-15

CREATE TABLE product_prices (
  id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  price INTEGER NOT NULL,
  valid_from DATE NOT NULL,
  valid_to DATE
);

INSERT INTO product_prices (id, product_id, price, valid_from, valid_to) VALUES
  (1, 1, 100, '2026-01-01', '2026-03-01'),
  (2, 1, 120, '2026-03-01', '2026-06-01'),
  (3, 1, 135, '2026-06-01', NULL);

SELECT price
FROM product_prices
WHERE product_id = 1
  AND valid_from <= '2026-03-15'
  AND (valid_to IS NULL OR '2026-03-15' < valid_to);

Quick quiz

Interview questions