🔎
Bitemporalデータモデルを少し把握しておく

2025/04/14に公開
 はじめに履歴管理について年に1回くらいは悩みませんか？

「いつからいつまで」という単一の時間軸で管理する方法が主に使われると思いますが、実際の業務ではそれだけでは対応しきれないケースがあるかと思います。そこで登場するのがBitemporalデータモデルです。有効時間と記録時間の2軸で履歴する方法なのですが、運用が難しいです。導入した結果、失敗したケースを聞いたので、えいやっで採用を考える前にどんなものかを把握しておきましょう。

(私は業務で使用したことはないです。)

 1. 単一の時間軸による履歴管理多くのシステムでは、履歴管理は「有効期間」や「開始日・終了日」など、単一の時間軸で行われてきました。たとえば、契約情報を管理する場合、「契約開始日」と「契約終了日」というカラムを持ったテーブルに、契約がいつ有効だったかを記録するのが多いかと思います。
CREATE TABLE contracts (
    contract_id   INTEGER PRIMARY KEY,
    contract_name VARCHAR(100),
    start_date    DATE,
    end_date      DATE
);
この方法では、例えば「2024年1月1日から2024年12月31日まで有効な契約」といった情報をシンプルに管理できます。

ではこの方法にはどのような課題が考えられるでしょうか。

更新したい場合に上書きすることを前提に作られているので、その場合にリスクがあります。契約内容が後から変更された場合、単にend_dateやstart_dateを更新するだけでは、元々記録されていた情報が上書きされ、過去の履歴が失われる危険があります。過去の更新履歴が残らないことで後から誤りに気づいて修正することができません。例えば、契約延長の情報を更新する際、変更前の値がどのような状態だったかを参照する必要が生じますが、単一時間軸ではその履歴を保持できないですよね。

 2. ログテーブルやバージョン管理の活用単一の時間軸による履歴管理の課題を解決することを目指してみましょう。

上記では履歴が残らないことが問題だったので、更新が発生するたびに変更履歴を別テーブルに記録する方法を採用してみましょう。
CREATE TABLE contract_versions (
    contract_id   INTEGER,
    version_no    INTEGER,
    contract_name VARCHAR(100),
    start_date    DATE,
    end_date      DATE,
    created_at   TIMESTAMP,
    updated_at    TIMESTAMP,
    PRIMARY KEY (contract_id, version_no)
);
このようにすれば、各変更ごとにバージョン番号をインクリメントして履歴を残すことができます。更新のたびに新しいレコードを追加し、後から「どの時点でどんな内容が記録されていたか」を確認することが可能です。

ではこの方法で対応できない場合とは？
例えば、ある契約書に「2024年1月1日から有効」と記載されていたとします。しかし、実際にその契約情報がシステムに登録されたのが「2024年1月10日」だった場合、現実の契約開始日とシステム上で記録される開始日がずれてしまいます。システムに残るのは登録された1月10日という情報だけになってしまうため、本来は1月1日から契約が有効だったという事実が、後から見ると分からないですね。

契約書に合わせてstart_dateを「2024年1月1日」にした場合は、システムでの有効になった日はわからないですよね。(created_atとstart_dateはイコールではない場合)
契約書に記載された正しい有効期間と、システムにおける変更履歴の記録日時の2つの時間情報を同時に管理する仕組みがありません。そのため、たとえば契約延長などで契約内容が後から訂正された場合に、正しい契約期間がいつからいつまでで、変更がシステムに反映されたのがいつなのかを、同時に正確に把握することが難しくなってしまいます。

 Bitemporalデータモデルとは？Bitemporalデータモデルとは、簡単に言えば「2つの時間軸を持ったデータの履歴管理」モデルです。通常、履歴データを管理するテーブルでは、あるレコードの「有効期間」（業務上そのデータが正しい期間）だけを扱います。しかし、Bitemporalモデルでは、これに加えて「システムがそのデータをいつ記録したか」という情報も保存するため、2種類の時間軸でデータを管理することになります。
有効時間（Valid Time）

有効時間は、現実世界においてその情報が実際に有効だった期間を指します。たとえば「2024年1月1日から2024年12月31日まで」が契約の有効期間であれば、これがValid Timeとなります。これは、実際の業務や契約書に記載される期間と考えると分かりやすいです。
記録時間（Transaction Time）

一方の記録時間は、その情報がシステム上に「記録された期間」を示します。現実に起こった事実がシステムに反映されるのは必ずしも同じタイミングとは限りません。例えば、契約が実際には2024年1月1日から有効であっても、システムに登録されたのが2024年2月1日だったとすると、そのレコードは「2024年2月1日から存在する」と記録されます。つまり、Transaction Timeはシステムの内部事情（記録のタイミング）を反映するものです。
この2つの時間軸を併せ持つことで、「実際に起こった事実」と「その記録がどう管理されたか」の両側面を同時に扱うことができます。結果、過去の誤った情報が修正された場合でも、その変更前後の履歴がすべて残るため、後から「当時はこう記録していた」という追跡が可能となります。

 なぜ2つの時間軸が必要なのかもう一度掘り下げます。

遡及修正への対応

後から「実際の事実」が判明して、過去の登録内容を修正する必要が出てくることがあります。たとえば、契約書の内容が延長されたり、誤って登録された情報が訂正されたりする場合、単に更新すると以前の記録が失われてしまいます。Bitemporalモデルなら、以下のように処理できます。
元のレコードを「閉じる」

記録としての期間（Transaction Time）を終了させ、過去の情報をそのまま残す。
新しいレコードを追加

修正された有効期間（Valid Time）と、新たにシステムに反映された日付（Transaction Time）で新たなレコードを追加する。

システムと現実のタイムラグを反映

現実の事実とシステムへの反映時刻が必ずしも一致しないという問題はよくあります。たとえば、あるイベントが発生しても、それをシステムに登録するのが数時間後だったり、場合によっては翌日になったりするケースです。Bitemporalモデルなら、「実際の発生日時」と「登録日時」を分けて管理できるので、どの時点でシステムがどの情報を持っていたのかを正確に把握できます。

 サンプル実装実際にBitemporalデータモデルをどのようにテーブルで表現するか、簡単な設計例を示します。

ここでは契約テーブルを例に考えてみましょう。主なカラムは以下のようになります。
contract_id – 契約を識別するID（主キーの一部）
業務時間（Valid Time）関連:

valid_from – 契約が現実に有効開始した日時

valid_to – 契約が現実に有効終了する日時

システム時間（Transaction Time）関連:

transaction_from – レコードがシステムに登録された日時（そのレコードの有効開始日時点, *レコード作成のタイミングのcreated_atとは異なる）

transaction_to – レコードがシステム上有効でなくなる日時

簡単なテーブル定義例をSQL風に表現すると次のようになります。
CREATE TABLE contracts_history (
    contract_id        INTEGER,        -- 契約を識別するID
    contract_status    VARCHAR(20),    -- 契約状態や種別など（例：active/terminated 等）
    valid_from         DATE,           -- 有効期間の開始日（業務上の開始日時）
    valid_to           DATE,           -- 有効期間の終了日（業務上の終了日時）
    transaction_from   DATE,           -- システム記録開始日時
    transaction_to     DATE,           -- システム記録終了日時
    PRIMARY KEY (contract_id, valid_from, transaction_from)
);

 履歴管理クエリの例
現在有効な契約を取得するクエリ:

「現在（今この瞬間）有効な契約」を取得するには、有効時間上現在を含み、かつトランザクション時間上も現在有効なレコードを選ぶ必要があります。具体的には、valid_fromが現在日時より過去または同時で、valid_toが現在日時より未来であるもの、かつtransaction_fromが現在より過去同時でtransaction_toが現在より未来であるもの、を選択します。
SELECT *
FROM contracts_history
WHERE valid_from <= CURRENT_DATE 
  AND valid_to > CURRENT_DATE           
  AND transaction_from <= CURRENT_DATE 
  AND transaction_to > CURRENT_DATE;   
上記クエリにマッチするのは、「現時点で事実として有効で、かつ最新の有効なレコード」です。例えば延長前と延長後の契約履歴があっても、古い方はtransaction_toが過去日付になっているため除外され、最新の情報だけが取得されます。

特定の時点の状態を取得するクエリ:

過去や未来のある時点において、データがどういう状態だったか（あるいは予定か）を取得することもできます。例えば「2024年7月1日時点で有効だった契約」を知りたい場合、その日付を有効期間に含むレコードを探します。さらに、現時点から見て過去の状態を知りたいのか、当時の記録通りの状態を再現したいのかで条件が異なります。

現時点から見た過去の状態: これは、「いま持っている履歴データに基づけば、2024-07-01にはどの契約が有効だったか」を問うものです。クエリ条件としては有効時間だけを指定すればよく、トランザクション時間は現在時刻でフィルタすればOKです（現在有効な履歴を使う）
SELECT contract_id, contract_status
FROM contracts_history
WHERE valid_from <= DATE '2024-07-01' 
  AND valid_to > DATE '2024-07-01'
  AND transaction_to = DATE '9999-12-31';

当時の記録上の状態を確認するクエリ:

「2024年7月1日時点でシステムが認識していた契約状態」を問うものです。過去に誤って記録されていたが後で修正された場合などは、当時の記録と現時点の認識が異なる可能性があります。そのため、このクエリではトランザクション時間もその「当時の日時」で区切る必要があります。例えば「2024年7月1日時点でシステム上有効だった契約」は
SELECT contract_id, contract_status
FROM contracts_history
WHERE valid_from <= DATE '2024-07-01' 
  AND valid_to > DATE '2024-07-01'
  AND transaction_from <= DATE '2024-07-01'
  AND transaction_to > DATE '2024-07-01';
この条件にすると、2024/07/01の時点で有効期間上有効かつ、その時点でレコードが存在していたものを取得できます。例えば6月に契約延長が記録されていれば、その延長後のレコードが該当し、仮に延長がまだ記録されていなければ延長前のレコードが該当する、といったように当時の認識を再現できます。
!ややこしくないですか

 Bitemporalデータモデル設計時の注意データ整合性の複雑さ:

1つのエンティティ（契約や社員など）について、有効時間が重複する履歴がないように制約を設ける必要があります​。たとえば同じ契約IDで有効期間が重なって2つ存在してはいけません。また、トランザクション時間も各エンティティで途切れず連続するように管理する必要があります​

これらを保証するに排他制約やチェック制約、トリガーの活用などが求められてきます。
外部キー制約が使いにくい

例えば他テーブルから契約テーブルの「現在有効な契約」を参照したい場合、契約IDだけでは一意にレコードを決められず「どの時点の契約か」を考慮する必要があります​。同じIDでも時期によって複数レコードが存在するため、1対1の参照関係にはなりません、トリガーやアプリケーションロジックで整合性チェックを行ったり、有効期間も含めて参照整合性を検証するなどの方法が必要になってきます。
クエリの複雑さと性能

上述した通り、有効時間とトランザクション時間の両方を条件に含めたクエリを書く必要があり、SQLが複雑になりがちです​。
実装ロジックの複雑さ:

データ更新ロジックも平易ではありません。新規データの挿入、既存データの変更（履歴の継続）、過去データの修正（遡及変更）など、操作ごとに扱うべき時間軸と処理が異なります。開発者は2つの時間軸を常に意識してコーディングする必要があり、バグの温床になりえます​。

 運用するのは難しいこのモデルは「有効時間」と「記録時間」という、全く異なる2つの時間軸を同時に管理する仕組みです。たとえば、契約書に「2024年1月1日から有効」と記載されていても、システムに実際に登録されるのは「2024年1月10日」であったとします。現場では、この2つの日付を同一視してしまいがちですが、本来は業務上の真実（実際に契約が有効であった期間）と、システムに記録されたタイミングをきちんと区別する必要があります。これを正確に運用するためには、双方の意味を明確に理解し、設計段階で細かくルールを決める必要があるかと思います。
さらに、更新が発生するたびに、「古いレコードに終了日時を付けて閉じ」、新しい状態を新規のレコードとして追加するという処理は、通常のデータ更新とは異なり、手間がかかります。現場の開発者が「単に更新すればいいだけ」と油断してしまうと、履歴が正しく管理されず、後になって「いつ、どの変更が行われたか」が曖昧になってしまいます。

また、こうした複雑な更新ロジックは、クエリの内容やデータベース全体のパフォーマンスにも大きな影響を与え、設計の不備がそのままシステムの不安定さにつながるので、必ず必要になるケースだと判断できる場合以外は安易に取り入れない方が良いのかなと思いました。
はじめに

1. 単一の時間軸による履歴管理

2. ログテーブルやバージョン管理の活用

Bitemporalデータモデルとは？

なぜ2つの時間軸が必要なのか

サンプル実装

履歴管理クエリの例

Bitemporalデータモデル設計時の注意

運用するのは難しい

Discussion
