■ 1. 論理削除の問題点
- deleted_at列を用いた論理削除は導入が容易だが、運用コストが時間とともに蓄積する
- クエリのたびにWHERE deleted_at IS NULLの付与が必要で、漏れると削除済みレコードが返る
- ORMのデフォルトスコープで隠すと、削除済みを含めたい場面で抜け道が必要になる
- 同一メールアドレスが削除済みと未削除で共存できるため、ユニーク制約が機能しなくなる
- 外部キーの参照先が有効かをDBレベルで保証できなくなる
- 削除済みレコードが本番テーブルに蓄積し、インデックスや実行計画に悪影響を与える
- 根本原因はライフサイクルの異なる状態を1つのテーブルに同居させていること
■ 2. 状態表現の3つの選択肢
- 論理削除方式: users.deleted_atに時刻を入れる
- イベントテーブル方式: user_eventsに退会・復帰のイベント行を追加する
- 「いつ・なぜその状態になったか」を履歴として残したい場合に適する
- 退会と復帰を繰り返すユースケースと相性がよい
- 状態別テーブル方式: usersからwithdrawn_usersにレコードを移す
- 在籍中ユーザーと退会済みユーザーを別物として扱いたい場合に適する
- 現在の状態は「どのテーブルに行があるか」で決まる
■ 3. 状態別テーブルの設計方針
- 関連エンティティ（投稿・コメントなど）は連鎖移動させない:
- ユーザーを移動させると関連データの全てが二重化され、整合性の判断が困難になる
- 各エンティティのライフサイクルは独立して扱う
- 退会ユーザーの投稿を非表示にする要件はクエリ側のフィルタで対応する
- ID発番用の親テーブル(user_accounts)を別に置く:
- 状態にかかわらず一意IDを親が保持するため、参照先テーブルの切り替えが不要になる
- 各状態テーブルはその状態に固有のカラムのみを持ち、NULLの持ち込みが不要になる
■ 4. 排他制約の実装
- user_accounts.stateにENUM('active','withdrawn')を持たせ、UNIQUE KEY(id, state)を設定する
- 各状態テーブルに生成列stateを定義し、複合外部キーで親テーブルを参照する:
- usersのstateは常に'active'固定
- withdrawn_usersのstateは常に'withdrawn'固定
- 親のstateと異なる状態テーブルへのINSERTはFK違反となり、構造的に排除される
- state列を持たせるか否かはチームの方針による:
- 持たせる場合: DBレベルで排他制約を保証できるが、クエリで列参照が必要になる
- 持たせない場合: クエリがシンプルになるが、トランザクション規約に排他制約を任せる必要がある
■ 5. 退会遷移の手順
- 退会処理はトランザクション内で以下の順序で実行する:
- usersから対象レコードを削除する
- user_accountsのstateを'withdrawn'に更新する
- withdrawn_usersに行を挿入する
- 操作順序はFK制約の依存関係に従う必要がある
- トランザクションの分離により、外部から「両テーブルに行が存在しない瞬間」が観測されない
■ 6. クエリの利点
- INNER JOINそのものが状態フィルタとなり、書き忘れが構造的に起きない:
- 論理削除方式: AND u.deleted_at IS NULLの付与が必須で、漏れるとバグになる
- 状態別テーブル方式: usersへのINNER JOINが「在籍中ユーザーのみ」を意味する
- 退会済みを参照する場合はJOIN先をwithdrawn_usersに差し替えるだけで済む
- suspended_usersのような新しい状態を追加しても既存クエリは影響を受けない
■ 7. イベントテーブルとの併用
- イベントテーブルは退会・復帰の履歴をappend-onlyで記録するテーブル
- イベントテーブルのみで現在状態を判定すると、最新イベントの集約クエリが必要になりパフォーマンスに影響する
- 回避策として状態別テーブルとの併用が有効:
- 状態の「今」は状態別テーブルで表現する
- 遷移の履歴はイベントテーブルに保持する
- 書き忘れリスクを排除しつつ監査要件にも対応できる
- テーブル間の整合性はアプリ側のトランザクション規約に依存するため、書き込み処理はリポジトリ層などに統一する