About Bitemporal Data Model

Source: Dev.to

Temporal Data Model Types

Non‑Temporal

• Holds only the current state.
• Does not retain information about past history or future changes.

Uni‑Temporal

• Has a single timeline.
• Stores the start date when the data becomes valid and the end date when the validity period ends.

Bi‑Temporal

• Has two timelines:
  • Transaction time (system time) – the time recorded in the database.
  • Valid time – the time the event actually occurred.
• Unlike Uni‑Temporal, where transaction time and valid time are the same, Bi‑Temporal distinguishes them.

Why Adopt a Bitemporal Data Model?

• Tracking changes in data history
• Legal and audit requirements
• Improved flexibility in time‑series analysis

Challenges When Implementing a Bitemporal Model in an RDBMS

Ensuring Data Consistency

• Constraints are needed to prevent overlapping valid‑time periods for the same entity.
• Continuity of transaction time must be maintained.
• In PostgreSQL, the EXCLUDE constraint is useful for enforcing these rules.

Query Complexity

• Queries must consider both valid time and transaction time, which can quickly become intricate.
• This complexity makes performance tuning more difficult.

Application Logic Complexity

• Retrieval, update, and deletion operations must handle two timelines.
• Additional logic is required to maintain consistency across both time dimensions.

Although the bitemporal model enables referencing past history, adding past or future records, and retaining update information about the history itself, it introduces significant complexity.

Working with highly flexible historical data can be a steep learning curve, but it opens up powerful possibilities for managing and analyzing temporal information.

References

• Wikipedia – Bitemporal modeling
• Martin Fowler – Bitemporal History
• Martin Fowler – Temporal Patterns
• PostgreSQL Wiki – Temporal Data & Time Travel
• Progress – What can be achieved with Bitemporal
• Matsu‑Chara Blog – Points to consider when introducing BiTemporal Data Model
• SlideShare – Introduction to Temporal Data Model and Reladomo for Data History Management
• SmartHR Tech – How to walk with ActiveRecord::Bitemporal
• Speaker Deck – Implementing Command History and Temporal Access – BiTemporal Data Model Practice
• Zenn – Introduction to Interpretation and Utilization of Temporal Data Model from Business Perspective
• Medium – Bi‑Temporal Data Modeling: An Overview