Chapter 14 · Time, History, Hierarchies, and Recursive Structures
Modeling Events, Effective Dates, and History
Model events, effective dates, valid-time intervals, and history so the database can answer not only what is true now, but what was true at a specific point in time.
Learning outcomes
Many databases are designed only for the present: one row contains the current value, and updates overwrite the past. That is correct only when history has no business value. Temporal modeling adds explicit time semantics so the database can answer questions such as “Who owned this Asset on March 1?”, “Which status was active at 10:15?”, or “What price was valid when the WorkOrder was opened?”
Distinguish event time, valid time, and recording time.
Model effective-date intervals without ambiguous overlap.
Choose between current-state tables and event/history tables.
Apply temporal constraints to WorkshopHub.
Three notions of time
| Time concept | Meaning |
|---|---|
| Event time | When the business event actually happened. |
| Valid time | When a fact is considered true in the business domain. |
| Recorded/system time | When the database learned or stored the fact. |
These times can differ. A repair completed yesterday may be entered into the system today.
Current state only
Asset( asset_id, customer_id, serial_number)This answers the current owner but cannot reconstruct prior ownership if customer_id is overwritten.
Effective-date history
AssetOwnership( asset_id, customer_id, valid_from, valid_to)Each row represents one ownership interval.
Closed-open intervals
A common design uses:
[valid_from, valid_to)meaning valid_from is inclusive and valid_to is exclusive. This makes adjacent intervals easy to express:
[2026-01-01, 2026-04-01)[2026-04-01, 2026-08-10)There is no overlap at the boundary.
Open-ended current interval
valid_to IS NULLoften means “currently active.” If this convention is used, queries and constraints must handle NULL consistently.
Temporal overlap
If an Asset can have only one owner at a time, these intervals are invalid:
Alice: [Jan 1, May 1)Bob: [Apr 1, Aug 1)April has two owners.
Time intervals often require constraints that span rows, not merely columns.
Effective dates versus timestamps
Use DATE when the business rule changes by whole day. Use TIMESTAMP when hour/minute/second matters. Do not choose precision accidentally.
Time zones
If timestamps represent real-world instants across locations, store unambiguous instants and define display-zone behavior separately. “2026-08-10 09:00” without a zone can be ambiguous across offices.
Event table
WorkOrderStatusEvent( event_id, work_order_id, from_status, to_status, occurred_at, recorded_at, actor_id)This stores transitions rather than merely the latest status.
Current state plus history
A practical model may keep:
WorkOrder.status_code -- fast current stateWorkOrderStatusEvent -- append-only historyThe current value is a projection of the event history. The design must define how the two remain consistent.
Event sourcing is a stronger choice
Event sourcing makes the event log the authoritative source and reconstructs state from events. That is much more than “keeping an audit table.” It changes write semantics, versioning, replay, and schema evolution. Use it only when its benefits justify the complexity.
Late-arriving corrections
Suppose ownership changed on July 1 but was entered on July 5. Then:
valid_from = July 1recorded_at = July 5Keeping both times preserves the distinction between business truth and system knowledge.
Bitemporal idea
When both valid-time history and system-recording history matter, the system can track two time dimensions. This supports questions such as “What did we believe on July 3 about who owned the Asset on July 1?”
WorkshopHub temporal candidates
| Fact | Time semantics |
|---|---|
| Asset ownership | Effective interval |
| WorkOrder status | Transition events + current state |
| Technician assignment | started_at / ended_at interval |
| Part catalog price | Current price + optional effective price history |
| Charged unit price | Historical transaction fact |
Practice: choose the time model
Warranty coverage
Warranty plan changes can be backdated and auditors need to know when staff entered the change. Which times should be stored?
Review answer
Store the business-valid interval/effective date and the system recording timestamp. If prior corrections must remain traceable, preserve versions rather than overwriting them.
Summary and next lesson
Temporal design requires explicit semantics: when a fact became valid, when it stopped being valid, and when the system recorded it. The next lesson applies these ideas to slowly changing operational data such as ownership, status, classification, and price.
References
- Richard T. Snodgrass, writings on temporal databases.
- Martin Fowler, writings on temporal patterns.
- Martin Kleppmann, Designing Data-Intensive Applications.