極北データモデリングスマートフォン用の表示で見る極北データモデリングこの広告は、90日以上更新していないブログに表示しています。それは論理削除の問題なのかモデリング削除フラグ(というか「論理削除を削除フラグだけで実装すること」)批判は何度も見てきたがPostgreSQLアンチパターンこれ見るともはや論理削除自体が闇扱いになってしまったようだ。闇だろうと何だろうと論理削除(というドリルが提供する穴)は要件の実装に必要なので、このへんの議論はあまりまじめに追ってこなかった。いつだったか「削除フラグはバグの温床だからやめろ」という主張を読んでおぉなるほどと思い、ではどうやって論理削除を実装するのかなと思って続きを読むと「『ほんとに削除したデータが必要ですか?』とユーザに確認して、物理削除に変えさせてもらう」と書いてあってズコーとなったことがあるが、要件削っていいなら実装上のどんな問題も消えるわけで、何かもう別世界の議論で自分の仕事には関係ないと思っていた。これだけ繰り返し批判されているからには、論理削除を正しく使う方法なり条件なりを明らかにしておかないと不安だ。最近話題になった上記プレゼン資料(以下「資料」)を導きの糸にして、論理削除の何が問題なのかを理解したい。資料は論理削除の3つの問題点を指摘している。パーティショニング「削除フラグはクエリを複雑にする」という指摘には、以前から「生きているデータだけ切り出すビューを作ればいいんじゃないの」と思っていたが、資料のP-45以降にそれではダメだと書いてあった。それらのビュー同士を結合するクエリはパフォーマンスに難がある(=いらないデータを物理削除したテーブルを参照するより、遅い)パフォーマンス問題はインデックスやマテリアライズドビューで解決できない論理削除データの件数は増え続けるので、問題は悪化する一方になるとのことだが。商用DBであればパーティショニングがサポートされているので、削除フラグのON/OFFでパーティションを割ったテーブルに、生きているデータだけ切り出すビューをかぶせれば、指摘されている問題は解決する。死んだデータが0件から10億件に増えても、生きているデータだけを抽出/結合する速さは変わらない。PostgreSQLも(かなり変わった形で)パーティショニングをサポートしているので、同じことができるような気がするが、資料では検討されていない。PostgreSQLには「パーティションをまたがるUNIQUE制約が作れない」という制約があるらしいので、後段の議論との整合性で論外とされたのかもしれないし、全然関係ないかもしれない。「クエリが複雑になり、パフォーマンスも下がる」というのは、自由にパーティションを切れないDBMSに言えることであって、論理削除自体の問題ではない。当該資料はPostgreSQLのアンチパターンなので「削除フラグはダメだ」という結論でよいかもしれないが、一般論と誤解してはならない。Null-Key本来、論理削除でキー重複が発生することはない。論理削除とUNIQUE制約は普通に両立する。論理削除の概念を「データの指す対象が削除(=社員なら退職、ユーザなら退会、商品なら廃版、...)されたら、削除フラグを立てること」だけでなく「データの更新履歴をマスタテーブル本体に累積し、過去の履歴には削除フラグを立てること」にまで拡張すると、キー重複が発生し、UNIQUE制約との兼ね合いが問題になる。論理削除と更新履歴は分けて考えなくてはならないと思うがそれは置いて、資料の議論に乗っかるなら、更新履歴とUNIQUE制約/外部キー制約の両立にはNull-Keyの技法を使えばよい。←ヌル・キーの2列を用意し、まず両方に同じユーザ名を設定する。データが最新版から過去の履歴に変わるタイミングで、name_NKにNULLを設定する*1。Unique制約・外部キー制約(たぶん遅延制約にする必要がある)はname_NKについて設定し、主キーは無し(か、name列+何か)にする。PostgreSQLをはじめ、大半の実装ではNULLの重複はUNIQUE制約にひっかからないので、これで問題を回避できる。Null-Keyなんて聞いたことないしそんな妙なことしたくねえなぁと思うかもしれないが、これは20年以上前から*2使われている由緒正しいテクニックなので、経験ではなく歴史に学ぶ賢者ならむげにできないはずだ。俺は使ったことないけど。?ここは資料だけ見ても意味がわからなかった。プレゼン聴かないで「1つのデータを取るために関係するtableが多すぎる」原因が論理削除である理由がわかった方は居ますか。削除済みTABLEを作る」これが処方箋になるのはどういう場合だろうか。「論理削除はUNIQUE制約が使えないから駄目だ」というからには、処方箋ではUNIQUE制約が機能しなくてはならない。削除済みテーブルに削除データを移動したら、元テーブルにUNIQUE制約は残っていても、その制約は半分死んでいる。deleted_usersに移動した退会ユーザのユーザID(=name列)の値を、usersに再度挿入されることを拒否できないから。これで問題が起きないのは、UNIQUE制約の付いたキー値を使い回せる場合だけだろう。資料の例はブログシステムなので問題なさそうだ。ユーザID=xxxxが退会したら、ブログエントリも全部削除テーブルに移動し、次にユーザID=xxxxが新規登録されたら、まったくの別人として扱えばよい。これに対して、キー値の使い回しができない場合は、「有効なデータのみを残す」アプローチは処方箋にならない。仮に「論理削除はすべて悪だ」と信じて商品マスタの削除フラグを廃止し、廃版商品は削除済み商品テーブルに移したとする。廃版商品の商品コードは、商品マスタ本体のUNIQUE制約では拒否できない。結果、廃版商品と同じ商品コードの新商品を作ることができてしまう。受注明細(本体であれ、削除済みテーブルであれ)に載っているその商品コードは、新商品を指すのか、売切れ直前の廃版商品を指すのか、もはや知る方法がない。まとめ論理削除を削除フラグだけで実装するのはいろいろ問題があるが、それは論理削除の実装方法の問題であって、論理削除自体の問題ではないような。INDEXを振った列に複数のNULLを投入できる...2007-01-25データウェアハウス体験(2)動いているデータウェアハウスを見てびっくりしたことの続き。内的・外的という考え方がある。真野氏のやり方を見て思うのだが、データモデリング手法は着実...2006-07-19ボイス・コッド正規形への分解は、結局どうすればいいのか(2)2ヶ月前に書いた1回目の続き。ファットモデルになるのか生きているうちに自然キーvsサロゲートキー問題に決着を付けたい(1)事実と真実はどう違うか。裁判官のキャリブレーションについて月別アーカイブはてなブログをはじめよう!tgkさんは、はてなブログを使っています。ブログを報告するスターをつけました引用をストックしましたストック一覧を見る閉じる引用するにはまずログインしてくださいログイン閉じる引用をストックできませんでした。再度お試しください閉じる限定公開記事のため引用できません。