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
- Queries need to consider both valid time and transaction time
- 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.
Related posts
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
