This article was originally published on bmf-tech.com.

Overview

This post summarizes what I have researched about the Bitemporal Data Model.

What is Bitemporal Data Model

It is one of the data models handled in the field of Temporal Data Models, referring to a data model with two timelines among those that handle time axes.

• Non-Temporal
  • Holds only the current state
  • Does not retain information about past history or future changes
• Uni-Temporal
  • Has a single timeline
  • Retains the start date when the data is valid and the end date when the validity period ends
• Bi-Temporal
  • Has two timelines
  • Retains transaction time (system time), which is the time recorded in the database, and valid time, which is the time the event occurred
  • Unlike Uni-Temporal, where transaction time and valid time are the same, Bi-Temporal has different timelines

The background for adopting a bitemporal data model includes the following requirements:

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

When handling a bitemporal data model in an RDB, the following difficulties arise:

• Ensuring data consistency
  • It is necessary to impose constraints to ensure that the valid time does not overlap for the same entity
  • Continuity of transaction time must be maintained
  • In PostgreSQL, the EXCLUDE constraint is useful
• Complexity of queries
  • Queries need to consider both valid time and transaction time
  • This can easily become complex, making performance tuning difficult
• Complexity of application logic
  • There are two timelines to consider when retrieving, updating, or deleting data
  • It is necessary to implement logic to maintain consistency

Although it is a data model that allows for referencing past history, adding past or future history, and retaining update information of history itself, achieving this requires accepting complexity. (That’s how I felt…)

Impressions

I have never dealt with highly flexible historical data in my work before, so I felt a deep sense of the world of handling history.

References

• en.wikipedia.org – Bitemporal modeling
• martinfowler.com – Bitemporal History
• martinfowler.com – Temporal Patterns
• wiki.postgresql.org – Temporal Data & Time Travel in PostgreSQL
• www.progress.com – What can be achieved with Bitemporal
• matsu-chara.hatenablog.com – Points to consider when introducing BiTemporal Data Model
• matsu-chara.hatenablog.com – A blog that’s mostly unclear
• www.slideshare.net – Introduction to Temporal Data Model and Reladomo for Data History Management #jjug_ccc #ccc_g3
• tech.smarthr.jp – How to walk with ActiveRecord::Bitemporal
• speakerdeck.com – Implementing Command History and Temporal Access – BiTemporal Data Model Practice
• speakerdeck.com – After the Practice of BiTemporal Data Model in SmartHR
• zenn.dev – Introduction to Interpretation and Utilization of Temporal Data Model from Business Perspective
• ontact-rajeshvinayagam.medium.com – Bi-Temporal Data Modeling: An Overview