Temporal Data and Bi-temporal Modeling¶

When historical accuracy matters, data must track both when facts were true in reality (valid time) and when they were recorded in the system (transaction time), enabling queries about what was known at any point.

Tension Addressed¶

Write Optimization vs. Read Optimization; Data Integrity vs. Performance

Recognizable As¶

Valid-from/valid-to columns, transaction timestamps, historical tables, slowly changing dimensions, as-of queries, audit requirements

Canonical Sources¶

Snodgrass, "Developing Time-Oriented Database Applications in SQL" (2000); Johnston & Weis, "Managing Time in Relational Databases" (2010)

Transfers To¶

Version control, audit systems, regulatory compliance, analytics (point-in-time reporting), any domain requiring historical analysis

Sub-concepts: - Valid time: When the fact was true in the real world - Transaction time: When the fact was recorded in the database - Bi-temporal: Tracking both dimensions, enabling queries like "what did we believe on date X about date Y?"