Designing a database that remembers what used to be true

EN databasesdata engineeringsqlite

Here’s a problem that comes up constantly when you’re building databases around real-world entities: things change.

A company changes its name. A person changes their address. A product changes its price. If your schema just overwrites the current value, you’ve permanently lost the ability to answer questions like “what was the registered address of this company in March 2023?” or “what price did this customer pay at the time of purchase?”

For a lot of applications this doesn’t matter. But for anything involving auditing, compliance, historical analysis, or data that feeds into legal or financial records, it matters enormously. And the fix isn’t complicated once you understand the pattern.

The naive approach and what breaks

Say you’re tracking company ownership data — who the beneficial owner of each company is. The naive schema:

CREATE TABLE companies (
  id       INTEGER PRIMARY KEY,
  name     TEXT NOT NULL,
  owner    TEXT NOT NULL,
  updated  TEXT NOT NULL
);

When ownership changes, you run UPDATE companies SET owner = 'New Owner', updated = datetime('now') WHERE id = 42.

The problem: you’ve just destroyed the historical record. You now know who owns the company today, but you can’t answer “who owned it last year?” You can’t reconstruct the state of the database at any past point in time. If you’re feeding this data into anything — reports, compliance checks, a product that charges based on ownership structure — you have no audit trail.

Slowly Changing Dimensions: Type 2

The standard solution in data warehousing is called a Slowly Changing Dimension Type 2. Instead of updating rows, you close the old record and insert a new one:

CREATE TABLE company_ownership (
  id          INTEGER PRIMARY KEY,
  company_id  INTEGER NOT NULL,
  owner       TEXT NOT NULL,
  valid_from  TEXT NOT NULL,   -- ISO 8601 timestamp
  valid_to    TEXT,            -- NULL means currently active
  is_current  INTEGER NOT NULL DEFAULT 1  -- 1 = current, 0 = historical
);

When ownership changes on company 42:

-- close the current record
UPDATE company_ownership
   SET valid_to = datetime('now'),
       is_current = 0
 WHERE company_id = 42
   AND is_current = 1;

-- insert the new record
INSERT INTO company_ownership (company_id, owner, valid_from, valid_to, is_current)
VALUES (42, 'New Owner Ltd', datetime('now'), NULL, 1);

Now you can answer historical questions:

-- who owned company 42 on a specific date?
SELECT owner
  FROM company_ownership
 WHERE company_id = 42
   AND valid_from <= '2023-03-15 00:00:00'
   AND (valid_to IS NULL OR valid_to > '2023-03-15 00:00:00');

This is the core of the pattern. valid_from and valid_to define the interval during which that row reflects reality. valid_to IS NULL is a common shorthand for “currently valid,” though I’ll mention a cleaner alternative below.

Bi-temporal modeling: two timelines

SCD Type 2 tracks validity time — when something was true in the real world. But there’s a second timeline worth tracking in some applications: transaction time, meaning when you recorded the information.

These two timelines are different. You might learn today that a company changed ownership six months ago. When you insert that record, the validity period starts in the past, but the transaction time is now.

CREATE TABLE company_ownership_bitemporal (
  id              INTEGER PRIMARY KEY,
  company_id      INTEGER NOT NULL,
  owner           TEXT NOT NULL,
  -- when this was true in the world
  valid_from      TEXT NOT NULL,
  valid_to        TEXT,
  -- when we recorded it
  recorded_from   TEXT NOT NULL DEFAULT (datetime('now')),
  recorded_to     TEXT
);

With both dimensions you can answer:

  • “What did we believe about this company’s ownership on 2023-03-15?” (transaction time query)
  • “Who actually owned this company on 2023-03-15?” (valid time query)
  • “What did we believe on 2023-06-01 about ownership on 2023-03-15?” (combined)

That last question sounds academic but it’s important for audits — you need to show what your system reported at a given time, not just what you now know was true.

Practical notes for SQLite

SQLite doesn’t have native PERIOD or temporal types (PostgreSQL 16 added PERIOD syntax, but SQLite doesn’t have it). You implement this with plain TEXT columns storing ISO 8601 timestamps, which sort correctly as strings.

A few things that help:

Use a high sentinel value instead of NULL for valid_to. NULL makes queries more verbose. Some implementations use '9999-12-31' as a sentinel meaning “currently valid.” This simplifies range queries:

-- with sentinel:
WHERE valid_from <= ? AND valid_to > ?
-- vs with NULL:
WHERE valid_from <= ? AND (valid_to IS NULL OR valid_to > ?)

Pick one approach and be consistent.

Add a check constraint to prevent overlapping intervals for the same entity:

-- SQLite doesn't enforce this natively, but document the invariant
-- and enforce it in application code or a trigger.
-- Two rows for the same company_id should never have overlapping (valid_from, valid_to).

Index on (company_id, valid_from, valid_to) for efficient point-in-time lookups.

When to use this

Not everything needs bi-temporal modeling. For a personal project tracking prices or a simple log, you might just append rows with timestamps and be done. But if you’re building something where the data has legal or financial weight, where external parties might audit it, or where you need to replay “what did the system know at time T” — this is the right foundation.

The cost is modest: slightly more complex writes, a few extra columns, and queries that include date range predicates. The benefit is that you never permanently lose a historical record.

If you’re designing a schema for something like this — company records, regulatory data, anything that tracks entities over time — feel free to reach out. It’s a class of problem I spend a lot of time on.