■ 1. 一般的なイベント期間のテーブル設計
- アプリケーション開発で期間を表すデータを扱うことは珍しくない
- イベントの開催時間やタスクの予定時間などが該当する
- 一般的なテーブル設計は開始時間と終了時間をそれぞれstart_atとend_atカラムで保存する
- この設計はAIが一発で提案するほど一般的である
■ 2. 一つのテーブルに期間を持つ場合の適用範囲
- 一つのテーブルに開始時間と終了時間の両方を持つ設計は期間が明確に定義されている場合には有効である
- 開始時間と終了時間がともに必ず存在し必ず終了することが保証されている場合に適している
- イベントの開催時間は必ず開始時間と終了時間が存在しイベントが終了することが保証されている
- イベントの属性として期間を持つことは自然である
■ 3. 終了時間が不明確な場合の問題
- 終了時間が開始時に決まっていない場合や終了しない場合もある:
- タスクの予定時間はタスクが完了するまでの時間が開始時に決まっていないことが多い
- 有効期間のようなもので終了時間を決めずに無期限にする場合もある
- このような場合はend_atをNULLとして登録する設計になる:
- taskにデータを登録するタイミングではend_atにはNULLが登録される
- 終了時間が確定したタイミングでend_atにUPDATEで値を設定する
- 無期限の場合はNULLをそのまま設定する
- この設計は一見問題なさそうに見えるが実際にはいくつかの問題がある
■ 4. キャンセル仕様追加時の設計問題
- タスクのキャンセルを表現させたい場合の課題:
- end_atにキャンセル日時を設定すると理由がわからない
- キャンセルを表現するためにstatusカラムを追加することが考えられる
- status='canceled'のときはキャンセルでありstatus='closed'のときは正常終了である
- 最もやってはいけない設計:
- canceled_atカラムを追加すること
- end_atとcanceled_atの両方に値が入る可能性がある
- 逆にどちらかにしかデータが無い場合が発生する
- 集計クエリが破綻しどちらが正しい終了時間なのかがわからなくなる
■ 5. statusカラムの制限事項
- statusカラムは最新のデータしか持てない
- 失敗から学ぶRDBの正しい歩き方の「失われた事実」で説明されている
- キャンセル後の再オープンの問題:
- キャンセルしたがリオープンしたい場合はステータスをcanceledからopenにUPDATEしend_atをNULLに戻す
- 過去にキャンセルされた事実が失われてしまう
- statusカラムがなくても一回クローズしたタスクを再度オープンする場合も同様である
- このユースケースはGithubのIssueやPull Requestでもよくあり想定する必要がある
- 解決策:
- ステータスのhistoryテーブルを別に持つ必要がある
- statusカラムは履歴の最新の状態を非正規化で持っていることと同義になる
- 更新漏れや不整合が起きるリスクを常に抱える
- historyテーブルの最新行を現在のステータスとして参照する設計に変えることもできる
- status_historyテーブルにステータス変更の日時をもたせた方が良い
■ 6. イベントテーブル分割による設計改善
- start_atとend_atを持たせて期間として扱うという前提自体を疑う必要がある
- テーブルのステータスに依存関係がある場合:
- 下書き→承認→公開のようなワークフローがある場合
- ステータスの順番に制約を持たせたい場合もある
- イベントごとのテーブル分割設計:
- tasksテーブル（基本情報）
- task_draft_eventsテーブル（下書きイベント）
- task_approve_eventsテーブル（承認イベント。draft_event_idを参照しtask_idで必ずユニークになるように制約）
- task_close_eventsテーブル（クローズイベント。statusでclosedまたはcanceledを区別）
- この設計の利点:
- 特定のステータスの順番に制約を持たせることができる
- taskテーブルにstart_atとend_atを持たせる設計だとステータスの順番に制約を持たせることが出来ない
- データの整合性が保てなくなる
■ 7. 設計の推奨アプローチ
- 期間を表すデータをテーブルに持たせる場合の注意点:
- 開始時間と終了時間の両方を持たせる設計は一般的である
- 終了時間が不明確な場合や状態遷移がある場合には注意が必要である
- 終了時間カラムや範囲をもたせるときにNULLを許容する場合:
- まず一度立ち止まる
- そもそも期間として設計することが適切かどうかを検討する
- 多くの場合の最適解:
- 深ぼるとテーブルを分割した方が良いケースが多い
- 結果としてデータの整合性が保たれる
- クエリもシンプルになることが多い
- テーブル分割の検討方法:
- 期間の対象となるイベントやリソースに対してどのような状態があるのかを洗い出す
- 状態ごとにイベントテーブルを分割することを検討する
- まずは細かく分けて設計する
- その後に必要に応じて物理設計の際にパフォーマンスなどと比較しながら統合を検討する
■ 8. 時間枠の集計の難しさ
- テーブルを分割してもしなくても時間枠の集計は難しい
- ある期間に重複しないタスクを登録したい場合の課題:
- 時間枠の扱いはSQLに限らずプログラミングの題材として難易度が高い
- 特に重複と含有が複数のパターンの場合ロジックが複雑になりバグの温床になりやすい
- リーダブルコードでも「8.5 例:複雑なロジックと格闘する」でこの問題が取り上げられている
- end_atにNULLがある場合の処理:
- NULLは無期限のタスクとして扱う
- NULLは比較演算子で扱えないためWHEREの範囲で絞ることはできない
- NULLを特定の未来日時に変換する必要がある（例:9999-12-31 23:59:59）
- MySQLではIFNULL関数を使用しPostgreSQLではCOALESCE関数を使用する
- 実務での複雑性:
- アサインされた担当別やプロジェクト別やステータス別など条件が増える
- より難易度が高くなる
- PostgreSQLの範囲型:
- この問題を解決してくれるため強力な選択肢である
- 全てのDBMSでサポートされているわけではない
- 別の考え方が必要になる
■ 9. start/endの命名に関する考察
- ClaudeもChatGPTも全てstart_atとend_atで設計することを提案する
- 本来の適切な命名:
- 期間を表す場合はfromとtoの方が適切である
- beginとendの方が自然である
- startならstopの方が対になる
- 期間を表すならfrom-toやbegin-endの方が意味が通りやすい
- 著者は毎回レビューなどで指摘しがちである
- あまりにもstartとendが浸透している
- 本来は誤用だったが慣用化したと言えるかもしれない