Data Modeling & Schema Design
Temporal Data Modeling
Temporal modeling distinguishes when a fact is true in the business from when the database knew it. Valid time, transaction time, and half-open intervals make corrections, audits, and as-of queries precise instead of overwriting history.
- Valid time describes the period when a fact applies in the modeled world.
- Transaction time describes when a version was recorded in the database.
- Bitemporal data carries both valid-time and transaction-time intervals.
- Half-open intervals
[start, end)compose without boundary overlap. - Temporal keys prevent overlapping versions for the same business identity; adding timestamps to an ordinary key does not enforce non-overlap.
- Current-state, valid-as-of, and known-as-of queries are different APIs and should be named and tested explicitly.
A retroactive product-price correction
CREATE TABLE product_price_history (
product_id BIGINT NOT NULL REFERENCES products(product_id),
valid_from TIMESTAMP NOT NULL,
valid_to TIMESTAMP NOT NULL,
recorded_from TIMESTAMP NOT NULL,
recorded_to TIMESTAMP NOT NULL,
price DECIMAL(19, 4) NOT NULL CHECK (price >= 0),
CHECK (valid_from < valid_to),
CHECK (recorded_from < recorded_to),
PRIMARY KEY (product_id, valid_from, recorded_from)
);| Question | Valid-time filter | Transaction-time filter |
|---|---|---|
| Current accepted price | Contains now | Contains now |
| Price valid on February 5, using today's corrected knowledge | Contains Feb 5 | Contains now |
| What the system reported on February 12 for February 5 | Contains Feb 5 | Contains Feb 12 |
| Complete audit | No collapse | No collapse |