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...)