🐈

[Claude 3.5 * Perplexity][Bitemporal Data Model]入出金取引管理の実装について

2024/06/23に公開

Perplexity

Claude

bitemporaldatamode

tech

Bitemporal Data Model: 入出金取引管理システムの例

はじめに

Bitemporal Data Model（二時間データモデル）は、データの2つの時間軸を管理する方法です：

ビジネス時間：実際の世界でイベントが発生した時間
システム時間：データがシステムに記録された時間

この方法を使用することで、過去のある時点でのデータの状態を正確に把握したり、データの変更履歴を追跡したりすることができます。

1. 必要なテーブル構造

1.1 入出金取引テーブル

CREATE TABLE transactions (
    transaction_id BIGINT PRIMARY KEY,
    sender_account_id BIGINT NOT NULL,
    receiver_account_id BIGINT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    transaction_type VARCHAR(20) NOT NULL,
    valid_from TIMESTAMP NOT NULL,
    valid_to TIMESTAMP NOT NULL,
    system_from TIMESTAMP NOT NULL,
    system_to TIMESTAMP NOT NULL,
    FOREIGN KEY (sender_account_id) REFERENCES bank_accounts(account_id),
    FOREIGN KEY (receiver_account_id) REFERENCES bank_accounts(account_id)
);

transaction_id: 取引を一意に識別するID（プライマリーキー）
sender_account_id: 送金元の銀行口座ID（外部キー）
receiver_account_id: 送金先の銀行口座ID（外部キー）
amount: 取引金額
transaction_type: 取引種類（例：送金、入金、出金）
valid_from: 取引の有効開始日時（ビジネス時間）
valid_to: 取引の有効終了日時（ビジネス時間）
system_from: システムでの記録開始日時（システム時間）
system_to: システムでの記録終了日時（システム時間）

1.2 銀行口座テーブル

CREATE TABLE bank_accounts (
    account_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    account_number VARCHAR(20) NOT NULL,
    balance DECIMAL(10, 2) NOT NULL,
    valid_from TIMESTAMP NOT NULL,
    valid_to TIMESTAMP NOT NULL,
    system_from TIMESTAMP NOT NULL,
    system_to TIMESTAMP NOT NULL
);

account_id: 銀行口座を一意に識別するID（プライマリーキー）
user_id: 口座所有者のユーザーID
account_number: 口座番号
balance: 口座残高
valid_from: 口座情報の有効開始日時（ビジネス時間）
valid_to: 口座情報の有効終了日時（ビジネス時間）
system_from: システムでの記録開始日時（システム時間）
system_to: システムでの記録終了日時（システム時間）

2. 入出金取引テーブルの設計詳細

2.1 送金元と送金先の関連付け

sender_account_idとreceiver_account_idは、bank_accountsテーブルのaccount_idを参照する外部キーです。
これにより、各取引の送金元と送金先の口座を正確に特定できます。

2.2 取引種類と金額の記録方法

transaction_type: 取引の種類を示す文字列（例：'TRANSFER', 'DEPOSIT', 'WITHDRAWAL'）
amount: 取引金額を小数点以下2桁まで記録

2.3 時間関連カラムの意味と更新タイミング

valid_from（ビジネス時間）: 取引が有効になった日時
- 新規取引作成時に設定
- 取引内容の修正時に更新される可能性あり
valid_to（ビジネス時間）: 取引が無効になった日時
- 新規作成時は遠い未来の日時（例：'9999-12-31 23:59:59'）
- 取引のキャンセルや修正時に更新
system_from（システム時間）: システムにレコードが挿入された日時
- 新規レコード作成時にシステムの現在時刻で自動設定
- 以降は変更されない
system_to（システム時間）: システム上でレコードが無効になった日時
- 新規作成時は遠い未来の日時
- レコードの更新や削除時に、古いレコードのsystem_toを更新時刻に設定

3. 具体的なシナリオ

シナリオ1: 新規取引の作成

ユーザーAがユーザーBに1000円を送金する場合：

-- 新規取引を作成
-- 理由: 新しい送金取引をシステムに記録するため
INSERT INTO transactions (
    transaction_id, sender_account_id, receiver_account_id, 
    amount, transaction_type, 
    valid_from, valid_to, system_from, system_to
) VALUES (
    1, 1001, 1002, 1000.00, 'TRANSFER',
    '2024-06-26 10:00:00', '9999-12-31 23:59:59',
    CURRENT_TIMESTAMP, '9999-12-31 23:59:59'
);

-- 送金元の残高を更新
-- 理由: 送金元の口座から送金額を引き落とすため
UPDATE bank_accounts
SET balance = balance - 1000.00,
    valid_from = '2024-06-26 10:00:00',
    system_from = CURRENT_TIMESTAMP
WHERE account_id = 1001;

-- 送金先の残高を更新
-- 理由: 送金先の口座に送金額を入金するため
UPDATE bank_accounts
SET balance = balance + 1000.00,
    valid_from = '2024-06-26 10:00:00',
    system_from = CURRENT_TIMESTAMP
WHERE account_id = 1002;

この時点でのテーブルの状態：

transactions テーブル：

| transaction_id | sender_account_id | receiver_account_id | amount | transaction_type | valid_from          | valid_to            | system_from         | system_to           |
|----------------|--------------------|-----------------------|--------|------------------|---------------------|---------------------|---------------------|---------------------|
| 1              | 1001               | 1002                  | 1000.00| TRANSFER         | 2024-06-26 10:00:00 | 9999-12-31 23:59:59 | 2024-06-26 10:00:00 | 9999-12-31 23:59:59 |

bank_accounts テーブル（更新後）：

| account_id | user_id | account_number | balance  | valid_from          | valid_to            | system_from         | system_to           |
|------------|---------|----------------|----------|---------------------|---------------------|---------------------|---------------------|
| 1001       | 101     | A12345         | 9000.00  | 2024-06-26 10:00:00 | 9999-12-31 23:59:59 | 2024-06-26 10:00:00 | 9999-12-31 23:59:59 |
| 1002       | 102     | B67890         | 11000.00 | 2024-06-26 10:00:00 | 9999-12-31 23:59:59 | 2024-06-26 10:00:00 | 9999-12-31 23:59:59 |

シナリオ2: 取引の修正（金額の訂正）

取引ID 1の金額を1000円から1200円に修正する場合：

-- 既存の取引レコードを無効化
-- 理由: 修正前の取引記録を履歴として残すため
UPDATE transactions
SET valid_to = '2024-06-26 11:00:00',
    system_to = CURRENT_TIMESTAMP
WHERE transaction_id = 1 AND system_to = '9999-12-31 23:59:59';

-- 新しい取引レコードを挿入
-- 理由: 修正後の新しい取引内容を記録するため
INSERT INTO transactions (
    transaction_id, sender_account_id, receiver_account_id, 
    amount, transaction_type, 
    valid_from, valid_to, system_from, system_to
) VALUES (
    1, 1001, 1002, 1200.00, 'TRANSFER',
    '2024-06-26 11:00:00', '9999-12-31 23:59:59',
    CURRENT_TIMESTAMP, '9999-12-31 23:59:59'
);

-- 送金元の残高を再更新
-- 理由: 送金元の口座から追加の200円を引き落とすため
UPDATE bank_accounts
SET balance = balance - 200.00,
    valid_from = '2024-06-26 11:00:00',
    system_from = CURRENT_TIMESTAMP
WHERE account_id = 1001;

-- 送金先の残高を再更新
-- 理由: 送金先の口座に追加の200円を入金するため
UPDATE bank_accounts
SET balance = balance + 200.00,
    valid_from = '2024-06-26 11:00:00',
    system_from = CURRENT_TIMESTAMP
WHERE account_id = 1002;

この時点でのテーブルの状態：

transactions テーブル：

| transaction_id | sender_account_id | receiver_account_id | amount | transaction_type | valid_from          | valid_to            | system_from         | system_to           |
|----------------|--------------------|-----------------------|--------|------------------|---------------------|---------------------|---------------------|---------------------|
| 1              | 1001               | 1002                  | 1000.00| TRANSFER         | 2024-06-26 10:00:00 | 2024-06-26 11:00:00 | 2024-06-26 10:00:00 | 2024-06-26 11:00:00 |
| 1              | 1001               | 1002                  | 1200.00| TRANSFER         | 2024-06-26 11:00:00 | 9999-12-31 23:59:59 | 2024-06-26 11:00:00 | 9999-12-31 23:59:59 |

bank_accounts テーブル（更新後）：

| account_id | user_id | account_number | balance  | valid_from          | valid_to            | system_from         | system_to           |
|------------|---------|----------------|----------|---------------------|---------------------|---------------------|---------------------|
| 1001       | 101     | A12345         | 8800.00  | 2024-06-26 11:00:00 | 9999-12-31 23:59:59 | 2024-06-26 11:00:00 | 9999-12-31 23:59:59 |
| 1002       | 102     | B67890         | 11200.00 | 2024-06-26 11:00:00 | 9999-12-31 23:59:59 | 2024-06-26 11:00:00 | 9999-12-31 23:59:59 |

シナリオ3: 取引のキャンセル

取引ID 1をキャンセルする場合：

-- 既存の取引レコードを無効化
-- 理由: キャンセル前の取引記録を履歴として残すため
UPDATE transactions
SET valid_to = '2024-06-26 12:00:00',
    system_to = CURRENT_TIMESTAMP
WHERE transaction_id = 1 AND system_to = '9999-12-31 23:59:59';

-- キャンセル状態の新しい取引レコードを挿入
-- 理由: 取引がキャンセルされたことを記録するため
INSERT INTO transactions (
    transaction_id, sender_account_id, receiver_account_id, 
    amount, transaction_type, 
    valid_from, valid_to, system_from, system_to
) VALUES (
    1, 1001, 1002, 1200.00, 'CANCELLED',
    '2024-06-26 12:00:00', '9999-12-31 23:59:59',
    CURRENT_TIMESTAMP, '9999-12-31 23:59:59'
);

-- 送金元の残高を元に戻す
-- 理由: キャンセルにより送金元の口座に金額を返金するため
UPDATE bank_accounts
SET balance = balance + 1200.00,
    valid_from = '2024-06-26 12:00:00',
    system_from = CURRENT_TIMESTAMP
WHERE account_id = 1001;

-- 送金先の残高を元に戻す
-- 理由: キャンセルにより送金先の口座から金額を引き落とすため
UPDATE bank_accounts
SET balance = balance - 1200.00,
    valid_from = '2024-06-26 12:00:00',
    system_from = CURRENT_TIMESTAMP
WHERE account_id = 1002;

この時点でのテーブルの状態：

transactions テーブル：

| transaction_id | sender_account_id | receiver_account_id | amount | transaction_type | valid_from          | valid_to            | system_from         | system_to           |
|----------------|--------------------|-----------------------|--------|------------------|---------------------|---------------------|---------------------|---------------------|
| 1              | 1001               | 1002                  | 1000.00| TRANSFER         | 2024-06-26 10:00:00 | 2024-06-26 11:00:00 | 2024-06-26 10:00:00 | 2024-06-26 11:00:00 |
| 1              | 1001               | 1002                  | 1200.00| TRANSFER         | 2024-06-26 11:00:00 | 2024-06-26 12:00:00 | 2024-06-26 11:00:00 | 2024-06-26 12:00:00 |
| 1              | 1001               | 1002                  | 1200.00| CANCELLED        | 2024-06-26 12:00:00 | 9999-12-31 23:59:59 | 2024-06-26 12:00:00 | 9999-12-31 23:59:59 |

bank_accounts テーブル（更新後）：

| account_id | user_id | account_number | balance  | valid_from          | valid_to            | system_from         | system_to           |
|------------|---------|----------------|----------|---------------------|---------------------|---------------------|---------------------|
| 1001       | 101     | A12345         | 10000.00 | 2024-06-26 12:00:00 | 9999-12-31 23:59:59 | 2024-06-26 12:00:00 | 9999-12-31 23:59:59 |
| 1002       | 102     | B67890         | 10000.00 | 2024-06-26 12:00:00 | 9999-12-31 23:59:59 | 2024-06-26 12:00:00 | 9999-12-31 23:59:59 |

シナリオ4: 過去の時点での取引状態の確認

2024年6月26日11時30分時点での取引状態を確認する場合：

-- 理由: 特定の時点における有効な取引記録を取得するため
SELECT *
FROM transactions
WHERE valid_from <= '2024-06-26 11:30:00'
  AND valid_to > '2024-06-26 11:30:00'
  AND system_from <= CURRENT_TIMESTAMP
  AND system_to > CURRENT_TIMESTAMP;

このクエリの結果：

| transaction_id | sender_account_id | receiver_account_id | amount | transaction_type | valid_from          | valid_to            | system_from         | system_to           |
|----------------|--------------------|-----------------------|--------|------------------|---------------------|---------------------|---------------------|---------------------|
| 1              | 1001               | 1002                  | 1200.00| TRANSFER         | 2024-06-26 11:00:00 | 2024-06-26 12:00:00 | 2024-06-26 11:00:00 | 2024-06-26 12:00:00 |

シナリオ5: 取引履歴の追跡

取引ID 1の全履歴を確認する場合：

-- 理由: 特定の取引IDに関するすべての変更履歴を時系列順で取得するため
SELECT *
FROM transactions
WHERE transaction_id = 1
ORDER BY system_from;

このクエリの結果：

| transaction_id | sender_account_id | receiver_account_id | amount | transaction_type | valid_from          | valid_to            | system_from         | system_to           |
|----------------|--------------------|-----------------------|--------|------------------|---------------------|---------------------|---------------------|---------------------|
| 1              | 1001               | 1002                  | 1000.00| TRANSFER         | 2024-06-26 10:00:00 | 2024-06-26 11:00:00 | 2024-06-26 10:00:00 | 2024-06-26 11:00:00 |
| 1              | 1001               | 1002                  | 1200.00| TRANSFER         | 2024-06-26 11:00:00 | 2024-06-26 12:00:00 | 2024-06-26 11:00:00 | 2024-06-26 12:00:00 |
| 1              | 1001               | 1002                  | 1200.00| CANCELLED        | 2024-06-26 12:00:00 | 9999-12-31 23:59:59 | 2024-06-26 12:00:00 | 9999-12-31 23:59:59 |

4. Bitemporal Data Modelの利点

データの整合性: 過去のある時点でのデータ状態を正確に再現できます。
監査とコンプライアンス: データの変更履歴を詳細に追跡できるため、規制要件への対応が容易になります。
エラー修正: 過去のデータエラーを修正しつつ、修正前の状態も保持できます。
ビジネスインテリジェンス: 時系列でのデータ分析が可能になり、トレンドの把握や予測に役立ちます。

5. 実際のビジネスシーンでの応用例

金融取引の追跡: 銀行や証券会社が、取引の正確な履歴を維持し、監査に対応する。
保険金請求の管理: 保険会社が、請求処理の各段階を追跡し、ポリシーの変更履歴を管理する。
在庫管理: 小売業者が、商品の在庫レベルの変動を時系列で追跡し、需要予測に活用する。
人事記録: 企業が、従業員の役職や給与の変更履歴を正確に管理し、労務問題に対応する。
契約管理: 法務部門が、契約条件の変更履歴を追跡し、特定時点での有効な契約内容を確認する。

これらの例から分かるように、Bitemporal Data Modelは、データの時間的な変化を正確に追跡する必要がある様々な業界や部門で有用です。

[Perplexity][Bitemporal Data Model]入出金取引管理の実装について

Bitemporal Data Modelを使った入出金取引管理の実装手順を、初心者にも分かりやすく説明します。入出金取引を例に、具体的なシナリオを交えて解説します。

まず、入出金取引を管理するテーブルを作成します：

CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    from_user_id INT,
    to_user_id INT,
    amount DECIMAL(10, 2),
    transaction_type VARCHAR(50),
    valid_from DATETIME,
    valid_to DATETIME,
    system_from DATETIME,
    system_to DATETIME
);

このテーブルの説明：

id: 各取引の一意の識別子（プライマリーキー）
from_user_id: 送金元ユーザーID
to_user_id: 送金先ユーザーID
amount: 取引金額
transaction_type: 取引種類（例：送金、入金、出金）
valid_from: 取引の有効開始日時（ビジネス時間）
valid_to: 取引の有効終了日時（ビジネス時間）
system_from: システムでの記録開始日時（システム時間）
system_to: システムでの記録終了日時（システム時間）

それでは、具体的なシナリオを見ていきましょう。

シナリオ1: 新規取引の登録

ユーザー1からユーザー2へ1000円を送金する取引を2023-06-01 10:00:00に登録します。

-- 新規取引を登録
INSERT INTO transactions 
(from_user_id, to_user_id, amount, transaction_type, valid_from, valid_to, system_from, system_to)
VALUES 
(1, 2, 1000, '送金', '2023-06-01 10:00:00', '9999-12-31 23:59:59', NOW(), '9999-12-31 23:59:59');

コメント: この SQL 文は新しい取引を登録します。valid_to と system_to に遠い未来の日付を設定することで、現在有効なレコードであることを示します。

この時点でのテーブルの状態：

id	from_user_id	to_user_id	amount	transaction_type	valid_from	valid_to	system_from	system_to
1	1	2	1000	送金	2023-06-01 10:00:00	9999-12-31 23:59:59	2023-06-01 10:00:00	9999-12-31 23:59:59

シナリオ2: 取引金額の修正

2023-06-02 09:00:00に、前日の取引金額が間違っていたことが判明し、1000円から1500円に修正します。

-- 既存の取引を終了させる
UPDATE transactions 
SET valid_to = '2023-06-02 09:00:00', system_to = NOW()
WHERE id = 1 AND system_to = '9999-12-31 23:59:59';

-- 新しい取引情報を挿入
INSERT INTO transactions 
(from_user_id, to_user_id, amount, transaction_type, valid_from, valid_to, system_from, system_to)
VALUES 
(1, 2, 1500, '送金', '2023-06-01 10:00:00', '9999-12-31 23:59:59', NOW(), '9999-12-31 23:59:59');

コメント: 最初の UPDATE 文は既存の取引レコードを終了させ、次の INSERT 文で修正後の新しい取引情報を挿入します。これにより、取引の履歴が保持されます。

この時点でのテーブルの状態：

id	from_user_id	to_user_id	amount	transaction_type	valid_from	valid_to	system_from	system_to
1	1	2	1000	送金	2023-06-01 10:00:00	2023-06-02 09:00:00	2023-06-01 10:00:00	2023-06-02 09:00:00
2	1	2	1500	送金	2023-06-01 10:00:00	9999-12-31 23:59:59	2023-06-02 09:00:00	9999-12-31 23:59:59

シナリオ3: 取引の取り消し

2023-06-03 14:00:00に、この取引を完全に取り消すことになりました。

-- 既存の取引を終了させる
UPDATE transactions 
SET valid_to = '2023-06-03 14:00:00', system_to = NOW()
WHERE id = 2 AND system_to = '9999-12-31 23:59:59';

-- 取り消し情報を挿入
INSERT INTO transactions 
(from_user_id, to_user_id, amount, transaction_type, valid_from, valid_to, system_from, system_to)
VALUES 
(1, 2, 0, '取り消し', '2023-06-03 14:00:00', '9999-12-31 23:59:59', NOW(), '9999-12-31 23:59:59');

コメント: 最初の UPDATE 文で既存の取引を終了させ、次の INSERT 文で取り消し情報を挿入します。金額を0円とすることで取引が取り消されたことを示します。

この時点でのテーブルの状態：

id	from_user_id	to_user_id	amount	transaction_type	valid_from	valid_to	system_from	system_to
1	1	2	1000	送金	2023-06-01 10:00:00	2023-06-02 09:00:00	2023-06-01 10:00:00	2023-06-02 09:00:00
2	1	2	1500	送金	2023-06-01 10:00:00	2023-06-03 14:00:00	2023-06-02 09:00:00	2023-06-03 14:00:00
3	1	2	0	取り消し	2023-06-03 14:00:00	9999-12-31 23:59:59	2023-06-03 14:00:00	9999-12-31 23:59:59

シナリオ4: 新規入金取引の登録

2023-06-04 11:30:00に、ユーザー3が自身の口座に2000円を入金しました。

INSERT INTO transactions 
(from_user_id, to_user_id, amount, transaction_type, valid_from, valid_to, system_from, system_to)
VALUES 
(3, 3, 2000, '入金', '2023-06-04 11:30:00', '9999-12-31 23:59:59', NOW(), '9999-12-31 23:59:59');

コメント: この SQL 文は新しい入金取引を登録します。入金の場合、from_user_id と to_user_id は同じになります。

この時点でのテーブルの状態：

id	from_user_id	to_user_id	amount	transaction_type	valid_from	valid_to	system_from	system_to
1	1	2	1000	送金	2023-06-01 10:00:00	2023-06-02 09:00:00	2023-06-01 10:00:00	2023-06-02 09:00:00
2	1	2	1500	送金	2023-06-01 10:00:00	2023-06-03 14:00:00	2023-06-02 09:00:00	2023-06-03 14:00:00
3	1	2	0	取り消し	2023-06-03 14:00:00	9999-12-31 23:59:59	2023-06-03 14:00:00	9999-12-31 23:59:59
4	3	3	2000	入金	2023-06-04 11:30:00	9999-12-31 23:59:59	2023-06-04 11:30:00	9999-12-31 23:59:59

シナリオ5: 過去の時点での取引状況の確認

2023-06-02 15:00:00時点での有効な取引を確認したい場合：

SELECT * FROM transactions
WHERE valid_from <= '2023-06-02 15:00:00'
  AND valid_to > '2023-06-02 15:00:00'
  AND system_from <= '2023-06-02 15:00:00'
  AND system_to > '2023-06-02 15:00:00';

コメント: この SQL 文は指定した日時（2023-06-02 15:00:00）時点で有効だった取引を取得します。valid_fromとvalid_to、system_fromとsystem_toの両方を考慮することで、その時点で知られていた情報のみを取得します。

この時点でのクエリ結果：

id	from_user_id	to_user_id	amount	transaction_type	valid_from	valid_to	system_from	system_to
2	1	2	1500	送金	2023-06-01 10:00:00	2023-06-03 14:00:00	2023-06-02 09:00:00	2023-06-03 14:00:00

このように、Bitemporal Data Modelを使用することで、データの変更履歴を正確に追跡し、過去のある時点でのデータの状態を再現することができます。これは、金融取引のような厳密な記録が必要な分野で特に有用です。

Citations:
[1] https://zenn.dev/zahn/articles/6a3d2138e9fe68
[2] https://matsu-chara.hatenablog.com/entry/2017/04/01/110000
[3] https://matsu-chara.hatenablog.com/entry/2022/06/25/110000
[4] https://zenn.dev/sbk0716/articles/fddc9b132ef7aa
[5] https://speakerdeck.com/f440/implementing-command-history-and-temporal-access

Bitemporal Data Model: 入出金取引管理システムの例

はじめに

1. 必要なテーブル構造

1.1 入出金取引テーブル

1.2 銀行口座テーブル

2. 入出金取引テーブルの設計詳細

2.1 送金元と送金先の関連付け

2.2 取引種類と金額の記録方法

2.3 時間関連カラムの意味と更新タイミング

3. 具体的なシナリオ

シナリオ1: 新規取引の作成

シナリオ2: 取引の修正（金額の訂正）

シナリオ3: 取引のキャンセル

シナリオ4: 過去の時点での取引状態の確認

シナリオ5: 取引履歴の追跡

4. Bitemporal Data Modelの利点

5. 実際のビジネスシーンでの応用例

[Perplexity][Bitemporal Data Model]入出金取引管理の実装について

シナリオ1: 新規取引の登録

シナリオ2: 取引金額の修正

シナリオ3: 取引の取り消し

シナリオ4: 新規入金取引の登録

シナリオ5: 過去の時点での取引状況の確認

Discussion