👋

[Claude]テンポラルデータモデル入門(人事システムでの実装例)

2024/12/01に公開

PostgreSQL

Claude

bitemporaldatamode

tech

テンポラルデータモデル入門(人事システムでの実装例)

0. はじめに

データベースで時間の経過に伴う変更を正確に記録・管理したい場合、テンポラルデータモデルが有効です。この記事では、人事システムを例に、実践的な実装方法を解説します。

※この記事は Claude 3.5 Sonnet を用いて生成しています。

1. テンポラルデータモデルの基礎

1.1 テンポラルデータモデルとは

テンポラルデータモデルは、データの時間的な変化を管理するためのデータモデルです。以下の2つの時間軸を使用して、データの履歴を正確に記録します：

時間軸	データベースでの列名	意味	例
業務時間（Valid Time）	effective	実際の業務上で事実が有効な期間	4月1日付けの人事異動
システム時間（Transaction Time）	assertive	システム上でその記録が有効な期間	3月15日にシステムに登録

列名の英語的について

effective（形容詞）

意味：「効力のある」「有効な」「実際の」
使用理由：業務上で実際に効力を持つ期間を表すのに適切
一般的な用例：
- effective date（発効日）
- effective immediately（即時有効）
- effective period（有効期間）

assertive（形容詞）

意味：「主張する」「断言する」
使用理由：システムが「この記録は有効である」と主張する期間を表すのに適切
一般的な用例：
- assertive statement（断定的な声明）
- assertive record（確定記録）
- assertive period（主張期間）

@startuml
title テンポラルデータモデルの2つの時間軸

skinparam backgroundColor white

rectangle "業務時間（Valid Time）" {
  [過去] #lightblue
  [現在] #lightblue
  [未来] #lightblue
}

rectangle "システム時間（Transaction Time）" {
  [過去の記録] #lightgreen
  [現在の記録] #lightgreen
}

note bottom of "業務時間（Valid Time）"
  実際の業務上の出来事の時間
  例：4月1日付けの人事異動
end note

note bottom of "システム時間（Transaction Time）"
  システムへの記録時間
  例：3月15日にシステムに登録
end note

@enduml

1.2 モデルの分類と採用傾向

テンポラルデータモデルは、これら2つの時間軸の使用方法によって4つのモデルに分類されます：

@startuml
title システム開発におけるテンポラルデータモデルの採用傾向

rectangle "一般的な業務システム" {
    rectangle "Transaction Time Model\n\n・CREATE/UPDATE/DELETE日時の記録\n・論理削除による履歴管理\n・監査ログの保持" as TTM #lightgreen
}

rectangle "特殊用途システム" {
    rectangle "Snapshot Model\n\n・キャッシュデータ\n・一時的なデータ\n・リアルタイムの状態" as SM #lightgrey
    
    rectangle "Valid Time Model\n\n・カレンダー管理\n・スケジュール管理\n・期間限定の設定値" as VM #lightblue
    
    rectangle "BiTemporal Model\n\n・金融システム\n・人事システム\n・契約管理システム" as BM #pink
}

note bottom of TTM
  最も一般的な実装パターン
  - 標準的なDBMSの機能で実現可能
  - 開発者にとって理解しやすい
  - 基本的な監査要件を満たせる
end note

@enduml

1.3 各モデルの特徴と使い分け

モデル	時間管理	主な用途	具体例	採用傾向
Snapshot Model	なし	一時的なデータ管理	- キャッシュデータ - リアルタイムの状態表示 - 現在の在庫数 - 現在の気温	特殊用途（一時的なデータ管理が目的の場合）
Valid Time Model	業務時間のみ	期間管理が必要なデータ	- カレンダー管理 - スケジュール管理 - 契約期間 - 商品の有効期限 - 期間限定の設定値	特殊用途（期間管理が主目的の場合）
Transaction Time Model	システム時間のみ	変更履歴の管理	- CREATE/UPDATE/DELETE日時の記録 - 論理削除による履歴管理 - 監査ログの保持 - データ更新履歴	最も一般的（標準的な業務システム）
BiTemporal Model	両方	完全な履歴管理	- 人事記録 - 金融取引履歴 - 契約管理システム - 保険システム	特殊用途（厳密な履歴管理が必要な場合）

1.4 実際のユースケース例

人事システムでの例を使って、各モデルの違いを説明します：

@startuml
title 人事異動の記録例

skinparam backgroundColor white

rectangle "Snapshot Model" as SM {
    note right: "山田太郎は営業部に所属"
}

rectangle "Valid Time Model" as VT {
    note right: "山田太郎は\n2024/4/1-2024/6/30：開発部\n2024/7/1-現在：営業部"
}

rectangle "Transaction Time Model" as TT {
    note right: "2024/3/15に開発部配属を記録\n2024/6/15に営業部異動を記録"
}

rectangle "BiTemporal Model" as BT {
    note right: "2024/3/15：4/1付で開発部配属を記録\n2024/6/15：7/1付で営業部異動を記録"
}

@enduml

2. データベース設計と実装

2.1 基本テーブル構造

人事システムの主要なテーブルについて、BiTemporalモデルを適用した設計を説明します。

@startuml
!define table(x) class x << (T,#FFAAAA) >>
!define primary_key(x) <u>x</u>
!define foreign_key(x) #x#

title 人事システムのテーブル構造

' マスタテーブル
table(departments) {
    primary_key(department_id): varchar(10)
    department_name: varchar(50)
    created_at: timestamp with time zone
    updated_at: timestamp with time zone
}

table(positions) {
    primary_key(position_id): varchar(10)
    position_name: varchar(50)
    min_salary: numeric(10,0)
    max_salary: numeric(10,0)
    rank: integer
    created_at: timestamp with time zone
    updated_at: timestamp with time zone
}

' トランザクションテーブル
table(employees) {
    primary_key(record_id): bigint
    foreign_key(employee_id): varchar(10)
    first_name: varchar(50)
    last_name: varchar(50)
    email: varchar(100)
    hire_date: date
    effective: tstzrange
    assertive: tstzrange
    created_at: timestamp with time zone
    updated_at: timestamp with time zone
}

table(department_assignments) {
    primary_key(record_id): bigint
    foreign_key(employee_id): varchar(10)
    foreign_key(department_id): varchar(10)
    foreign_key(position_id): varchar(10)
    effective: tstzrange
    assertive: tstzrange
    created_at: timestamp with time zone
    updated_at: timestamp with time zone
}

table(salaries) {
    primary_key(record_id): bigint
    foreign_key(employee_id): varchar(10)
    amount: numeric(10,0)
    salary_type: varchar(20)
    effective: tstzrange
    assertive: tstzrange
    created_at: timestamp with time zone
    updated_at: timestamp with time zone
}

' リレーションシップ
employees "1" -- "n" department_assignments
employees "1" -- "n" salaries
department_assignments "n" -- "1" departments
department_assignments "n" -- "1" positions

@enduml

2.2 テーブル定義

2.2.1 マスタテーブル

-- 部署マスタ
CREATE TABLE departments (
    -- 部署を識別するID（例：'D001'）
    department_id VARCHAR(10) NOT NULL PRIMARY KEY,
    
    -- 部署名
    department_name VARCHAR(50) NOT NULL,
    
    -- 監査証跡用のカラム
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 役職マスタ
CREATE TABLE positions (
    -- 役職を識別するID（例：'P001'）
    position_id VARCHAR(10) NOT NULL PRIMARY KEY,
    
    -- 役職名
    position_name VARCHAR(50) NOT NULL,
    
    -- 給与範囲
    min_salary NUMERIC(10,0) NOT NULL,
    max_salary NUMERIC(10,0) NOT NULL,
    
    -- 役職順位（昇進時のチェックに使用）
    rank INTEGER NOT NULL,
    
    -- 監査証跡用のカラム
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- 給与範囲の整合性チェック
    CONSTRAINT valid_salary_range 
        CHECK (min_salary < max_salary)
);

-- マスタデータの初期登録
INSERT INTO departments (department_id, department_name) VALUES
    ('D001', '開発部'),
    ('D002', '営業部'),
    ('D003', '営業1部'),
    ('D004', '営業2部'),
    ('D005', '営業企画部');

INSERT INTO positions (
    position_id, 
    position_name, 
    min_salary, 
    max_salary, 
    rank
) VALUES
    ('P001', '社員', 250000, 300000, 1),
    ('P002', '主任', 300000, 400000, 2),
    ('P003', '課長', 400000, 500000, 3);

2.2.2 従業員基本情報（employees）

-- 従業員の基本情報を管理するテーブル
CREATE TABLE employees (
    -- レコードを一意に識別するID（自動採番）
    record_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    
    -- 従業員を識別するID（例：'E001'）
    employee_id VARCHAR(10) NOT NULL,
    
    -- 従業員の氏名（姓名別管理）
    first_name VARCHAR(50) NOT NULL,  -- 名
    last_name VARCHAR(50) NOT NULL,   -- 姓
    
    -- 業務連絡用メールアドレス
    email VARCHAR(100) NOT NULL,
    
    -- 入社日（YYYY-MM-DD形式）
    hire_date DATE NOT NULL,
    
    -- BiTemporal管理用のカラム
    -- 業務上の有効期間（例：[2024-04-01, infinity)）
    effective TSTZRANGE NOT NULL,
    -- システム上の有効期間（例：[2024-03-15, infinity)）
    assertive TSTZRANGE NOT NULL,
    
    -- 監査証跡用のカラム
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- 制約
    CONSTRAINT valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    CONSTRAINT no_overlapping_employee_periods 
        EXCLUDE USING gist (
            employee_id WITH =,
            effective WITH &&,
            assertive WITH &&
        )
);

-- インデックス
CREATE INDEX idx_employees_employee_id 
    ON employees (employee_id);
CREATE INDEX idx_employees_effective 
    ON employees USING gist (effective);
CREATE INDEX idx_employees_assertive 
    ON employees USING gist (assertive);
CREATE INDEX idx_employees_effective_assertive 
    ON employees USING gist (effective, assertive);
CREATE INDEX idx_employees_email 
    ON employees (email);
CREATE INDEX idx_employees_hire_date 
    ON employees (hire_date);

2.2.3 部署所属情報（department_assignments）

-- 従業員の部署所属情報を管理するテーブル
CREATE TABLE department_assignments (
    -- レコードを一意に識別するID（自動採番）
    record_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    
    -- 従業員を識別するID
    employee_id VARCHAR(10) NOT NULL,
    
    -- 部署を識別するID
    department_id VARCHAR(10) NOT NULL,
    
    -- 役職を識別するID
    position_id VARCHAR(10) NOT NULL,
    
    -- BiTemporal管理用のカラム
    -- 業務上の有効期間
    effective TSTZRANGE NOT NULL,
    -- システム上の有効期間
    assertive TSTZRANGE NOT NULL,
    
    -- 監査証跡用のカラム
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
        
    -- 期間重複の排他制約
    CONSTRAINT no_overlapping_assignment_periods 
        EXCLUDE USING gist (
            employee_id WITH =,
            effective WITH &&,
            assertive WITH &&
        )
);

-- インデックス
CREATE INDEX idx_assignments_employee_id 
    ON department_assignments (employee_id);
CREATE INDEX idx_assignments_effective 
    ON department_assignments USING gist (effective);
CREATE INDEX idx_assignments_assertive 
    ON department_assignments USING gist (assertive);
CREATE INDEX idx_assignments_effective_assertive 
    ON department_assignments USING gist (effective, assertive);
CREATE INDEX idx_assignments_department 
    ON department_assignments (department_id);
CREATE INDEX idx_assignments_position 
    ON department_assignments (position_id);
CREATE INDEX idx_assignments_employee_effective 
    ON department_assignments (employee_id, department_id, lower(effective));

2.2.4 給与情報（salaries）

-- 従業員の給与情報を管理するテーブル
CREATE TABLE salaries (
    -- レコードを一意に識別するID（自動採番）
    record_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    
    -- 従業員を識別するID
    employee_id VARCHAR(10) NOT NULL,
    
    -- 給与額
    amount NUMERIC(10, 0) NOT NULL,
    
    -- 給与種別（例：月給、年俸）
    salary_type VARCHAR(20) NOT NULL,
    
    -- BiTemporal管理用のカラム
    -- 業務上の有効期間
    effective TSTZRANGE NOT NULL,
    -- システム上の有効期間
    assertive TSTZRANGE NOT NULL,
    
    -- 監査証跡用のカラム
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- 制約
    CONSTRAINT valid_amount 
        CHECK (amount >= 0),
    CONSTRAINT valid_salary_type 
        CHECK (salary_type IN ('MONTHLY', 'YEARLY')),
    CONSTRAINT no_overlapping_salary_periods 
        EXCLUDE USING gist (
            employee_id WITH =,
            effective WITH &&,
            assertive WITH &&
        )
);

-- インデックス
CREATE INDEX idx_salaries_employee_id 
    ON salaries (employee_id);
CREATE INDEX idx_salaries_effective 
    ON salaries USING gist (effective);
CREATE INDEX idx_salaries_assertive 
    ON salaries USING gist (assertive);
CREATE INDEX idx_salaries_effective_assertive 
    ON salaries USING gist (effective, assertive);
CREATE INDEX idx_salaries_amount 
    ON salaries (amount);
CREATE INDEX idx_salaries_employee_effective 
    ON salaries (employee_id, lower(effective));

-- 監査証跡用の関数
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 監査証跡用のトリガー
CREATE TRIGGER update_departments_timestamp
    BEFORE UPDATE ON departments
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_positions_timestamp
    BEFORE UPDATE ON positions
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_employees_timestamp
    BEFORE UPDATE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_department_assignments_timestamp
    BEFORE UPDATE ON department_assignments
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_salaries_timestamp
    BEFORE UPDATE ON salaries
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

-- マスタデータの参照整合性チェック用の関数
CREATE OR REPLACE FUNCTION check_master_data_exists()
RETURNS TRIGGER AS $$
BEGIN
    -- 部署の存在チェック
    IF NOT EXISTS (
        SELECT 1 
        FROM departments 
        WHERE department_id = NEW.department_id
    ) THEN
        RAISE EXCEPTION 'Invalid department_id: %', NEW.department_id;
    END IF;

    -- 役職の存在チェック
    IF NOT EXISTS (
        SELECT 1 
        FROM positions 
        WHERE position_id = NEW.position_id
    ) THEN
        RAISE EXCEPTION 'Invalid position_id: %', NEW.position_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- マスタデータチェック用のトリガー
CREATE TRIGGER check_master_data_trigger
    BEFORE INSERT OR UPDATE ON department_assignments
    FOR EACH ROW
    EXECUTE FUNCTION check_master_data_exists();

2.3 時間範囲型（tstzrange）の説明

PostgreSQLの時間範囲型tstzrangeは、タイムゾーン付きのタイムスタンプ範囲を表現します。

/*
tstzrangeの書式：
tstzrange(開始時刻, 終了時刻, '境界指定')

境界指定の種類：
- '[)' = 開始を含み、終了を含まない（デフォルト）
- '[]' = 開始も終了も含む
- '(]' = 開始を含まず、終了を含む
- '()' = 開始も終了も含まない
*/

-- 例1: 2024年4月1日から無期限
tstzrange('2024-04-01 00:00:00+09', 'infinity')
-- 結果：[2024-04-01 00:00:00+09, infinity)

-- 例2: 2024年4月1日から2024年7月1日まで
tstzrange('2024-04-01 00:00:00+09', '2024-07-01 00:00:00+09')
-- 結果：[2024-04-01 00:00:00+09, 2024-07-01 00:00:00+09)

-- 例3: 範囲の演算子
SELECT tstzrange('2024-04-01', '2024-07-01') @> '2024-05-01'::timestamp;
-- 結果：true（範囲に含まれる）

SELECT tstzrange('2024-04-01', '2024-07-01') && 
       tstzrange('2024-06-01', '2024-09-01');
-- 結果：true（範囲が重なる）

2.4 重要な設計ポイント

自動採番の使用
- record_idはGENERATED ALWAYS AS IDENTITYで自動採番
- 一意性の保証と管理の簡素化
排他制約
- GiSTインデックスを使用した期間重複の防止
- 業務上の一意性を持つべき項目の組み合わせに対して、期間重複を防止
複合インデックス
- 頻繁に使用される検索パターンに対応
- パフォーマンスの最適化
監査証跡
- 全テーブルにcreated_atとupdated_atを設定
- データの変更履歴を追跡可能
チェック制約
- ビジネスルールの強制
- データの整合性確保
外部キー制約の制限
- BiTemporalモデルでは通常の外部キー制約の実装が困難
- 主な理由：
  - 時間軸による有効期間の管理があり、単純な外部キー参照では期間の整合性を表現できない
  - 同一IDでも時期によって異なるレコードが存在するため、一対一の参照関係が成立しない
  - 履歴データの保持が必要なため、通常の削除制御（CASCADE/RESTRICT）が適切に機能しない
- 代替手段：
  - トリガーやアプリケーションロジックでの整合性チェック
  - 参照整合性を時間軸も考慮して検証する独自の制約の実装

3. CRUD操作の実装

3.1 新規登録（Create）操作

新入社員の登録を例に、BiTemporalモデルでのCreate操作を説明します。

@startuml
title 新入社員登録の時系列

rectangle "業務時間" as Business #lightblue
rectangle "システム時間" as System #lightgreen

note bottom of Business
  入社: 2024/4/1 → 継続中
  実際の入社日からデータが有効
end note

note bottom of System
  登録: 2024/3/15 → 継続中
  事前登録時からシステムに記録
end note

@enduml

@startuml
title 新入社員の登録のシーケンス図

actor User
participant "Application" as App
database "Database" as DB

User -> App: 新入社員登録リクエスト (2024-03-15)
App -> DB: INSERT INTO employees
note right of App
  - effective: [2024-04-01, infinity)
    理由: 実際の入社日から業務上有効
  - assertive: [2024-03-15, infinity)
    理由: システムへの登録日から記録開始
end note
DB --> App: 登録成功 (2024-03-15)

App -> DB: INSERT INTO department_assignments
note right of App
  - effective: [2024-04-01, infinity)
    理由: 実際の入社日から業務上有効
  - assertive: [2024-03-15, infinity)
    理由: システムへの登録日から記録開始
end note
DB --> App: 登録成功 (2024-03-15)

App -> DB: INSERT INTO salaries
note right of App
  - effective: [2024-04-01, infinity)
    理由: 実際の入社日から業務上有効
  - assertive: [2024-03-15, infinity)
    理由: システムへの登録日から記録開始
end note
DB --> App: 登録成功 (2024-03-15)

App -> User: 登録完了メッセージ (2024-03-15)

note right of DB
  時系列:
  - 2024-03-15: employeesテーブルに登録
  - 2024-03-15: department_assignmentsテーブルに登録
  - 2024-03-15: salariesテーブルに登録
end note

@enduml

処理前のテーブル状態:

-- employeesテーブルは空の状態
SELECT * FROM employees;
-- 結果：レコードなし

-- トランザクション開始
-- 目的：全ての登録処理を一つの単位として実行
BEGIN;

-- 1. 従業員基本情報の登録
INSERT INTO employees (
    record_id,
    employee_id,
    first_name,
    last_name,
    email,
    hire_date,
    -- 業務時間：入社日から無期限
    -- 実際の業務上の有効期間を設定
    effective,
    -- システム時間：登録日から無期限
    -- システムでの記録期間を設定
    assertive,
    created_at,
    updated_at
) VALUES (
    1,                -- レコードID（自動採番）
    'E001',          -- 従業員ID
    '太郎',          -- 名
    '山田',          -- 姓
    'taro.yamada@example.com',  -- メールアドレス
    '2024-04-01',    -- 入社日
    -- 4月1日の入社日から無期限で有効
    tstzrange('2024-04-01', 'infinity'),
    -- システムへの登録日（3月15日）から無期限で有効
    tstzrange('2024-03-15', 'infinity'),
    CURRENT_TIMESTAMP,     -- 作成日時
    CURRENT_TIMESTAMP      -- 更新日時
);

-- 2. 部署所属情報の登録
INSERT INTO department_assignments (
    record_id,
    employee_id,
    department_id,
    position_id,
    effective,        -- 業務上の有効期間
    assertive,        -- システム上の有効期間
    created_at,
    updated_at
) VALUES (
    1,                -- レコードID（自動採番）
    'E001',          -- 従業員ID
    'D001',          -- 開発部
    'P001',          -- 一般社員
    -- 入社日から無期限で有効
    tstzrange('2024-04-01', 'infinity'),
    -- システム登録日から無期限で有効
    tstzrange('2024-03-15', 'infinity'),
    CURRENT_TIMESTAMP,     -- 作成日時
    CURRENT_TIMESTAMP      -- 更新日時
);

-- トランザクションの確定
-- 全ての処理が成功した場合のみコミット
COMMIT;

処理後のテーブル状態:

-- 従業員テーブルの状態確認
SELECT * FROM employees WHERE employee_id = 'E001';

record_id	employee_id	first_name	last_name	email	hire_date	effective	assertive	created_at	updated_at
1	E001	太郎	山田	taro.yamada@example.com	2024-04-01	[2024-04-01, infinity)	[2024-03-15, infinity)	2024-03-15 10:00:00	2024-03-15 10:00:00

-- 部署所属情報の状態確認
SELECT * FROM department_assignments WHERE employee_id = 'E001';

record_id	employee_id	department_id	position_id	effective	assertive	created_at	updated_at
1	E001	D001	P001	[2024-04-01, infinity)	[2024-03-15, infinity)	2024-03-15 10:00:00	2024-03-15 10:00:00

3.2 参照（Read）操作

データの参照パターンには以下の3つがあります：

@startuml
title データ参照パターン

rectangle "参照パターン" {
    rectangle "現在の状態参照" as Current #lightblue
    rectangle "特定時点の状態参照" as Point #lightgreen
    rectangle "期間内の履歴参照" as History #pink
}

note right of Current: "現時点で有効なデータ"
note right of Point: "指定日時での状態"
note right of History: "期間内の変更履歴"

@enduml

@startuml
title 特定時点の状態参照のシーケンス図

actor User
participant "Application" as App
database "Database" as DB

User -> App: 特定時点の状態参照リクエスト (2024-06-20)
App -> DB: SELECT * FROM employees, department_assignments, salaries
note right of App
  - effective: [2024-07-01, infinity)
    理由: 7月1日付けの異動が事前登録されており、
    7月1日から業務上有効
  - assertive: [2024-06-15, infinity)
    理由: 6月15日にシステムに事前登録され、
    現在（6月20日）もシステム上有効
end note
DB --> App: データ取得成功 (2024-06-20)
App -> User: データ返却 (2024-06-20)

note right of DB
  時系列:
  - 2024-06-15: 異動情報が事前登録
  - 2024-06-20: 7月1日時点の状態を参照
  - まだ6月だが、7月1日からの異動情報も取得
end note

@enduml

3.2.1 現在の状態参照

-- 現在有効な従業員情報の取得
SELECT 
    e.*,
    d.department_name,
    p.position_name
FROM 
    employees e
    -- 部署所属情報とマスタの結合
    JOIN department_assignments da ON e.employee_id = da.employee_id
    JOIN departments d ON da.department_id = d.department_id
    JOIN positions p ON da.position_id = p.position_id
WHERE 
    -- @> は「範囲に含まれる」ことを表すPostgreSQLの演算子です
    -- 業務時間で現在有効なレコードを抽出
    e.effective @> CURRENT_DATE
    AND da.effective @> CURRENT_DATE
    -- システム時間で最新のレコードを抽出
    AND e.assertive @> CURRENT_TIMESTAMP
    AND da.assertive @> CURRENT_TIMESTAMP;

/*
具体例：
- effective: [2024-04-01, infinity)
  → 2024年4月1日から無期限で業務上有効
- assertive: [2024-03-15, infinity)
  → 2024年3月15日のシステム登録から現在も有効

CURRENT_DATEが2024-04-15の場合：
- effective @> '2024-04-15' は true （業務上有効）
- assertive @> '2024-04-15' も true （システム上有効）
となり、このレコードが検索結果として取得されます
*/

3.2.2 特定時点の状態参照

-- 2024年7月1日時点での従業員の状態を参照
SELECT 
    e.employee_id,
    e.last_name || ' ' || e.first_name as full_name,
    d.department_name,
    p.position_name,
    s.amount as salary
FROM 
    employees e
    JOIN department_assignments da ON e.employee_id = da.employee_id
    JOIN departments d ON da.department_id = d.department_id
    JOIN positions p ON da.position_id = p.position_id
    JOIN salaries s ON e.employee_id = s.employee_id
WHERE 
    -- 指定した日時（2024-07-01）が各テーブルの業務上の有効期間に含まれるか確認
    e.effective @> '2024-07-01'::date
    AND da.effective @> '2024-07-01'::date
    AND s.effective @> '2024-07-01'::date
    -- システム上の最新データのみを参照
    AND e.assertive @> CURRENT_TIMESTAMP
    AND da.assertive @> CURRENT_TIMESTAMP
    AND s.assertive @> CURRENT_TIMESTAMP;

/*
例えば以下のようなケースを考えます：

1. 7月1日付けの部署異動が6月15日に事前登録された場合
   - effective: [2024-07-01, infinity)
   - assertive: [2024-06-15, infinity)

2. 検索時点が6月20日の場合
   - 7月1日時点の状態を確認したい
   - まだ6月だが、7月1日からの異動情報も取得できる

このように、業務上の有効日（effective）と
システムでの記録日（assertive）を分けて管理することで、
将来の予定も含めた正確な状態参照が可能になります
*/

3.2.3 履歴の参照

-- 従業員の部署異動履歴を時系列で取得
SELECT 
    da.employee_id,
    e.last_name || ' ' || e.first_name as full_name,
    d.department_name,
    -- 業務上の有効期間（実際の所属期間）
    lower(da.effective) as valid_from,    -- 開始日
    CASE 
        WHEN upper(da.effective) = 'infinity' 
        THEN '現在も所属中'
        ELSE upper(da.effective)::text    -- 終了日
    END as valid_until,
    -- システム上の記録期間（いつ記録されたか）
    lower(da.assertive) as recorded_at    -- 記録日
FROM 
    department_assignments da
    JOIN employees e ON da.employee_id = e.employee_id
    JOIN departments d ON da.department_id = d.department_id
WHERE 
    -- 特定の従業員の履歴を取得
    da.employee_id = 'E001'
ORDER BY 
    -- 業務上の開始日でソート
    lower(da.effective);

/*
結果の例：
employee_id | full_name | department_name | valid_from  | valid_until | recorded_at
-----------+-----------+----------------+-------------+-------------+-------------
E001       | 山田 太郎 | 開発部         | 2024-04-01  | 2024-06-30  | 2024-03-15
E001       | 山田 太郎 | 営業部         | 2024-07-01  | 現在も所属中 | 2024-06-15

この結果から以下が分かります：
1. 開発部での所属期間：2024-04-01 ～ 2024-06-30
   - 3月15日に事前登録された
2. 営業部での所属期間：2024-07-01 ～ 現在
   - 6月15日に事前登録された
*/

3.3 更新（Update）操作

部署異動を例に、BiTemporalモデルでの更新操作を説明します。

@startuml
title 部署異動の時系列と状態変化

rectangle "業務時間" as Business {
    rectangle "開発部所属期間\n2024/4/1 → 2024/7/1" as Dev #lightblue
    rectangle "営業部所属期間\n2024/7/1 → 継続中" as Sales #lightgreen
}

rectangle "システム時間" as System {
    rectangle "開発部記録期間\n2024/3/15 → 継続中" as DevRecord #lightblue
    rectangle "異動記録期間\n2024/6/15 → 継続中" as SalesRecord #lightgreen
}

note bottom of System
  2024/6/15: 異動情報の事前登録
  2024/7/1: 実際の異動日
end note

@enduml

@startuml
title 部署異動のシーケンス図

actor User
participant "Application" as App
database "Database" as DB

User -> App: 部署異動リクエスト (2024-06-15)
App -> DB: UPDATE department_assignments
note right of App
  - effective: [2024-04-01, 2024-07-01)
    理由: 異動日前日で業務上の期間を終了
  - assertive: [2024-06-15, infinity)
    理由: システム上の記録を更新日から開始
end note
DB --> App: 更新成功 (2024-06-15)

App -> DB: INSERT INTO department_assignments
note right of App
  - effective: [2024-07-01, infinity)
    理由: 異動日から新しい業務上の期間を開始
  - assertive: [2024-06-15, infinity)
    理由: システム上の記録を更新日から開始
end note
DB --> App: 登録成功 (2024-06-15)

App -> User: 更新完了メッセージ (2024-06-15)

note right of DB
  時系列:
  - 2024-06-15: 異動情報を更新
end note

@enduml

処理前のテーブル状態:

-- 部署所属情報の現在の状態確認
SELECT 
    da.*,
    d.department_name,
    p.position_name
FROM department_assignments da
JOIN departments d ON da.department_id = d.department_id
JOIN positions p ON da.position_id = p.position_id
WHERE employee_id = 'E001';

record_id	employee_id	department_id	position_id	effective	assertive	department_name	position_name
1	E001	D001	P001	[2024-04-01, infinity)	[2024-03-15, infinity)	開発部	一般社員

-- トランザクション開始
-- 目的：異動に関する全ての処理を一つの単位として実行
BEGIN;

-- 1. 現在の部署所属情報の終了日時を設定
UPDATE department_assignments
SET 
    -- 業務時間の終了日を設定（異動日の前日まで）
    -- 現在の開始日はそのままに、終了日を異動日に設定
    effective = tstzrange(
        lower(effective),       -- 現在の開始日を維持
        '2024-07-01'           -- 異動日の00:00:00で終了
    ),
    -- 更新日時を記録
    updated_at = CURRENT_TIMESTAMP
WHERE 
    employee_id = 'E001'
    -- 現在有効なレコードを特定
    AND upper(effective) = 'infinity'    -- 業務上の終了日が設定されていない
    AND upper(assertive) = 'infinity';   -- システム上の終了日が設定されていない

-- 2. 新しい部署所属情報を登録
INSERT INTO department_assignments (
    record_id,
    employee_id,
    department_id,    -- 異動先部署ID
    position_id,      -- 役職ID（変更なし）
    effective,        -- 業務上の有効期間
    assertive,        -- システム上の有効期間
    created_at,
    updated_at
) VALUES (
    2,                -- レコードID（自動採番）
    'E001',          -- 従業員ID
    'D002',          -- 営業部
    'P001',          -- 一般社員（変更なし）
    -- 異動日から無期限で有効
    tstzrange('2024-07-01', 'infinity'),
    -- システム登録日から無期限で有効
    tstzrange('2024-06-15', 'infinity'),
    CURRENT_TIMESTAMP,     -- 作成日時
    CURRENT_TIMESTAMP      -- 更新日時
);

-- トランザクションの確定
COMMIT;

/*
重要なポイント：
1. 既存レコードの論理削除ではなく、effective期間の終了日を設定
2. 新しい状態は新規レコードとして追加
3. トランザクションで両方の処理をまとめて実行
4. assertiveは新規登録時の日時から開始
*/

処理後のテーブル状態:

-- 部署所属情報の履歴確認
SELECT 
    da.*,
    d.department_name,
    p.position_name
FROM department_assignments da
JOIN departments d ON da.department_id = d.department_id
JOIN positions p ON da.position_id = p.position_id
WHERE da.employee_id = 'E001'
ORDER BY lower(da.effective);

record_id	employee_id	department_id	position_id	effective	assertive	department_name	position_name
1	E001	D001	P001	[2024-04-01, 2024-07-01)	[2024-03-15, infinity)	開発部	一般社員
2	E001	D002	P001	[2024-07-01, infinity)	[2024-06-15, infinity)	営業部	一般社員

3.4 論理削除（Logical Delete）操作

BiTemporalモデルでは、物理削除ではなく論理削除を使用します。これは、assertive（システム時間）の終了日時を設定することで実現します。

@startuml
title 論理削除の仕組み

skinparam backgroundColor white

rectangle "論理削除前" {
    rectangle "有効なレコード\nassertive: [2024-03-15, infinity)" as Active #lightgreen
}

rectangle "論理削除後" {
    rectangle "無効化されたレコード\nassertive: [2024-03-15, 2024-06-30)" as Inactive #pink
}

Active --> Inactive : 論理削除

note bottom of Inactive: assertiveの終了日時を設定することで\n論理削除を実現

@enduml

@startuml
title 論理削除のシーケンス図

actor User
participant "Application" as App
database "Database" as DB

User -> App: データ削除リクエスト (2024-06-30)
App -> DB: UPDATE department_assignments
note right of App
  - assertive: [2024-03-15, 2024-06-30)
    理由: システム上の有効期間を削除日で終了
end note
DB --> App: 更新成功 (2024-06-30)

App -> User: 削除完了メッセージ (2024-06-30)

note right of DB
  時系列:
  - 2024-06-30: データを論理削除
end note

@enduml

処理前のテーブル状態:

-- 部署所属情報の現在の状態確認
SELECT 
    da.*,
    d.department_name,
    p.position_name
FROM department_assignments da
JOIN departments d ON da.department_id = d.department_id
JOIN positions p ON da.position_id = p.position_id
WHERE da.employee_id = 'E001'
AND da.assertive @> CURRENT_TIMESTAMP;

record_id	employee_id	department_id	position_id	effective	assertive	department_name	position_name
2	E001	D002	P001	[2024-07-01, infinity)	[2024-06-15, infinity)	営業部	一般社員

-- 論理削除の実装例
-- 目的：誤って登録された情報を無効化する

-- トランザクション開始
BEGIN;

-- 論理削除の実行
UPDATE department_assignments
SET 
    -- システム時間の終了日時を設定
    -- これにより、このレコードは指定した時点で無効になる
    assertive = tstzrange(
        lower(assertive),        -- 現在の開始日を維持
        CURRENT_TIMESTAMP        -- 現在時刻で終了
    ),
    -- 更新日時を記録
    updated_at = CURRENT_TIMESTAMP
WHERE 
    employee_id = 'E001'
    -- 現在有効なレコードを特定
    AND upper(assertive) = 'infinity';

/*
重要なポイント：
1. effectiveではなくassertiveを更新
   - 業務上の期間（effective）はそのまま
   - システム上の期間（assertive）に終了日時を設定

2. 物理削除（DELETEコマンド）は使用しない
   - データの履歴を完全に保持
   - 後から参照可能

3. 現在時刻（CURRENT_TIMESTAMP）で無効化
   - 無効になるタイミングを明確に記録
*/

-- トランザクションの確定
COMMIT;

処理後のテーブル状態:

-- 論理削除後の状態確認（全履歴を表示）
SELECT 
    da.*,
    d.department_name,
    p.position_name,
    -- assertiveの状態を分かりやすく表示
    CASE 
        WHEN upper(da.assertive) = 'infinity' 
        THEN 'アクティブ'
        ELSE '無効化済み'
    END as record_status
FROM department_assignments da
JOIN departments d ON da.department_id = d.department_id
JOIN positions p ON da.position_id = p.position_id
WHERE da.employee_id = 'E001'
ORDER BY lower(da.effective);

record_id	employee_id	department_id	position_id	effective	assertive	department_name	position_name	record_status
1	E001	D001	P001	[2024-04-01, 2024-07-01)	[2024-03-15, infinity)	開発部	一般社員	アクティブ
2	E001	D002	P001	[2024-07-01, infinity)	[2024-06-15, 2024-06-30)	営業部	一般社員	無効化済み

-- 現在有効なレコードのみを確認
SELECT 
    da.*,
    d.department_name,
    p.position_name
FROM department_assignments da
JOIN departments d ON da.department_id = d.department_id
JOIN positions p ON da.position_id = p.position_id
WHERE da.employee_id = 'E001'
AND da.assertive @> CURRENT_TIMESTAMP;

record_id	employee_id	department_id	position_id	effective	assertive	department_name	position_name
1	E001	D001	P001	[2024-04-01, 2024-07-01)	[2024-03-15, infinity)	開発部	一般社員

3.5 データ整合性の確保

BiTemporalモデルでは、以下の3つの整合性を確保することが重要です：

期間の重複防止
期間の連続性確保
トランザクションの一貫性

3.5.1 期間の重複防止

-- 期間重複チェック用の制約
-- 目的：同一従業員の同一期間での重複を防ぐ
ALTER TABLE department_assignments
    ADD CONSTRAINT no_overlapping_periods
    EXCLUDE USING gist (
        employee_id WITH =,      -- 同一従業員で
        effective WITH &&,       -- 業務上の期間が重なり
        assertive WITH &&        -- システム上の期間が重なる
    );                          -- レコードを排除

/*
例：以下のような重複データは登録できない

既存レコード：
employee_id: E001
effective: [2024-04-01, 2024-07-01)
assertive: [2024-03-15, infinity)

重複エラーとなる例：
INSERT INTO department_assignments VALUES (
    'E001',                     -- 同じ従業員
    [2024-06-01, 2024-08-01),  -- 期間が重複
    [2024-03-15, infinity)      -- システム期間も重複
);
*/

3.5.2 期間の連続性確保

-- 履歴の連続性チェック用のビュー
-- 目的：業務上の期間（effective）に隙間や重複がないことを確認
CREATE VIEW check_assignment_continuity AS
WITH period_check AS (
    SELECT 
        employee_id,
        -- 期間の開始日
        lower(effective) as period_start,
        -- 期間の終了日
        upper(effective) as period_end,
        -- 次の期間の開始日を取得
        LEAD(lower(effective)) OVER (
            PARTITION BY employee_id 
            ORDER BY lower(effective)
        ) as next_period_start
    FROM department_assignments
    -- 現在システム上有効なレコードのみを対象
    WHERE upper(assertive) = 'infinity'
)
SELECT 
    employee_id,
    period_start,
    period_end,
    next_period_start,
    -- 期間の隙間や重複をチェック
    CASE 
        WHEN next_period_start IS NULL THEN 'OK（最終レコード）'
        WHEN next_period_start = period_end THEN 'OK'
        WHEN next_period_start > period_end THEN 
            '警告：期間に隙間があります'
        ELSE 'エラー：期間が重複しています'
    END as status
FROM period_check;

/*
チェック結果の例：

employee_id | period_start | period_end  | next_period_start | status
-----------+-------------+-------------+------------------+--------
E001       | 2024-04-01  | 2024-07-01 | 2024-07-01      | OK
E001       | 2024-07-01  | infinity   | null             | OK（最終レコード）

異常パターンの例：
- 隙間がある場合：
  [2024-04-01 → 2024-07-01], [2024-07-15 → infinity]
  → 7/1～7/15の期間が抜けている

- 重複がある場合：
  [2024-04-01 → 2024-07-15], [2024-07-01 → infinity]
  → 7/1～7/15の期間が重複している
*/

3.5.3 トランザクションの一貫性

-- トランザクションを使用した一貫性の確保
-- 目的：関連する更新を全て成功するか、全て失敗するかのどちらかにする
BEGIN;

-- 1. 部署異動の登録
-- 1.1 現在の所属を終了
UPDATE department_assignments
SET effective = tstzrange(lower(effective), '2024-07-01')
WHERE employee_id = 'E001'
AND upper(effective) = 'infinity'
AND upper(assertive) = 'infinity';

-- 1.2 新しい所属を登録
INSERT INTO department_assignments (...) VALUES (...);

-- 2. 給与改定の登録
-- 2.1 現在の給与を終了
UPDATE salaries
SET effective = tstzrange(lower(effective), '2024-07-01')
WHERE employee_id = 'E001'
AND upper(effective) = 'infinity'
AND upper(assertive) = 'infinity';

-- 2.2 新しい給与を登録
INSERT INTO salaries (...) VALUES (...);

-- 整合性チェック
DO $$
DECLARE
    v_count integer;
BEGIN
    -- 1. 最優先：期間の重複防止チェック
    WITH overlap_check AS (
        SELECT 
            employee_id,
            effective,
            COUNT(*) OVER (
                PARTITION BY employee_id,
                CASE WHEN effective && lag(effective) 
                     OVER (PARTITION BY employee_id ORDER BY lower(effective))
                     THEN 1 ELSE 0 END
            ) as overlap_count
        FROM department_assignments
        WHERE assertive @> CURRENT_TIMESTAMP
    )
    SELECT COUNT(*)
    INTO v_count
    FROM overlap_check
    WHERE overlap_count > 1;

    IF v_count > 0 THEN
        RAISE EXCEPTION '期間の重複が検出されました（最優先のエラー）';
    END IF;

    -- 2. 部署所属期間と給与期間の整合性チェック
    SELECT COUNT(*)
    INTO v_count
    FROM department_assignments da
    LEFT JOIN salaries s ON 
        da.employee_id = s.employee_id AND
        da.effective && s.effective
    WHERE da.employee_id = 'E001'
    AND (s.employee_id IS NULL OR 
         lower(da.effective) != lower(s.effective) OR
         upper(da.effective) != upper(s.effective));
    
    IF v_count > 0 THEN
        RAISE EXCEPTION '部署所属期間と給与期間が一致しません';
    END IF;

    -- 3. 期間の連続性チェック（警告レベル）
    WITH continuity_check AS (
        SELECT 
            employee_id,
            lower(effective) as period_start,
            upper(effective) as period_end,
            LEAD(lower(effective)) OVER (
                PARTITION BY employee_id 
                ORDER BY lower(effective)
            ) as next_period_start
        FROM department_assignments
        WHERE assertive @> CURRENT_TIMESTAMP
    )
    SELECT COUNT(*)
    INTO v_count
    FROM continuity_check
    WHERE next_period_start IS NOT NULL
    AND next_period_start != period_end;

    IF v_count > 0 THEN
        RAISE WARNING '期間に隙間が存在します（警告レベル）';
    END IF;
END $$;

COMMIT;

/*
トランザクションの重要性：
1. 複数のテーブルの更新を一括で行う
2. チェックに失敗した場合は全ての更新をロールバック
3. データの整合性を確実に保持
*/

4. 実践的なユースケース

本章では、人事システムにおける実践的なユースケースを、以下の時系列に沿って説明します。
各シナリオは、事前登録・遡及登録・将来更新など、実務でよく発生する状況を想定しています。

@startuml
title 人事システムの時系列シナリオ

scale 1.5
skinparam backgroundColor white

' 2024年の出来事
rectangle 2024 {
    rectangle "3月" {
        rectangle "3/15\n新入社員情報の事前登録" as Mar15 #lightblue
    }
    
    rectangle "4月" {
        rectangle "4/1\n新入社員の入社\n- 山田花子：開発部\n- 鈴木一郎：営業部" as Apr1 #lightblue
    }
    
    rectangle "6月" {
        rectangle "6/15\n7月の定期昇給を事前登録\n25万円→27万円" as Jun15 #lightgreen
    }
    
    rectangle "7月" {
        rectangle "7/1\n定期昇給発効" as Jul1 #lightgreen
    }
    
    rectangle "8月" {
        rectangle "8/1\n山田花子：開発部→営業部\n（異動発令）" as Aug1 #pink
        rectangle "8/10\n異動情報の遡及登録" as Aug10 #pink
    }
    
    rectangle "9月" {
        rectangle "9/20\n組織改編を事前登録\n営業部→営業1部・営業2部" as Sep20 #orange
    }
    
    rectangle "10月" {
        rectangle "10/1\n組織改編発効\n- 山田花子：営業1部\n- 鈴木一郎：営業2部" as Oct1 #orange
    }
    
    rectangle "11月" {
        rectangle "11/15\n山田花子の昇進を事前登録\n社員→主任（35万円）" as Nov15 #yellow
    }
}

' 2025年の出来事
rectangle 2025 {
    rectangle "1月" {
        rectangle "1/1\n山田花子の昇進発効" as Jan1 #yellow
    }
}

' 時系列の流れを示す矢印
Mar15 --> Apr1
Apr1 --> Jun15
Jun15 --> Jul1
Jul1 --> Aug1
Aug1 --> Aug10
Aug10 --> Sep20
Sep20 --> Oct1
Oct1 --> Nov15
Nov15 --> Jan1

legend right
  青色: 新入社員入社
  緑色: 定期昇給
  ピンク色: 部署異動
  オレンジ色: 組織改編
  黄色: 昇進
endlegend

@enduml

シナリオの概要

時期	イベント	登録タイプ	対象者	変更内容
2024/3/15	新入社員登録	事前登録	山田花子鈴木一郎	・開発部配属（25万円）・営業部配属（25万円）
2024/6/15	定期昇給	事前登録	全社員	・給与改定（25万円→27万円）
2024/8/10	部署異動	遡及登録	山田花子	・開発部→営業部
2024/9/20	組織改編	事前登録	営業部全員	・営業部→営業1部/営業2部
2024/11/15	昇進	事前登録	山田花子	・社員→主任・給与改定（27万円→35万円）

4.1 基本シナリオ

4.1.1 新入社員の入社登録

シナリオ概要:

登録日：2024年3月15日
発効日：2024年4月1日
対象者：
- 山田花子：開発部配属、初任給25万円
- 鈴木一郎：営業部配属、初任給25万円

@startuml
title 新入社員の入社登録（2024年4月入社）

skinparam backgroundColor white

rectangle "業務時間（Effective）" {
    rectangle "2024/4/1～\n山田花子：開発部（25万円）\n鈴木一郎：営業部（25万円）" as E1 #lightblue
}

rectangle "システム時間（Assertive）" {
    rectangle "2024/3/15～\n新入社員情報を事前登録" as A1 #lightgreen
}

note bottom
  事前登録のケース
  - 実際の入社日：2024/4/1
  - システム登録日：2024/3/15
end note

@enduml

@startuml
title 新入社員の入社登録のシーケンス図

actor "人事担当者" as HR
participant "人事システム" as System
database "データベース" as DB

HR -> System: 新入社員情報入力 (2024-03-15)
activate System

System -> DB: トランザクション開始（BEGIN）
activate DB

System -> System: 入力値の事前チェック
note right
  - メールアドレスの形式チェック
  - 入社日の妥当性チェック
  - 必須項目の入力チェック
end note

System -> DB: 従業員基本情報の登録（INSERT INTO employees）
note right
  - effective: [2024-04-01, infinity)
    理由: 入社日から業務上有効
  - assertive: [2024-03-15, infinity)
    理由: システム登録日から記録開始
end note
DB --> System: 登録成功

System -> DB: 部署所属情報の登録（INSERT INTO department_assignments）
note right
  - effective: [2024-04-01, infinity)
  - assertive: [2024-03-15, infinity)
  - 山田花子：開発部
  - 鈴木一郎：営業部
end note
DB --> System: 登録成功

System -> DB: 給与情報の登録（INSERT INTO salaries）
note right
  - effective: [2024-04-01, infinity)
  - assertive: [2024-03-15, infinity)
  - 初任給：25万円
end note
DB --> System: 登録成功

System -> DB: 整合性チェック実行
note right
  1. 期間の重複チェック
  2. 部署所属期間と給与期間の整合性チェック
  3. 給与額の妥当性チェック（役職範囲内）
  4. マスタデータの参照整合性チェック
end note
DB --> System: チェック成功

System -> DB: トランザクション確定（COMMIT）
deactivate DB

System -> HR: 登録完了通知
deactivate System

@enduml

処理前のテーブル状態:

-- 全テーブルが空の状態を確認
SELECT COUNT(*) FROM employees;         -- 結果: 0
SELECT COUNT(*) FROM department_assignments;  -- 結果: 0
SELECT COUNT(*) FROM salaries;         -- 結果: 0

実行するSQL:

-- トランザクション開始
BEGIN;

-- 1. 従業員基本情報の登録
INSERT INTO employees (
    employee_id, first_name, last_name, 
    email, hire_date, effective, assertive
) VALUES 
    -- 山田花子の情報
    ('EMP001', '花子', '山田',
     'hanako.yamada@example.com', '2024-04-01',
     tstzrange('2024-04-01', 'infinity'),  -- 入社日から有効
     tstzrange('2024-03-15', 'infinity')), -- システム登録日から有効
    -- 鈴木一郎の情報
    ('EMP002', '一郎', '鈴木',
     'ichiro.suzuki@example.com', '2024-04-01',
     tstzrange('2024-04-01', 'infinity'),
     tstzrange('2024-03-15', 'infinity'));

-- 2. 部署所属情報の登録
INSERT INTO department_assignments (
    employee_id, department_id, position_id,
    effective, assertive
) VALUES 
    -- 山田花子：開発部配属
    ('EMP001', 'D001', 'P001',  -- 開発部・社員
     tstzrange('2024-04-01', 'infinity'),
     tstzrange('2024-03-15', 'infinity')),
    -- 鈴木一郎：営業部配属
    ('EMP002', 'D002', 'P001',  -- 営業部・社員
     tstzrange('2024-04-01', 'infinity'),
     tstzrange('2024-03-15', 'infinity'));

-- 3. 給与情報の登録
INSERT INTO salaries (
    employee_id, amount, salary_type,
    effective, assertive
) VALUES 
    -- 山田花子の給与
    ('EMP001', 250000, 'MONTHLY',  -- 25万円
     tstzrange('2024-04-01', 'infinity'),
     tstzrange('2024-03-15', 'infinity')),
    -- 鈴木一郎の給与
    ('EMP002', 250000, 'MONTHLY',  -- 25万円
     tstzrange('2024-04-01', 'infinity'),
     tstzrange('2024-03-15', 'infinity'));

-- 整合性チェック
DO $$
DECLARE
    v_count integer;
BEGIN
    -- 1. 部署所属期間と給与期間の整合性チェック
    SELECT COUNT(*)
    INTO v_count
    FROM department_assignments da
    LEFT JOIN salaries s ON 
        da.employee_id = s.employee_id AND
        da.effective = s.effective
    WHERE da.employee_id IN ('EMP001', 'EMP002')
    AND s.employee_id IS NULL;

    IF v_count > 0 THEN
        RAISE EXCEPTION '部署所属期間と給与期間が一致しません';
    END IF;

    -- 2. メールアドレスの重複チェック
    SELECT COUNT(*)
    INTO v_count
    FROM employees
    WHERE email IN ('hanako.yamada@example.com', 'ichiro.suzuki@example.com')
    GROUP BY email
    HAVING COUNT(*) > 1;

    IF v_count > 0 THEN
        RAISE EXCEPTION 'メールアドレスが重複しています';
    END IF;
END $$;

-- トランザクションの確定
COMMIT;

処理後のテーブル状態:

-- 1. 従業員基本情報の確認
SELECT 
    e.employee_id,
    e.last_name || ' ' || e.first_name as full_name,
    e.email,
    e.hire_date,
    e.effective as valid_period,
    e.assertive as system_period
FROM employees e
ORDER BY e.employee_id;

-- 2. 部署所属情報の確認
SELECT 
    da.employee_id,
    e.last_name || ' ' || e.first_name as full_name,
    d.department_name,
    p.position_name,
    da.effective as valid_period,
    da.assertive as system_period
FROM department_assignments da
JOIN employees e ON da.employee_id = e.employee_id
JOIN departments d ON da.department_id = d.department_id
JOIN positions p ON da.position_id = p.position_id
ORDER BY da.employee_id;

-- 3. 給与情報の確認
SELECT 
    s.employee_id,
    e.last_name || ' ' || e.first_name as full_name,
    s.amount,
    s.salary_type,
    s.effective as valid_period,
    s.assertive as system_period
FROM salaries s
JOIN employees e ON s.employee_id = e.employee_id
ORDER BY s.employee_id;

4.1.2 定期昇給（複数社員）

シナリオ概要:

登録日：2024年6月15日
発効日：2024年7月1日
対象者：全社員
内容：基本給を25万円から27万円に昇給

@startuml
title 定期昇給の登録（2024年7月昇給）

skinparam backgroundColor white

rectangle "業務時間（Effective）" {
    rectangle "2024/4/1-6/30\n基本給：25万円" as E1 #lightblue
    rectangle "2024/7/1～\n基本給：27万円" as E2 #lightgreen
}

rectangle "システム時間（Assertive）" {
    rectangle "2024/6/15～\n7月昇給情報を事前登録" as A1 #lightgreen
}

note bottom
  事前登録のケース
  - 実際の昇給日：2024/7/1
  - システム登録日：2024/6/15
end note

E1 -[hidden]right-> E2

@enduml

@startuml
title 定期昇給のシーケンス図

actor "人事担当者" as HR
participant "人事システム" as System
database "データベース" as DB

HR -> System: 定期昇給情報入力 (2024-06-15)
activate System

System -> DB: トランザクション開始（BEGIN）
activate DB

System -> System: 入力値の事前チェック
note right
  - 昇給日の妥当性チェック
  - 給与額の範囲チェック
  - 対象者の存在チェック
end note

System -> DB: 現在の給与情報を終了（UPDATE salaries）
note right
  - effective終了日: 2024-07-01
    理由: 昇給日の前日まで現在の給与が有効
  - 対象: 全社員
end note
DB --> System: 更新成功

System -> DB: 新しい給与情報を登録（INSERT INTO salaries）
note right
  - effective: [2024-07-01, infinity)
    理由: 昇給日から新給与が有効
  - assertive: [2024-06-15, infinity)
    理由: システム登録日から記録開始
  - 給与額: 27万円
end note
DB --> System: 登録成功

System -> DB: 整合性チェック実行
note right
  1. 給与期間の連続性チェック
  2. 給与額の妥当性チェック（役職範囲内）
  3. 部署所属期間との整合性チェック
end note
DB --> System: チェック成功

System -> DB: トランザクション確定（COMMIT）
deactivate DB

System -> HR: 昇給登録完了通知
deactivate System

@enduml

処理前のテーブル状態:

-- 現在の給与情報を確認
SELECT 
    s.employee_id,
    e.last_name || ' ' || e.first_name as full_name,
    s.amount as current_salary,
    s.effective as valid_period,
    s.assertive as system_period
FROM salaries s
JOIN employees e ON s.employee_id = e.employee_id
WHERE s.assertive @> CURRENT_TIMESTAMP
ORDER BY s.employee_id;

employee_id	full_name	current_salary	valid_period	system_period
EMP001	山田花子	250000	[2024-04-01, infinity)	[2024-03-15, infinity)
EMP002	鈴木一郎	250000	[2024-04-01, infinity)	[2024-03-15, infinity)

実行するSQL:

-- トランザクション開始
BEGIN;

-- 1. 現在の給与情報の終了日時を設定
UPDATE salaries
SET 
    effective = tstzrange(
        lower(effective),
        '2024-07-01'    -- 昇給日の00:00:00で終了
    ),
    updated_at = CURRENT_TIMESTAMP
WHERE 
    employee_id IN ('EMP001', 'EMP002')
    AND upper(effective) = 'infinity'
    AND upper(assertive) = 'infinity';

-- 2. 昇給後の給与情報を一括登録
INSERT INTO salaries (
    employee_id,
    amount,
    salary_type,
    effective,
    assertive
)
SELECT 
    employee_id,
    270000,          -- 昇給後の給与額（27万円）
    salary_type,
    tstzrange('2024-07-01', 'infinity'),  -- 昇給日から有効
    tstzrange(CURRENT_TIMESTAMP, 'infinity')  -- システム登録日から有効
FROM salaries
WHERE employee_id IN ('EMP001', 'EMP002')
GROUP BY employee_id, salary_type;

-- 整合性チェック
DO $$
DECLARE
    v_count integer;
BEGIN
    -- 1. 給与期間の連続性チェック
    WITH period_check AS (
        SELECT 
            employee_id,
            lower(effective) as start_date,
            upper(effective) as end_date,
            LEAD(lower(effective)) OVER (
                PARTITION BY employee_id 
                ORDER BY lower(effective)
            ) as next_start_date
        FROM salaries
        WHERE employee_id IN ('EMP001', 'EMP002')
        AND assertive @> CURRENT_TIMESTAMP
    )
    SELECT COUNT(*)
    INTO v_count
    FROM period_check
    WHERE next_start_date IS NOT NULL
    AND next_start_date != end_date;

    IF v_count > 0 THEN
        RAISE EXCEPTION '給与期間に隙間または重複があります';
    END IF;

    -- 2. 給与額の妥当性チェック
    SELECT COUNT(*)
    INTO v_count
    FROM salaries s
    JOIN positions p ON s.amount < p.min_salary OR s.amount > p.max_salary
    JOIN department_assignments da ON 
        s.employee_id = da.employee_id AND
        da.position_id = p.position_id AND
        da.effective @> lower(s.effective)
    WHERE s.employee_id IN ('EMP001', 'EMP002')
    AND s.assertive @> CURRENT_TIMESTAMP;

    IF v_count > 0 THEN
        RAISE EXCEPTION '給与額が役職の範囲外です';
    END IF;
END $$;

-- トランザクションの確定
COMMIT;

処理後のテーブル状態:

-- 給与履歴の確認
SELECT 
    s.employee_id,
    e.last_name || ' ' || e.first_name as full_name,
    s.amount,
    s.effective as valid_period,
    s.assertive as system_period,
    CASE 
        WHEN upper(s.assertive) = 'infinity' THEN '有効'
        ELSE '無効'
    END as status
FROM salaries s
JOIN employees e ON s.employee_id = e.employee_id
ORDER BY s.employee_id, lower(s.effective);

employee_id	full_name	amount	valid_period	system_period	status
EMP001	山田花子	250000	[2024-04-01, 2024-07-01)	[2024-03-15, infinity)	有効
EMP001	山田花子	270000	[2024-07-01, infinity)	[2024-06-15, infinity)	有効
EMP002	鈴木一郎	250000	[2024-04-01, 2024-07-01)	[2024-03-15, infinity)	有効
EMP002	鈴木一郎	270000	[2024-07-01, infinity)	[2024-06-15, infinity)	有効

4.2 遡及修正シナリオ

4.2.1 部署異動の遡及登録（届出遅延）

シナリオ概要:

登録日：2024年8月10日（遅延登録）
発効日：2024年8月1日（実際の異動日）
対象者：山田花子
内容：開発部から営業部への異動

@startuml
title 部署異動の遡及登録

skinparam backgroundColor white

rectangle "業務時間（Effective）" {
    rectangle "2024/4/1-7/31\n所属：開発部" as E1 #lightblue
    rectangle "2024/8/1～\n所属：営業部" as E2 #pink
}

rectangle "システム時間（Assertive）" {
    rectangle "2024/8/10～\n8/1付異動を遅延登録" as A1 #pink
}

note bottom
  遡及登録のケース
  - 実際の異動日：2024/8/1
  - システム登録日：2024/8/10（9日遅延）
end note

E1 -[hidden]right-> E2

@enduml

@startuml
title 部署異動の遡及登録のシーケンス図

actor "人事担当者" as HR
participant "人事システム" as System
database "データベース" as DB

HR -> System: 異動情報の遡及登録リクエスト (2024-08-10)
activate System

System -> DB: トランザクション開始（BEGIN）
activate DB

System -> System: 入力値の事前チェック
note right
  - 異動日の妥当性チェック
  - 部署コードの存在チェック
  - 必須項目の入力チェック
end note

System -> DB: 現在の部署所属情報を終了（UPDATE department_assignments）
note right
  - effective終了日: 2024-08-01
    理由: 実際の異動日の前日まで
    現在の所属が有効
end note
DB --> System: 更新成功

System -> DB: 新しい部署所属情報を登録（INSERT INTO department_assignments）
note right
  - effective: [2024-08-01, infinity)
    理由: 実際の異動日から新所属が有効
  - assertive: [2024-08-10, infinity)
    理由: システム登録日から記録開始
  - 異動先: 営業部
end note
DB --> System: 登録成功

System -> DB: 整合性チェック実行
note right
  1. 部署所属期間の連続性チェック
  2. 遡及期間の妥当性チェック
  3. 給与期間との整合性チェック
  4. マスタデータの参照整合性チェック
end note
DB --> System: チェック成功

System -> DB: トランザクション確定（COMMIT）
deactivate DB

System -> HR: 異動情報登録完了通知
deactivate System

@enduml

処理前のテーブル状態:

-- 現在の部署所属情報を確認
SELECT 
    da.employee_id,
    e.last_name || ' ' || e.first_name as full_name,
    d.department_name,
    p.position_name,
    da.effective as valid_period,
    da.assertive as system_period
FROM department_assignments da
JOIN employees e ON da.employee_id = e.employee_id
JOIN departments d ON da.department_id = d.department_id
JOIN positions p ON da.position_id = p.position_id
WHERE da.employee_id = 'EMP001'
AND da.assertive @> CURRENT_TIMESTAMP;

employee_id	full_name	department_name	position_name	valid_period	system_period
EMP001	山田花子	開発部	社員	[2024-04-01, infinity)	[2024-03-15, infinity)

実行するSQL:

-- トランザクション開始
BEGIN;

-- 1. 現在の部署所属情報の終了日時を設定
UPDATE department_assignments
SET 
    effective = tstzrange(
        lower(effective),
        '2024-08-01'    -- 異動日の00:00:00で終了
    ),
    updated_at = CURRENT_TIMESTAMP
WHERE 
    employee_id = 'EMP001'
    AND upper(effective) = 'infinity'
    AND upper(assertive) = 'infinity';

-- 2. 新しい部署所属情報を登録（遡及）
INSERT INTO department_assignments (
    employee_id,
    department_id,
    position_id,      -- 役職は変更なし
    effective,        -- 業務上の有効期間
    assertive         -- システム上の有効期間
) VALUES (
    'EMP001',        -- 山田花子
    'D002',          -- 営業部
    'P001',          -- 社員（変更なし）
    -- 異動日から無期限で有効
    tstzrange('2024-08-01', 'infinity'),
    -- システム登録日から無期限で有効（遡及登録）
    tstzrange(CURRENT_TIMESTAMP, 'infinity')
);

-- 整合性チェック
DO $$
DECLARE
    v_count integer;
BEGIN
    -- 1. 部署所属期間の連続性チェック
    WITH period_check AS (
        SELECT 
            employee_id,
            lower(effective) as start_date,
            upper(effective) as end_date,
            LEAD(lower(effective)) OVER (
                PARTITION BY employee_id 
                ORDER BY lower(effective)
            ) as next_start_date
        FROM department_assignments
        WHERE employee_id = 'EMP001'
        AND assertive @> CURRENT_TIMESTAMP
    )
    SELECT COUNT(*)
    INTO v_count
    FROM period_check
    WHERE next_start_date IS NOT NULL
    AND next_start_date != end_date;

    IF v_count > 0 THEN
        RAISE EXCEPTION '部署所属期間に隙間または重複があります';
    END IF;

    -- 2. 遡及期間の妥当性チェック
    -- （現在日付より未来の日付での遡及を防止）
    IF EXISTS (
        SELECT 1
        FROM department_assignments
        WHERE employee_id = 'EMP001'
        AND lower(effective) > CURRENT_DATE
        AND lower(assertive) = CURRENT_TIMESTAMP
    ) THEN
        RAISE EXCEPTION '未来日付への遡及はできません';
    END IF;

    -- 3. 遡及による給与期間との整合性チェック
    IF EXISTS (
        SELECT 1
        FROM department_assignments da
        LEFT JOIN salaries s ON 
            da.employee_id = s.employee_id AND
            da.effective && s.effective
        WHERE da.employee_id = 'EMP001'
        AND s.employee_id IS NULL
    ) THEN
        RAISE EXCEPTION '給与期間との整合性が取れません';
    END IF;
END $$;

-- トランザクションの確定
COMMIT;

処理後のテーブル状態:

-- 部署所属履歴の確認
SELECT 
    da.employee_id,
    e.last_name || ' ' || e.first_name as full_name,
    d.department_name,
    p.position_name,
    da.effective as valid_period,
    da.assertive as system_period,
    CASE 
        WHEN upper(da.assertive) = 'infinity' THEN '有効'
        ELSE '無効'
    END as status
FROM department_assignments da
JOIN employees e ON da.employee_id = e.employee_id
JOIN departments d ON da.department_id = d.department_id
JOIN positions p ON da.position_id = p.position_id
WHERE da.employee_id = 'EMP001'
ORDER BY lower(da.effective);

employee_id	full_name	department_name	position_name	valid_period	system_period	status
EMP001	山田花子	開発部	社員	[2024-04-01, 2024-08-01)	[2024-03-15, infinity)	有効
EMP001	山田花子	営業部	社員	[2024-08-01, infinity)	[2024-08-10, infinity)	有効

4.2.2 組織改編による一括更新

シナリオ概要:

登録日：2024年9月20日
発効日：2024年10月1日
内容：営業部を営業1部と営業2部に分割
対象者：
- 山田花子：営業部 → 営業1部
- 鈴木一郎：営業部 → 営業2部

@startuml
title 組織改編による一括更新

skinparam backgroundColor white

rectangle "業務時間（Effective）" {
    rectangle "～2024/9/30" as E1 {
        rectangle "営業部\n- 山田花子\n- 鈴木一郎" as E1_1 #lightblue
    }
    
    rectangle "2024/10/1～" as E2 {
        rectangle "営業1部\n- 山田花子" as E2_1 #orange
        rectangle "営業2部\n- 鈴木一郎" as E2_2 #orange
    }
}

rectangle "システム時間（Assertive）" {
    rectangle "2024/9/20～\n組織改編を事前登録" as A1 #orange
}

note bottom
  事前登録のケース
  - 実際の改編日：2024/10/1
  - システム登録日：2024/9/20
end note

E1 -[hidden]right-> E2
E2_1 -[hidden]right-> E2_2

@enduml

@startuml
title 組織改編のシーケンス図

actor "人事担当者" as HR
participant "人事システム" as System
database "データベース" as DB

HR -> System: 組織改編情報入力 (2024-09-20)
activate System

System -> DB: トランザクション開始（BEGIN）
activate DB

System -> System: 入力値の事前チェック
note right
  - 改編日の妥当性チェック
  - 新組織コードの重複チェック
  - 対象者の配置確認
end note

System -> DB: 新しい部署の登録（INSERT INTO departments）
note right
  - 営業1部（D003）
  - 営業2部（D004）を登録
end note
DB --> System: 登録成功

System -> DB: 現在の部署所属情報を終了（UPDATE department_assignments）
note right
  - effective終了日: 2024-10-01
    理由: 組織改編日の前日まで
    現在の所属が有効
  - 対象: 営業部所属の全員
end note
DB --> System: 更新成功

System -> DB: 新組織の所属情報を一括登録（INSERT INTO department_assignments）
note right
  - effective: [2024-10-01, infinity)
    理由: 組織改編日から新組織が有効
  - assertive: [2024-09-20, infinity)
    理由: システム登録日から記録開始
  - 山田花子：営業1部
  - 鈴木一郎：営業2部
end note
DB --> System: 登録成功

System -> DB: 整合性チェック実行
note right
  1. 部署所属期間の連続性チェック
  2. 全員の新組織割当確認
  3. マスタデータの参照整合性チェック
  4. 給与情報との整合性チェック
end note
DB --> System: チェック成功

System -> DB: トランザクション確定（COMMIT）
deactivate DB

System -> HR: 組織改編情報登録完了通知
deactivate System

@enduml

処理前のテーブル状態:

-- 現在の部署所属情報を確認
SELECT 
    da.employee_id,
    e.last_name || ' ' || e.first_name as full_name,
    d.department_name,
    p.position_name,
    da.effective as valid_period
FROM department_assignments da
JOIN employees e ON da.employee_id = e.employee_id
JOIN departments d ON da.department_id = d.department_id
JOIN positions p ON da.position_id = p.position_id
WHERE da.assertive @> CURRENT_TIMESTAMP
AND da.employee_id IN ('EMP001', 'EMP002')
ORDER BY da.employee_id;

employee_id	full_name	department_name	position_name	valid_period
EMP001	山田花子	営業部	社員	[2024-08-01, infinity)
EMP002	鈴木一郎	営業部	社員	[2024-04-01, infinity)

実行するSQL:

-- トランザクション開始
BEGIN;

-- 1. 新しい部署の登録（部署マスタの更新）
INSERT INTO departments (
    department_id, 
    department_name,
    created_at,
    updated_at
) VALUES
    ('D003', '営業1部', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
    ('D004', '営業2部', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

-- 2. 既存の部署所属情報の終了日設定
UPDATE department_assignments
SET 
    effective = tstzrange(
        lower(effective),
        '2024-10-01'    -- 組織改編日の00:00:00で終了
    ),
    updated_at = CURRENT_TIMESTAMP
WHERE 
    department_id = 'D002'   -- 営業部
    AND employee_id IN ('EMP001', 'EMP002')
    AND upper(effective) = 'infinity'
    AND upper(assertive) = 'infinity';

-- 3. 新組織での所属情報を一括登録
INSERT INTO department_assignments (
    employee_id,
    department_id,
    position_id,
    effective,
    assertive
)
SELECT 
    t.employee_id,
    t.new_department_id,
    t.position_id,
    tstzrange('2024-10-01', 'infinity'),  -- 組織改編日から有効
    tstzrange(CURRENT_TIMESTAMP, 'infinity')  -- システム登録日から有効
FROM (
    VALUES 
        ('EMP001', 'D003', 'P001'),  -- 山田花子 → 営業1部
        ('EMP002', 'D004', 'P001')   -- 鈴木一郎 → 営業2部
) AS t(employee_id, new_department_id, position_id);

-- 整合性チェック
DO $$
DECLARE
    v_count integer;
BEGIN
    -- 1. 部署所属期間の連続性チェック
    WITH period_check AS (
        SELECT 
            employee_id,
            lower(effective) as start_date,
            upper(effective) as end_date,
            LEAD(lower(effective)) OVER (
                PARTITION BY employee_id 
                ORDER BY lower(effective)
            ) as next_start_date
        FROM department_assignments
        WHERE employee_id IN ('EMP001', 'EMP002')
        AND assertive @> CURRENT_TIMESTAMP
    )
    SELECT COUNT(*)
    INTO v_count
    FROM period_check
    WHERE next_start_date IS NOT NULL
    AND next_start_date != end_date;

    IF v_count > 0 THEN
        RAISE EXCEPTION '部署所属期間に隙間または重複があります';
    END IF;

    -- 2. 全員が新組織に割り当てられていることを確認
    SELECT COUNT(*)
    INTO v_count
    FROM department_assignments da
    WHERE da.department_id = 'D002'  -- 旧組織（営業部）
    AND da.employee_id IN ('EMP001', 'EMP002')
    AND upper(da.effective) = 'infinity'
    AND upper(da.assertive) = 'infinity';

    IF v_count > 0 THEN
        RAISE EXCEPTION '新組織への割り当てがされていない従業員が存在します';
    END IF;

    -- 3. 新組織の部署IDの妥当性チェック
    IF EXISTS (
        SELECT 1
        FROM department_assignments da
        WHERE da.department_id NOT IN (
            SELECT department_id 
            FROM departments
        )
        AND da.assertive @> CURRENT_TIMESTAMP
    ) THEN
        RAISE EXCEPTION '無効な部署IDが指定されています';
    END IF;
END $$;

-- トランザクションの確定
COMMIT;

処理後のテーブル状態:

-- 部署所属履歴の確認
SELECT 
    da.employee_id,
    e.last_name || ' ' || e.first_name as full_name,
    d.department_name,
    p.position_name,
    da.effective as valid_period,
    da.assertive as system_period,
    CASE 
        WHEN upper(da.assertive) = 'infinity' THEN '有効'
        ELSE '無効'
    END as status
FROM department_assignments da
JOIN employees e ON da.employee_id = e.employee_id
JOIN departments d ON da.department_id = d.department_id
JOIN positions p ON da.position_id = p.position_id
WHERE da.employee_id IN ('EMP001', 'EMP002')
ORDER BY da.employee_id, lower(da.effective);

employee_id	full_name	department_name	position_name	valid_period	system_period	status
EMP001	山田花子	営業部	社員	[2024-08-01, 2024-10-01)	[2024-08-10, infinity)	有効
EMP001	山田花子	営業1部	社員	[2024-10-01, infinity)	[2024-09-20, infinity)	有効
EMP002	鈴木一郎	営業部	社員	[2024-04-01, 2024-10-01)	[2024-03-15, infinity)	有効
EMP002	鈴木一郎	営業2部	社員	[2024-10-01, infinity)	[2024-09-20, infinity)	有効

4.3 将来更新シナリオ

4.3.1 昇進予定の事前登録

シナリオ概要:

登録日：2024年11月15日
発効日：2025年1月1日
対象者：山田花子
内容：
- 役職：社員 → 主任
- 給与：27万円 → 35万円

@startuml
title 昇進予定の事前登録

skinparam backgroundColor white

rectangle "業務時間（Effective）" {
    rectangle "～2024/12/31" as E1 {
        rectangle "山田花子\n役職：社員\n給与：27万円" as E1_1 #lightblue
    }
    
    rectangle "2025/1/1～" as E2 {
        rectangle "山田花子\n役職：主任\n給与：35万円" as E2_1 #yellow
    }
}

rectangle "システム時間（Assertive）" {
    rectangle "2024/11/15～\n昇進情報を事前登録" as A1 #yellow
}

note bottom
  事前登録のケース
  - 実際の昇進日：2025/1/1
  - システム登録日：2024/11/15
  - 約1.5ヶ月前に事前登録
end note

E1 -[hidden]right-> E2

@enduml

@startuml
title 昇進予定の事前登録のシーケンス図

actor "人事担当者" as HR
participant "人事システム" as System
database "データベース" as DB

HR -> System: 昇進予定情報入力 (2024-11-15)
activate System

System -> DB: トランザクション開始（BEGIN）
activate DB

System -> System: 入力値の事前チェック
note right
  - 昇進日の妥当性チェック
  - 役職の昇進順序チェック
  - 給与額の範囲チェック
end note

System -> DB: 現在の部署所属情報を終了（UPDATE department_assignments）
note right
  - effective終了日: 2025-01-01
    理由: 昇進日の前日まで
    現在の役職が有効
end note
DB --> System: 更新成功

System -> DB: 昇進後の部署所属情報を登録（INSERT INTO department_assignments）
note right
  - effective: [2025-01-01, infinity)
    理由: 昇進日から新役職が有効
  - assertive: [2024-11-15, infinity)
    理由: システム登録日から記録開始
  - 部署: 営業1部（変更なし）
  - 役職: 主任
end note
DB --> System: 登録成功

System -> DB: 現在の給与情報を終了（UPDATE salaries）
note right
  - effective終了日: 2025-01-01
    理由: 昇進日の前日まで
    現在の給与が有効
end note
DB --> System: 更新成功

System -> DB: 昇進後の給与情報を登録（INSERT INTO salaries）
note right
  - effective: [2025-01-01, infinity)
    理由: 昇進日から新給与が有効
  - assertive: [2024-11-15, infinity)
    理由: システム登録日から記録開始
  - 給与額: 35万円
end note
DB --> System: 登録成功

System -> DB: 整合性チェック実行
note right
  1. 部署所属期間と給与期間の整合性チェック
  2. 給与額の妥当性チェック（役職範囲内）
  3. 将来日付の妥当性チェック
  4. マスタデータの参照整合性チェック
end note
DB --> System: チェック成功

System -> DB: トランザクション確定（COMMIT）
deactivate DB

System -> HR: 昇進予定登録完了通知
deactivate System

@enduml

処理前のテーブル状態:

-- 現在の役職と給与情報を確認
SELECT 
    e.employee_id,
    e.last_name || ' ' || e.first_name as full_name,
    d.department_name,
    p.position_name,
    s.amount as salary,
    da.effective as assignment_period,
    s.effective as salary_period
FROM employees e
JOIN department_assignments da ON e.employee_id = da.employee_id
JOIN departments d ON da.department_id = d.department_id
JOIN positions p ON da.position_id = p.position_id
JOIN salaries s ON e.employee_id = s.employee_id
WHERE e.employee_id = 'EMP001'
AND da.assertive @> CURRENT_TIMESTAMP
AND s.assertive @> CURRENT_TIMESTAMP;

employee_id	full_name	department_name	position_name	salary	assignment_period	salary_period
EMP001	山田花子	営業1部	社員	270000	[2024-10-01, infinity)	[2024-07-01, infinity)

実行するSQL:

-- トランザクション開始
BEGIN;

-- 1. 現在の部署所属情報の終了日設定
UPDATE department_assignments
SET 
    effective = tstzrange(
        lower(effective),
        '2025-01-01'    -- 昇進日の00:00:00で終了
    ),
    updated_at = CURRENT_TIMESTAMP
WHERE 
    employee_id = 'EMP001'
    AND upper(effective) = 'infinity'
    AND upper(assertive) = 'infinity';

-- 2. 昇進後の部署所属情報を登録
INSERT INTO department_assignments (
    employee_id,
    department_id,    -- 部署は変更なし
    position_id,      -- 役職を主任に変更
    effective,        -- 業務上の有効期間
    assertive         -- システム上の有効期間
) VALUES (
    'EMP001',        -- 山田花子
    'D003',          -- 営業1部（変更なし）
    'P002',          -- 主任
    tstzrange('2025-01-01', 'infinity'),  -- 昇進日から有効
    tstzrange(CURRENT_TIMESTAMP, 'infinity')  -- システム登録日から有効
);

-- 3. 現在の給与情報の終了日設定
UPDATE salaries
SET 
    effective = tstzrange(
        lower(effective),
        '2025-01-01'    -- 昇進日の00:00:00で終了
    ),
    updated_at = CURRENT_TIMESTAMP
WHERE 
    employee_id = 'EMP001'
    AND upper(effective) = 'infinity'
    AND upper(assertive) = 'infinity';

-- 4. 昇進後の給与情報を登録
INSERT INTO salaries (
    employee_id,
    amount,          -- 昇進後の給与
    salary_type,
    effective,
    assertive
) VALUES (
    'EMP001',       -- 山田花子
    350000,         -- 35万円
    'MONTHLY',
    tstzrange('2025-01-01', 'infinity'),  -- 昇進日から有効
    tstzrange(CURRENT_TIMESTAMP, 'infinity')  -- システム登録日から有効
);

-- 整合性チェック
DO $$
DECLARE
    v_count integer;
BEGIN
    -- 1. 部署所属期間と給与期間の整合性チェック
    WITH period_check AS (
        SELECT 
            da.employee_id,
            da.effective as assignment_period,
            s.effective as salary_period
        FROM department_assignments da
        LEFT JOIN salaries s ON 
            da.employee_id = s.employee_id AND
            da.effective = s.effective
        WHERE da.employee_id = 'EMP001'
        AND da.assertive @> CURRENT_TIMESTAMP
        AND s.assertive @> CURRENT_TIMESTAMP
    )
    SELECT COUNT(*)
    INTO v_count
    FROM period_check
    WHERE assignment_period != salary_period;

    IF v_count > 0 THEN
        RAISE EXCEPTION '部署所属期間と給与期間が一致しません';
    END IF;

    -- 2. 給与額の妥当性チェック（役職の給与範囲内か）
    IF EXISTS (
        SELECT 1
        FROM salaries s
        JOIN department_assignments da ON 
            s.employee_id = da.employee_id AND
            s.effective = da.effective
        JOIN positions p ON da.position_id = p.position_id
        WHERE s.employee_id = 'EMP001'
        AND s.assertive @> CURRENT_TIMESTAMP
        AND (s.amount < p.min_salary OR s.amount > p.max_salary)
    ) THEN
        RAISE EXCEPTION '給与額が役職の範囲外です';
    END IF;

    -- 3. 将来日付の妥当性チェック
    IF EXISTS (
        SELECT 1
        FROM department_assignments
        WHERE employee_id = 'EMP001'
        AND lower(effective) < CURRENT_DATE
        AND lower(assertive) = CURRENT_TIMESTAMP
    ) THEN
        RAISE EXCEPTION '過去日付での事前登録はできません';
    END IF;
END $$;

-- トランザクションの確定
COMMIT;

処理後のテーブル状態:

-- 役職と給与の変更履歴を確認
SELECT 
    e.employee_id,
    e.last_name || ' ' || e.first_name as full_name,
    d.department_name,
    p.position_name,
    s.amount as salary,
    da.effective as valid_period,
    da.assertive as system_period,
    CASE 
        WHEN upper(da.assertive) = 'infinity' THEN '有効'
        ELSE '無効'
    END as status
FROM department_assignments da
JOIN employees e ON da.employee_id = e.employee_id
JOIN departments d ON da.department_id = d.department_id
JOIN positions p ON da.position_id = p.position_id
JOIN salaries s ON 
    da.employee_id = s.employee_id AND
    da.effective = s.effective
WHERE da.employee_id = 'EMP001'
ORDER BY lower(da.effective);

employee_id	full_name	department_name	position_name	salary	valid_period	system_period	status
EMP001	山田花子	営業1部	社員	270000	[2024-10-01, 2025-01-01)	[2024-09-20, infinity)	有効
EMP001	山田花子	営業1部	主任	350000	[2025-01-01, infinity)	[2024-11-15, infinity)	有効

5. effectiveとassertiveの更新パターン

5.1. 基本原則

5.1.1 時間軸の役割と更新原則

時間軸	用途	更新タイミング	データ整合性の観点
effective	人事発令や給与改定など、業務上の事実の有効期間	実際の発効日で区切る	・業務上の事実を正確に記録・将来に向かって設定可能
assertive	システムへの登録や修正の記録期間	システム操作実行日で区切る	・監査証跡として利用・過去に遡って設定不可

5.1.2 assertiveをシステム操作日で区切る理由

監査証跡としての正確性

-- 例：8月1日付の異動を8月10日に事後登録する場合
INSERT INTO department_assignments (
    employee_id,
    department_id,
    effective,    -- 業務上の事実
    assertive     -- システムでの記録
) VALUES (
    'E001',
    'D002',
    tstzrange('2024-08-01', 'infinity'),  -- 実際の異動日
    tstzrange(CURRENT_TIMESTAMP, 'infinity')  -- 登録時点
);

システムでの操作日時を正確に記録
データがいつ登録/更新されたかを追跡可能

データ整合性の維持

-- 誤りの修正例：7月分の給与額を8月15日に修正
-- 1. 誤ったデータの無効化
UPDATE salaries
SET assertive = tstzrange(
    lower(assertive),
    CURRENT_TIMESTAMP  -- 修正時点で終了
)
WHERE employee_id = 'E001'
AND effective @> '2024-07-01'::date;

-- 2. 正しいデータの登録
INSERT INTO salaries (
    employee_id,
    amount,
    effective,
    assertive
) VALUES (
    'E001',
    300000,
    tstzrange('2024-07-01', 'infinity'),
    tstzrange(CURRENT_TIMESTAMP, 'infinity')  -- 修正時点から有効
);

データの変更履歴を時系列で追跡可能
任意の時点での状態を再現可能

システムの整合性確保

-- 整合性チェックの例
SELECT 
    employee_id,
    effective,
    assertive,
    CASE 
        WHEN lower(assertive) > CURRENT_TIMESTAMP THEN
            'エラー：未来の登録日'
        WHEN lower(assertive) < lower(effective) AND
             upper(assertive) = 'infinity' THEN
            'OK：事前登録'
        WHEN lower(assertive) > lower(effective) AND
             upper(assertive) = 'infinity' THEN
            'OK：事後登録'
        ELSE
            'OK：履歴データ'
    END as status
FROM department_assignments;

未来日付での登録を防止
データの時系列的な整合性を確保

変更履歴の追跡

-- 特定データの変更履歴を確認
SELECT 
    employee_id,
    amount,
    effective,
    assertive,
    CASE 
        WHEN upper(assertive) = 'infinity' THEN '現在有効'
        ELSE '履歴'
    END as record_status
FROM salaries
WHERE employee_id = 'E001'
ORDER BY lower(assertive);

誰が、いつ、どのように変更したかを追跡
コンプライアンス要件への対応

5.1.3 更新時の制約事項

assertiveの制約

-- テーブル定義での制約例
ALTER TABLE department_assignments
ADD CONSTRAINT valid_assertive_period
CHECK (
    -- 未来日付での登録を防止
    lower(assertive) <= CURRENT_TIMESTAMP AND
    -- 期間の整合性確保（開始日 <= 終了日）
    lower(assertive) <= upper(assertive) AND
    -- 同一従業員の最古のassertiveより過去の日付を設定できない
    lower(assertive) >= (
        SELECT MIN(lower(assertive))
        FROM department_assignments da2
        WHERE da2.employee_id = department_assignments.employee_id
    )
);

effectiveの制約

ALTER TABLE department_assignments
ADD CONSTRAINT valid_effective_period
CHECK (
    -- 期間の整合性確保（開始日 <= 終了日）
    lower(effective) <= upper(effective)
);

相互の整合性

-- トリガーでの整合性チェック例
CREATE OR REPLACE FUNCTION check_temporal_consistency()
RETURNS TRIGGER AS $$
BEGIN
    -- 同一従業員の同一期間での重複チェック
    IF EXISTS (
        SELECT 1
        FROM department_assignments
        WHERE employee_id = NEW.employee_id
        AND id != NEW.id
        AND (
            -- assertive期間の重複チェック
            assertive && NEW.assertive
            OR
            -- effective期間の重複チェック（同一assertive期間内）
            (effective && NEW.effective AND assertive && NEW.assertive)
        )
    ) THEN
        RAISE EXCEPTION 'temporal period overlap detected';
    END IF;

    -- effectiveの開始日がassertiveの開始日より前の場合は事前登録
    IF lower(NEW.effective) < lower(NEW.assertive) THEN
        -- 事前登録の場合、最大3ヶ月先までしか登録できない
        IF lower(NEW.effective) > lower(NEW.assertive) + INTERVAL '3 months' THEN
            RAISE EXCEPTION '事前登録は最大3ヶ月先までです';
        END IF;
    END IF;

    -- effectiveの開始日がassertiveの開始日より後の場合は遡及登録
    IF lower(NEW.effective) > lower(NEW.assertive) THEN
        -- 遡及登録の場合、最大3ヶ月前までしか登録できない
        IF lower(NEW.effective) < lower(NEW.assertive) - INTERVAL '3 months' THEN
            RAISE EXCEPTION '遡及登録は最大3ヶ月前までです';
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- トリガーの定義
CREATE TRIGGER check_temporal_consistency_trigger
    BEFORE INSERT OR UPDATE ON department_assignments
    FOR EACH ROW
    EXECUTE FUNCTION check_temporal_consistency();

監査ログの設定

-- 監査ログテーブル
CREATE TABLE temporal_audit_log (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    table_name VARCHAR(50) NOT NULL,
    record_id BIGINT NOT NULL,
    operation VARCHAR(10) NOT NULL,
    old_effective tstzrange,
    new_effective tstzrange,
    old_assertive tstzrange,
    new_assertive tstzrange,
    changed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    changed_by VARCHAR(50) NOT NULL
);

-- 監査ログ用のトリガー
CREATE TRIGGER log_temporal_changes_trigger
    AFTER INSERT OR UPDATE OR DELETE ON department_assignments
    FOR EACH ROW
    EXECUTE FUNCTION log_temporal_changes();

5.2. 主なユースケースと更新パターン

5.2.1 事前登録パターン

例：4月1日付けの新入社員登録を3月15日に実施

effective: [2024-04-01, infinity)  -- 実際の入社日から有効
assertive: [2024-03-15, infinity)  -- システムへの登録日から有効

使用場面：
- 新入社員の入社登録
- 定期昇給の登録
- 人事異動の事前登録
- 組織改編の事前登録

5.2.2 遡及登録パターン

例：8月1日付けの異動を8月10日に事後登録

effective: [2024-08-01, infinity)  -- 実際の異動日から有効
assertive: [2024-08-10, infinity)  -- システムへの登録日から有効

使用場面：
- 届出遅延の異動登録
- 事後承認された昇給の登録
- 緊急の人事発令の事後登録

5.2.3 誤り訂正パターン

例：7月分の給与額の誤りを8月15日に修正

// 誤ったデータ
effective: [2024-07-01, infinity)
assertive: [2024-06-15, 2024-08-15)  -- 修正日で終了

// 正しいデータ
effective: [2024-07-01, infinity)
assertive: [2024-08-15, infinity)  -- 修正日から有効

使用場面：
- 給与額の修正
- 発令内容の訂正
- データ入力ミスの修正

5.3. 更新時の注意点

effectiveの更新
- 業務上の発効日を基準に設定
- 期間の連続性を必ず確保
- 将来に向かって設定可能
assertiveの更新
- システムでの操作日時を基準に設定
- 過去に遡って設定しない
- 誤りの修正は既存レコードのassertiveを終了
整合性の確保
- 同一期間での重複を防止
- トランザクションでの一括更新
- 関連データの同時更新

5.4. 実装のポイント

-- 基本的な更新パターン
BEGIN;
  -- 1. 現在のレコードを終了
  UPDATE target_table
  SET effective = tstzrange(lower(effective), '発効日')
  WHERE current_record = true;

  -- 2. 新しいレコードを登録
  INSERT INTO target_table (
    effective,  -- 業務上の有効期間
    assertive   -- システム上の有効期間
  ) VALUES (
    tstzrange('発効日', 'infinity'),
    tstzrange(CURRENT_TIMESTAMP, 'infinity')
  );
COMMIT;

-- 整合性チェックの例
WITH period_check AS (
  SELECT 
    employee_id,
    effective,
    LAG(effective) OVER (
      PARTITION BY employee_id 
      ORDER BY lower(effective)
    ) as prev_period
  FROM target_table
  WHERE assertive @> CURRENT_TIMESTAMP
)
SELECT *
FROM period_check
WHERE lower(effective) != upper(prev_period);

5.5. 更新パターンの選択フロー

@startuml
title データ更新パターンの選択フロー

skinparam backgroundColor white
skinparam ActivityBorderColor #666666
skinparam NoteBackgroundColor #FFFFCC
skinparam NoteBorderColor #999966

start

:データ更新の要件発生;

if (更新の種類?) then (基本シナリオ)
  if (更新内容?) then (新規登録)
    #lightblue:【新規登録パターン】;
    note right #lightblue
      例：2024年4月入社の新入社員を3月15日に登録
      ---
      ・実際の入社日: 2024/4/1
      ・システム登録日: 2024/3/15
      ---
      effective: [2024-04-01, infinity)
      assertive: [2024-03-15, infinity)
    end note
  else (定期更新)
    #lightgreen:【定期更新パターン】;
    note right #lightgreen
      例：2024年7月の定期昇給を6月15日に登録
      ---
      既存データ（現在の給与）:
      ・適用期間: 2024/4/1～2024/6/30
      ・登録日: 2024/3/15
      effective: [2024-04-01, 2024-07-01)
      assertive: [2024-03-15, infinity)
      
      新規データ（昇給後）:
      ・適用期間: 2024/7/1～
      ・登録日: 2024/6/15
      effective: [2024-07-01, infinity)
      assertive: [2024-06-15, infinity)
    end note
  endif

else (例外シナリオ)
  if (いつ登録する?) then (未来の事象を事前に登録)
    #palegreen:【事前登録パターン】;
    note right #palegreen
      例：2024年10月1日付の組織改編を9月15日に事前登録
      ---
      ・実際の改編日: 2024/10/1
      ・システム登録日: 2024/9/15
      ---
      effective: [2024-10-01, infinity)
      assertive: [2024-09-15, infinity)
    end note
  else (過去の事象を後から登録)
    if (データの誤りを修正?) then (Yes)
      #pink:【誤り訂正パターン】;
      note right #pink
        例：7月分の給与額の誤りを8月15日に修正
        ---
        既存データ（誤った給与）:
        ・適用期間: 2024/7/1～
        ・登録期間: 2024/6/15～2024/8/15
        effective: [2024-07-01, infinity)
        assertive: [2024-06-15, 2024-08-15)
        
        新規データ（正しい給与）:
        ・適用期間: 2024/7/1～
        ・登録日: 2024/8/15
        effective: [2024-07-01, infinity)
        assertive: [2024-08-15, infinity)
      end note
    else (No)
      #skyblue:【遡及登録パターン】;
      note right #skyblue
        例：8月1日付の異動を8月10日に事後登録
        ---
        ・実際の異動日: 2024/8/1
        ・システム登録日: 2024/8/10（9日遅延）
        ---
        effective: [2024-08-01, infinity)
        assertive: [2024-08-10, infinity)
      end note
    endif
  endif
endif

:整合性チェック;
note right
  1. 期間の重複がないこと
  2. 期間の連続性が保たれていること
  3. 業務ルールに違反していないこと
  4. マスタデータとの整合性
end note

stop

legend right
  基本シナリオ：
  <back:#lightblue>青: 新規登録（入社・採用）</back>
  <back:#lightgreen>緑: 定期更新（昇給・評価）</back>
  
  例外シナリオ：
  <back:#palegreen>薄緑: 事前登録（組織改編）</back>
  <back:#skyblue>水色: 遡及登録（届出遅延）</back>
  <back:#pink>赤: 誤り訂正（データ修正）</back>
endlegend

@enduml

テンポラルデータモデル入門(人事システムでの実装例)

0. はじめに

1. テンポラルデータモデルの基礎

1.1 テンポラルデータモデルとは

列名の英語的について

effective（形容詞）

assertive（形容詞）

1.2 モデルの分類と採用傾向

1.3 各モデルの特徴と使い分け

1.4 実際のユースケース例

2. データベース設計と実装

2.1 基本テーブル構造

2.2 テーブル定義

2.2.1 マスタテーブル

2.2.2 従業員基本情報（employees）

2.2.3 部署所属情報（department_assignments）

2.2.4 給与情報（salaries）

2.3 時間範囲型（tstzrange）の説明

2.4 重要な設計ポイント

2.4 重要な設計ポイント

3. CRUD操作の実装

3.1 新規登録（Create）操作

3.2 参照（Read）操作

3.2.1 現在の状態参照

3.2.2 特定時点の状態参照

3.2.3 履歴の参照

3.3 更新（Update）操作

3.4 論理削除（Logical Delete）操作

3.5 データ整合性の確保

3.5.1 期間の重複防止

3.5.2 期間の連続性確保

3.5.3 トランザクションの一貫性

4. 実践的なユースケース

シナリオの概要

4.1 基本シナリオ

4.1.1 新入社員の入社登録

4.1.2 定期昇給（複数社員）

4.2 遡及修正シナリオ

4.2.1 部署異動の遡及登録（届出遅延）

4.2.2 組織改編による一括更新

4.3 将来更新シナリオ

4.3.1 昇進予定の事前登録

5. effectiveとassertiveの更新パターン

5.1. 基本原則

5.1.1 時間軸の役割と更新原則

5.1.2 assertiveをシステム操作日で区切る理由

5.1.3 更新時の制約事項

5.2. 主なユースケースと更新パターン

5.2.1 事前登録パターン

5.2.2 遡及登録パターン

5.2.3 誤り訂正パターン

5.3. 更新時の注意点

5.4. 実装のポイント

5.5. 更新パターンの選択フロー

Discussion