データベースの設計について、最近思うところがあり備忘も兼ねて投稿します。
おそらく、誰しもが一度は目にしたことがあるものだと思います。

当方プロフィール

データベーススペシャリスト資格保有
oracleはブロンズ止まり。。。
主に業務（バックエンド）系のシステムの設計、開発をしています。

導入

当方、不注意が多くミスが多いです。私のようなかわいそうな人間を生まないために開発しやすい、ミスが起きにくい設計について考えることが多いです。
テーブルのカラムは、必要な列をしっかりした名前、しっかりした定義とすることを旨としています。今回は、論理削除フラグについて考えました。

前提

データには主にマスタ系とトランザクション系の二つに分かれます。
今回は、マスタ系のテーブルについて考えます。
詳細は省きますが、トランザクション系で論理削除フラグはありえないと考えています。

結論

業務設計をきちんと行えば、論理削除フラグの設計に至ることは少ない。
よって、安易に定義するものではない。

私が論理削除フラグに持っている印象
危ない。危険が多い項目だと考えています。
一方、危ない割には当たり前のように見かける考え方な印象があります。
なぜ、危ないかは論理削除フラグのデメリットで説明します。

論理削除フラグのメリット

• すぐに復旧できる
• 外部参照先に影響を与えないため安心して削除できる
• 処理速度が速い

論理削除フラグのデメリット

• 開発時に参照するプログラム側で考慮が必要
• 外部参照先で論理削除フラグについて考慮する必要がある
• マスタが汚れる
• 論理削除フラグという名前の抽象度が高すぎてどういう状態か分かりにくい

メリットについての深堀

すぐに復旧できる

削除に関わらず、マスタがころころ変わることはないです。
ましてや消えたり、復活したりを繰り返すようなことは業務そのものに問題があると考えます。

代替案

削除時にトリガー等でバックアップテーブルに保存しましょう。
もしくは、ＤＢそのもののバックアップから復旧しましょう。
マスタであれば、前日のバックアップから十分復旧できるでしょう。

参照先に影響を与えないため安心して削除できる

論理だろうが物理だろうが、安易に削除しても良いのでしょうか。
間違えて登録したのであれば、参照しているトランザクション系のデータも
削除なり修正が必要ではないでしょうか。

代替案

無いです。
マスタの削除は慎重に行いましょう。

処理速度が速い

DELETEと比較しUPDATEの処理が速いということでしょう。
ざっと調べてみたら事実のようです。

代替案

この手の話はよく聞きますが、まずパフォーマンスの要件に違反するかを検証しましょう。違反しなければ、DELETEしましょう。
もしくは、オンライン処理で論理削除フラグを立てて、バッチでまとめて削除する。

デメリットについての深堀

開発時に参照するプログラム側で考慮が必要

外部参照先で論理削除フラグについて考慮する必要がある
プログラムからリードするときにも、外部参照する際にも論理削除フラグについて考慮が必要になってきます。論理削除フラグの考慮漏れはよくある不具合のパターンです。
また、往々にして設計書への記載が無いことが多いです。論理削除フラグが当たり前のように存在しているため、プログラマーへの判断に委ねられていることが多い印象を受けます。また、機能要件なのか非機能要件なのか微妙な立ち位置が設計書への未記載を増長させていると想像しています。愚痴になってしまいました。。。

マスタが汚れる

参照が不要になった死んだデータがマスタに残留します。
常に死んだデータについて、意識が必要となってしまいます。

論理削除フラグという名前の抽象度が高すぎてどういう状態か分かりにくい

とりあえず消したことにしたい。ということなんでしょう。
いっそ、「間違えて登録したので消しましたフラグ」とかにしてほしいです。

なぜ業務設計をきちんと行えば論理削除フラグの設計に至らないのか

マスタの状態が変わるのであれば「ステータス」という列がしっくりきます。
例えば担当者が着任した時には担当者マスタに「1:着任」。
退職等で離任した時には「2:離任」。退職と離任を分けたければ「3:退職」等を
設定すればよいです。
もしくは、退職済みフラグを設定するか。ただし、ステータスと同時に設定すると
現在の状態を表す「ステータス」と退職済みフラグの同期が必要なので注意が必要です。
というか設定すべきではないのでしょう。ステータス列が存在する時点で論理削除フラグは不要なものとなります。また、機能要件となるため設計書にも記載されるようになります。
以下だとどちらが分かりやすいでしょうか？
-退任した担当者は論理削除扱いとし論理削除フラグを参照しプログラムからは参照しない
-ステータスが（退任）のユーザーは一覧に表示しない

最後に（最悪な論理削除フラグ）

CHARやNUMで定義されていると最悪で、「0」か「1」どちらが生きているのか
おびえながら、生きていく必要があります。
「2」や「3」が出てきたらもはや何も信用できません。
そして、未だかつて設計書やテーブル定義にきちんと明記されているものを見たことがありません。

どうしても設定したい時は

名前が「IS_DELETED」で型がBooleanであればまだいいです。
見ただけで「True」が「削除済み」であることが分かります。
そして、設計書に明記しましょう。書かなくても分かるだろうは怠慢です。

本当の最後

とはいえ、根本的な論理削除フラグの設計動機が「サボり」なので、考慮されることはマレです。。。
論理削除フラグに対するアンチテーゼとなってしまいました。
私の意思では論理削除フラグを設定することはまず無いです。

以上です