　アプリケーションを開発するときに期間を表すデータを扱うことは珍しくない。例えばイベントの開催時間、タスクの予定時間などである。実際にイベントの開催をDBに保存する場合によく見るテーブルは以下のようなものだろう。

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    start_at TIMESTAMP NOT NULL,
    end_at TIMESTAMP NOT NULL
);

　このテーブルはCopilotが提案してくれたもので、開始時間と終了時間をそれぞれ start_at と end_at カラムで保存している。実際にみんなも現場でよく見る設計ではなかろうか。 AIが一発で提案してくるくらいには一般的な設計である。

　ただ、期間を一つのテーブルに持たせることは常にベストというわけではないということを今回は説明する。

一つのテーブルに期間を持つとき

　一つのテーブルに開始時間と終了時間の両方を持つ設計は、期間が明確に定義されている場合には有効である。つまり、開始時間と終了時間のともに必ず存在し、かならず終了することが保証されている場合である。上記の例であれば、イベントの開催時間は必ず開始時間と終了時間が存在し、イベントが終了することが保証されている。

　この場合であれば、イベントと属性として期間を持つことは自然であり、上記のテーブル設計でも上手くケースが多い。

終了時間が不明確なとき

　しかし、場合によっては終了時間が開始時に決まっていない、もしくは終了しない場合もある。例えば、タスクの予定時間であれば、タスクが完了するまでの時間は開始時に決まっていないことが多い。また有効期間のようなものでも、終了時間を決めずに無期限にする場合もある。

　このような場合に上記のテーブル設計の延長上で設計すると、以下のようなテーブルになる。

CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    start_at TIMESTAMP NOT NULL,
    end_at TIMESTAMP NULL
);

　つまりtaskにデータを登録するタイミングでは end_at にはNULLが登録される。そして終了時間が確定したタイミングで end_at にUPDATEで値を設定する、もしくは無期限の場合はNULLをそのまま設定する設計である。

　この設計は一見問題なさそうに見えるが、実際にはいくつかの問題がある。　

キャンセル仕様の追加

　上記のテーブルにタスクのキャンセルを表現させたいとき、あなたならどのようにするか？例えばタスクがキャンセルされた場合、 end_at にキャンセル日時を設定することも考えられるが、これだと理由がわからない。そこで、キャンセルを表現するために status カラムを追加することが考えられる。そうすると status='canceled' のときはキャンセルであり、 status='closed' のときは正常終了である、という設計になる。

　一番やってはいけないのは、canceled_at カラムを追加することである。そうすると end_at と canceled_at の両方に値が入る可能性があったり、逆にどちらかにしかデータが無い場合が発生し、集計クエリが破綻し、どちらが正しい終了時間なのかがわからなくなる。

statusカラムは最新の状態しか持てない

　statusカラムの注意点は最新のデータしか持てない。これは失敗から学ぶRDBの正しい歩き方の『失われた事実』でも説明されている。

　例えばキャンセルしたが、やはりリオープンしたい場合、ステータスを canceled から open にUPDATEし、 end_at をNULLに戻すことになる。この場合、過去にキャンセルされた事実が失われてしまう。

　これは status カラムがなくても、一回クローズしたタスクを再度オープンする場合も同様である。このユースケースはGithubのIssueやPull Requestでもよくあることだし、想定する必要のあるユースケースである。

　このような場合はステータスの history テーブルを別に持つ必要があるし、 status カラムは履歴の最新の状態を非正規化で持っていることと同義になる。*1 そのため、history テーブルの最新行を現在のステータスとして参照する設計に変えることもできる。そうなるとそもそも、 status_historyテーブルにステータス変更の日時をもたせた方が良い。実際にテーブルを分割すると以下のようになる。

CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL
);


CREATE TABLE task_status_events (
    id SERIAL PRIMARY KEY,
    task_id INT NOT NULL REFERENCES tasks(id),
    status VARCHAR(50) NOT NULL CHECK (status IN ('open', 'closed', 'canceled')),
    changed_at TIMESTAMP NOT NULL
);

それは本当に期間ですか？

　ここまで来ると、そもそも「 start_at と end_at を持たせて期間として扱う」という前提自体を疑う必要が出てくる。

　テーブルのステータスに依存関係があるような場合、例えば下書き→承認→公開のようなワークフローがある場合。このようなときにステータスの順番に制約を持たせたい場合もある。この場合は以下のようにステータスのイベントごとにテーブルを分割することも考えられる。

CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL
);
-- 下書き
CREATE TABLE task_draft_events (
    id SERIAL PRIMARY KEY,
    task_id INT NOT NULL REFERENCES tasks(id),
    drafted_at TIMESTAMP NOT NULL
);
-- 承認
-- 下書きに対して承認イベントが発生するため、task_draft_eventsを参照
-- task_idで必ずユニークになるように制約をつける
CREATE TABLE task_approve_events (
    id SERIAL PRIMARY KEY,
    task_id INT NOT NULL REFERENCES tasks(id),
    draft_event_id INT NOT NULL REFERENCES task_draft_events(id),
    approved_at TIMESTAMP NOT NULL,
    UNIQUE (task_id)
);

CREATE TABLE task_close_events (
    id SERIAL PRIMARY KEY,
    task_id INT NOT NULL REFERENCES tasks(id),
    status VARCHAR(50) NOT NULL CHECK (status IN ('closed', 'canceled')),
    closed_at TIMESTAMP NOT NULL
);

　このようにすれば、特定のステータスの順番に制約を持たせることができる。これが最初の設計のようにtaskテーブルに start_at と end_at を持たせる設計だと、ステータスの順番に制約を持たせることが出来ず、データの整合性が保てなくなる。 MermaidでER図を書くと以下のようになる。

erDiagram
    TASKS {
        INT id PK
        VARCHAR name
        TIMESTAMP created_at
    }
    TASK_DRAFT_EVENTS {
        INT id PK
        INT task_id FK
        TIMESTAMP drafted_at
    }
    TASK_APPROVE_EVENTS {
        INT id PK
        INT task_id FK
        INT draft_event_id FK
        TIMESTAMP approved_at
    }
    TASK_CLOSE_EVENTS {
        INT id PK
        INT task_id FK
        VARCHAR status
        TIMESTAMP closed_at
    }
    TASKS ||--o{ TASK_DRAFT_EVENTS : has
    TASKS ||--o{ TASK_APPROVE_EVENTS : has
    TASKS ||--o{ TASK_CLOSE_EVENTS : has
    TASK_DRAFT_EVENTS ||--|| TASK_APPROVE_EVENTS : references

おわりに

　期間を表すデータをテーブルに持たせる場合、開始時間と終了時間の両方を持たせる設計は一般的であるが、終了時間が不明確な場合や、状態遷移がある場合には注意が必要である。まず終了時間カラムや範囲をもたせるときにNULLを許容する場合はまず一度立ち止まり、そもそも期間として設計することが適切かどうかを検討すること。多くの場合、深ぼるとテーブルを分割した方が良いケースが多く、結果としてデータの整合性が保たれ、クエリもシンプルになることが多い。

　テーブルを分割する場合に、期間の対象となるイベントやリソースに対してどのような状態があるのかを洗い出し、状態ごとにイベントテーブルを分割することを検討すると良いだろう。まずは細かく分けて設計し、その後に必要に応じて物理設計の際にパフォーマンスなどと比較しながら統合を検討するのが良い。

おまけ

時間枠の集計が難しい

　そもそもテーブルを分割したとしても、しなくても、時間枠の集計は難しい。

　これは例えば、ある期間に重複しないタスクを登録したい場合である。そもそも大前提としてこのような時間枠の集計は難しい。

soudai.hatenablog.com

時間枠の扱いはSQLに限らず、プログラミングの題材として難易度が高い。特に重複と含有が複数のパターンの場合、ロジックが複雑になり、バグの温床になりやすい。これは『リーダブルコード』でも、「8.5 例：複雑なロジックと格闘する」でこの問題が取り上げられている。

　実際に冒頭のテーブルで end_at にNULLがある場合は無期限のタスクとして扱う場合はどうなるだろうか？ NULLは比較演算子で扱えないため、WHEREの範囲で絞ることはできない。そこでまずはNULLを特定の未来日時に変換することになる。

--MySQLの場合は
SELECT *
FROM tasks t1
WHERE NOT EXISTS (
    SELECT 1
    FROM tasks t2
    WHERE t1.id <> t2.id
    AND t1.start_at < IFNULL(t2.end_at, '9999-12-31 23:59:59')
    AND IFNULL(t1.end_at, '9999-12-31 23:59:59') > t2.start_at
); 

-- PostgreSQLの場合
SELECT *
FROM tasks t1
WHERE NOT EXISTS (
    SELECT 1
    FROM tasks t2
    WHERE t1.id <> t2.id
    AND t1.start_at < COALESCE(t2.end_at, '9999-12-31 23:59:59'::timestamp)
    AND COALESCE(t1.end_at, '9999-12-31 23:59:59'::timestamp) > t2.start_at
);

　もう難しくなってきた。実務ではアサインされた担当別だったり、プロジェクト別だったり、ステータス別だったりと条件が増えることが増えるため、より難易度が高くなる。

　前述の記事にあるとおり、PostgreSQLの範囲型はこの問題を解決してくれるため強力な選択肢ではあるが、全てのDBMSでサポートされているわけではないため、別の考え方が必要になる。合わせて覚えておきたい。

startの対はstopかendか

　ClaudeもChatGPTも全てstart_atとend_atで設計することを提案してくるが、命名としては本来は期間を表す場合はfromとtoの方が適切であるし、beginとendの方が自然である。 startならstopの方が対になるし、期間を表すならfrom-toやbegin-endの方が意味が通りやすい。

　と毎回レビューなどで指摘しがちであるが、あまりにもstartとendが浸透しているのでこれは「全然大丈夫」のような本来は誤用だったが慣用化したといえるのかもなぁと思ったので、思いだけ書いておく。

*1:更新漏れや不整合が起きるリスクを常に抱える