とりあえず削除フラグ!? または履歴管理!? 変更履歴を持つテーブル設計

業務上で不要になったデータをどのように扱うべきか。

これに対処する一つの方法として削除フラグが考えられますが、色んな有識者の話を聞いてみると、なんとなく削除フラグって発想は褒められたものではないようです。

一応ユーザーから見るとデータが無いように見えるし、有事の際にすぐ戻せるため、一見すると安心感があるように思えますが、削除フラグの運用は常にWhere句が削除フラグだらけとなり、場当たり的なクエリでデータ不整合の危険性も増えます。

また削除フラグの制御では「だれがいつどのような変更を加えたのか」といったデータの履歴が残らないので、業務的にも不都合が生まれたり、障害対応では色々困るケースも考えられます。

では一体どう対応するのがベストなのか…

正直ベストな結論は分かりませんが、少なくとも思考停止で ”とりあえず削除フラグ” は有り得なくて、履歴テーブルなどで履歴管理する設計を考慮することが大切かなと。

削除フラグとは!?

削除フラグの代表例はboolean列で0:有効/1:無効と定義し、論理削除を行います。

削除フラグを使う理由は冒頭でも書いた理由+αで、DELETEよる物理削除よりUPDATEによる論理削除が高速なので、高トラフィックのサイトでは致し方なし!?的な思想があります。

ただパフォーマンスはチューニングで改善出来たり、昨今のクラウド系インフラはスケールアップも可能なので、本当にそこにボトルネックがあるのかは疑問ですし、また論理削除されたデータも長い期間放置していると結構なデータ量になり、これはこれで対策が必要になります。

DELETE_FLAG を付ける前に確認したいこと

SQLアンチパターン 幻の第26章「とりあえず削除フラグ」

有識者の間では一つの解決策として、フラグ以外の状態(status:有効/無効/廃止など)で対応する方法があり、もう一つ妥当な解決策としては、履歴テーブルを持つことを挙げられています。

履歴テーブルでの管理

削除フラグを使わない方法として、対象データの履歴を管理するアプローチの一つ。

変更履歴を持つテーブルの設計

こちらの記事では同一テーブル内で履歴を管理するか、別テーブルで履歴を管理するか、双方のメリット・デメリットをまとめられていますが、どちらを選ぶかは現場毎に異なる感じでしょうか。

一休さんは履歴テーブルの考え方をブログで公開されており、履歴のパターンを用途に応じてバージョンテーブル・ログテーブルに分類するなど図で丁寧に説明されています。

履歴テーブルについて

ちなみに同一テーブル内での管理をイミュータブルと呼んでおり、同社内ではメリット・デメリットを考えた結果、後者の別テーブルで管理になったようですが、自分が関わった案件では削除フラグが一番多く、あとは同一テーブル内での管理が多かったです。

イミュータブル管理

イミュータブルパターンの一例として、自分が関わった案件の設計を書いておきます。

基本的にほぼ全てのテーブル(ログイン履歴などを除く)で履歴管理が行われていました。

テーブル定義の設計としてはこんなイメージ。

データ登録時には開始日時と追加日時には現在時刻を、終了日時と削除日時にはendtime(intで表せるMAX値など)を設定すれば、履歴は残りますが、SELECT検索時では毎回Where句でUNIXTIMEを見る必要があり、まあ結構面倒ですね(;^_^

また更新時はもっと面倒で、まず一旦データを削除します。

次に履歴データを登録。

ここでようやく最新のデータを登録するので、三度のクエリ発行で処理は多い。

ちなみにイミュータブルの理由はテーブル数が多く、個別の履歴管理が面倒だったからですが、これだと頻繁に更新処理が走るとレコード数が膨大になるし、履歴に対する検索処理が重そう。

その点に関しては、こちらの文献も参考になります。

履歴を持ったテーブルの設計

とりあえず削除フラグもダメですが、とりあえず履歴を持たせるのも考え物。

なので履歴を持たせるテーブルも厳選した方が良さげです。