/note/social
PostgreSQLアンチパターンより
結論
何故削除フラグを使いたいのか？
- 特定のデータをエンドユーザーからは見えなくしたいが、テーブルから削除はしたくない
- 削除したデータ(を|も)検索したい
- データはログ的な意味で残しておきたい
- 誤った操作で削除してもすぐに元に戻せるようにしたい
削除フラグのデメリット
- 検索クエリが複雑になる
- ユニーク制約が使えなくなる
検索クエリが複雑になる
- 全ての検索クエリに"WHERE delete_flag=0"のような条件が必要になる
- 1テーブルだけならよいが、複数のテーブルを結合してデータを取り出す時は、全てのテーブルに対して"WHERE delete_flag=0"のような条件が必要になる
- 物理削除の場合、単なる外部結合で済む
- 処理の本質的な内容とは関係ないコードなので物凄く鬱陶しい
- 加えて、検索条件が増えるためボトルネックになりがち
検索クエリが複雑になることの対策
- 有効なデータ（削除フラグが立っていないデータ）のViewを作る
- 検索クエリが複雑化する問題は解決可能
- しかし、ボトルネックであることは変わらない
- したがって、削除フラグをインデックスに含めるという珍妙な設計を採らざるを得なくなる
- ただし、インデックスを有効利用するために検索クエリのチューニングが必要になることも
- 結論＝とにかく面倒くさい
ユニーク制約が使えなくなる
- 削除フラグ以外は同一のデータが登録される場合、ユニーク制約が使えなくなる
- 例えば、一度削除したユーザーの再登録とか？（退会ユーザーの復活ではなく、再登録）
- [NOTE]ここは仕様次第では？ という気がする
- ユニーク制約を使えない、ということは外部キー制約も使えない（特に自然キーの場合）
- 外部キー制約が使えない＝テーブル間のデータ関連性が担保できない
- したがって、データ不整合の発生を防げない
- したがって、バグの温床に...
対策
- 原則＝テーブルには有効なデータのみ残す（つまり物理削除）
- データを残したいというニーズには、削除済みデータ退避テーブルを作って対応する
- データを削除する時は、削除済みデータ退避テーブルに丸ごとコピーするという発想
- トリガーで実装するのが楽だが、トリガーは仕様変更に弱い＆Un Documentedになりがち、アプリで対応した方が良いケースもある
削除フラグの類縁
- 状態を持つフィールド全般（ユーザーステータス、課金状態など）
- 基本的にDBに状態を持たせるのは危険→DBには事実のみを保存するべき
[NOTE]
- この提言を素直に取り入れると状態毎にテーブルが必要になる（e.g.ユーザーテーブル、退会ユーザーテーブル、凍結ユーザーテーブル...）
- これはこれで面倒な気がしないでもないが...
(2016/02/26)