状態管理のためのテーブル設計パターン


参考文献
『The Data Model Resource Book Volume 3 - Chapter 6』
https://books.rakuten.co.jp/rk/88ab134dd4d344af83cfb3d2987b2e77/?s-id=ph_pc_itemname

# パターン1（Level 1）
非常にシンプルで具体的、かつ理解しやすいテーブル設計をします。

### 実装
**状態遷移が生じるイベントの発生日時**を使用して状態を導出します。

例
```mermaid
erDiagram
    ORDER {
        ID order_id PK
        DATETIME recieved_datetime "受付日時"
        DATETIME cancelled_datetime "キャンセル日時"
    }
```

- 受注を受け付けた日時を**recieved_datetime**に記録する
- 受注がキャンセルされた日時を**cancelled_datetime**に記録する
- まだ受注を受け付けていない場合やキャンセルされていない場合は、それぞれ**null**となる

これにより、受注が**受注前**か**受注済**か**キャンセル済**かを導出することができます。
管理したい状態の数だけカラムを用意します。

### このパターンの使いどころ
- **状態が全て明確であり、将来的に状態が追加されないことがわかっている場合**
- **状態が非常に少ない場合**（会員の有効/無効など）
- **状態がある特定のエンティティに特化したものである場合**
    - 例えば、出荷における「貨物紛失日」（貨物紛失状態）は出荷でのみ使用される

### このパターンの弱み
- **状態の追加に大きなコストがかかる**
    - 事業の変化にともない新しい状態を追加したい場合、テーブルの変更が必要になる
- **状態の数が多くなると管理が煩雑になる**（どのカラムが状態に関係しているかが不明確になる）
- **状態の分類が消失している**
    - 例えば、注文処理のための`Received`, `Entered`, `Confirmed`と、注文スケジュールのための`On Schedule`, `Behind Schedule`, `Overdue`を分類できない
- **状態のルールを保持できない**
    - 例えば、受注が「受付済」でないと「キャンセル済」にはなれない、など


# パターン2（Level 2）
将来的に新しい状態が必要になる場合に対応したテーブル設計をします。

### 実装
**状態区分テーブル**を使用して現在の状態を記録します。

例
```mermaid
erDiagram
    ORDER_STATUS_TYPE {
        ID status_type_id PK
        VARCHAR name
    }
    ORDER {
        ID order_id PK
        ID status_type_id FK
        DATETIME status_updated_at "状態更新日時"
    }

    ORDER_STATUS_TYPE ||--|{ ORDER : status_type_id
```

- 受注(ORDER)が取りうるすべての状態を持つ**状態区分テーブル**(ORDER_STATUS_TYPE)を用意する
- 状態が変わったら受注テーブルの**status_type_id**を更新する

例えば、状態区分テーブルに「受注受付前」「受注受付済」「受注確認中」「受有キャンセル済」といったレコードを持たせておき、受注テーブルからIDを参照するようにします。状態に変化があったときは、受注テーブルで参照しているIDを更新します。

### このパターンの使いどころ
- **現在の状態だけが分かればよい場合**（過去の状態は消えてもよい場合）
- **将来、新しい状態が追加される可能性がある場合**
    - データモデルを変更することなく、状態区分テーブルにレコードを追加するだけで新しい状態を追加することができる。

### このパターンの弱み
- **過去の状態を保持できない**
    - 一連の状態がいつ発生したかを管理することができない
- **同時に発生しうる複数の状態を管理できない**
    - 例えば、受注が「受付可能」かつ「受注受付済」の状態
- **現在の状態が「受付可能」のように1つしか持てないため、異なる解釈につながる可能性がある**
    - 出荷部門にとってはある意味、会計部門にとっては別の意味、というようになる可能性がある

# パターン3（Level 3）
複数の状態を持てるようにし、かつ状態の履歴を管理できるようなテーブル設計をします。

### 実装
**状態テーブル**（交差テーブル）を使用して、複数の状態を表現します。

例
```mermaid
erDiagram
    ORDER_STATUS_TYPE {
        ID status_type_id PK
        VARCHAR name
    }
    ORDER_STATUS {
        ID order_status_id PK
        ID order_id FK
        ID status_type_id FK
        DATETIME status_datetime
        DATE status_from_date
        DATE status_thru_date
        DATE from_date
        DATE thru_date
    }
    ORDER {
        ID order_id PK
    }

    ORDER_STATUS_TYPE ||--|{ ORDER_STATUS : status_type_id
    ORDER_STATUS }|--|| ORDER : order_id
```

- 受注(ORDER)が取りうるすべての状態を持つ状態区分テーブル(ORDER_STATUS_TYPE)を用意する
- 受注テーブルと状態区分テーブルを**状態テーブル**（ここでは受注状態テーブル(ORDER_STATUS)）を使って多対多で紐づける

**状態テーブル**（ここでは受注状態テーブル(ORDER_STATUS)）の各属性の説明

| 属性 | 説明 |
|-|-|
|order_status_id| ORDER_STATUSエンティティのID（PK） |
|order_id| ORDERエンティティのID（FK） |
|status_type_id| STATUS_TYPEのID（FK） |
|status_datetime| 「受注キャンセル」のような特定の時点で発生する状態の日時を記録する |
|status_from_date| 「受注受付期間」のような範囲日付を必要とする状態の開始日を記録する |
|status_thru_date|「受注受付期間」のような範囲日付を必要とする状態の終了日を記録する |
|from_date| ORDER_STATUSエンティティの有効期間の開始日 |
|thru_date| ORDER_STATUSエンティティの有効期間の終了日 |


### このパターンの使いどころ
- **状態の履歴を残したい場合**
    - 状態テーブル（ここでは受注状態テーブル(ORDER_STATUS)が過去の状態を保持するため履歴が残る
- **状態管理に高い柔軟性が求められる場合**
    - 状態区分テーブルにレコードを追加するだけで新しい状態を追加することができる。また、同時に複数の状態を持つ場合や、同じ状態が複数回発生する場合にも対応できる（例えば、受付後`Recieed`にキャンセルされて`Canceled`一度受注が終了した`Closed`受注に対して、再び受注を受け付けて`ReOpened`、再びキャンセルされた`ReCanceled`）
- **管理対象の状態が不明確でしっかりと定義されていない場合**
- **状態管理を容易にするために、すべての状態を統合したい場合**
    - すべての状態を一つの状態区分テーブルで定義すれば（すなわち、ORDER_STATUS_TYPEをSTATUS_TYPEとして受注に限定しないようにすれば）、個々のエンティティに対してそれぞれ状態テーブルを使用して状態を付与することができる。これにより、同じ構造で管理できるため、状態管理が非常に容易になる

### このパターンの弱み
- **業務ルールを表現できない**
    - このパターンでは、エンティティは複数のステータスをいくつでも持つことができ、それが意味を持つかどうかは問わない。例えば、ORDERは実際には1回しか確定しないというビジネスルールがあっても、「注文確定」というステータスをいくつも持つことができる
- **抽象的で理解しにくい**
    - 要求の収集や検証に使用するにはあまり効果的でなく、特に非技術者に説明するのには向かない


## おわりに
今回、状態管理のテーブル設計パターンを3つ取り上げました。
実は、参考文献にはもう一つのパターンが載っていたのですが、too matchな気がしたので省略しました。
気が向いたらまとめます。