jfluteの日記 このページをアンテナに追加

2012-11-16

論理削除がデータを汚している

| 02:45 |

ベクトルの違うデータ

まあ、それは事実。
ただ、履歴をそのまま残したいということも事実。
いちいち削除履歴テーブルなんて作ってられないのも事実。

# ここでの論理削除は、復活する論理削除じゃなく、
# 物理削除の代わりとして履歴のための論理削除を指します。
# (復活する論理削除って、そもそも削除とは言えないって気も...)

本来、論理削除されたデータって、
そのテーブルの定義するデータとはベクトルの違うデータ
である考えます。

でも、わざわざ削除されたデータを保持するテーブルを作ると、
それはそれで面倒なのでそのまま同じテーブルに持ったままにする。
その方が扱いが簡単なことが多いから。
削除フラグを true にするだけで済むから。

個人的には、業務上重要なテーブルに関しては、
しっかりと「削除履歴テーブル」を用意して、
本体のテーブルには常に有効なデータだけがある状態の方が、
データメンテもプログラムも遥かに楽になりますし、
なんといってもデータが安全です。
テーブルを限定すればそんなに数も多くならないし。

ユニーク制約とのトレードオフ

ポイントは、ユニーク制約が貼れるかどうか

FOOとBARカラムで業務上ユニークなテーブルがあるとして、
でも、そのテーブルに削除フラグ(DEL_FLG)があったら、
少なくともFOOとBARの複合ユニークは貼れません。
(削除も含めて、FOOとBARはユニークって仕様ならいいですが、
まあ、あんまりそういうことはないかなぁと)

FOOとBARと「削除フラグ=false」でユニークになります。
でも、FOOとBARと「DEL_FLG=true」ではユニークにならない。
なので、削除フラグをユニーク制約に含めることはできない。
「FOO=A, BAR=B, DEL_FLG=true」
のデータが大量にあるかもしれないから

ユニーク制約がないと何が困るかって、
一言でずばり「なんでも入っちゃう」ってところ。
要は「FOO=A, BAR=B, DEL_FLG=false」も、
複数入ってしまう可能性があるってこと。

そりゃ、アプリで一生懸命そうならないようにするわけですが、
実際の業務ではこんな単純じゃないわけで、
FOO, BAR, BAZ, QUX, QUUX, CORGEってカラム多かったり、
例えば、FOOの値によってユニーク性が若干変わったり、
そのテーブルに登録するアプリ(アクター)が複数あったり。

とあるすれ違いで入ってしまう可能性はなきにしもあらずです。
ふとしたときに重複データが入ってしまって、
「一件検索が二件になってエラーになっちゃった」とか。
もちろん、しょっちゅうじゃないけど、
でも確かにそういうトラブルは世の中発生しています。

スピード重視の今の時代、その辺の厳密なチェック実装に時間を
なかなかかけてられない、っていうならなおさら。
逆に言うと、少ない労力の仕組みで最低限の担保ができればうれしい。
ちゃんとみっちり仕様考えてテストすれば(お金かければ)OKでしょ、
っていう考えが通じる現場は徐々に少なくなってきています。
テストも大事ですが、一方で仕組みによる防御を加えることも大事。

エラーで困るってだけでなく、そのあとのデータメンテも大変で、
同じ落ちるにしても、せめてデータがブロックされていれば。
あわよくば、そのブロックを検知して排他制御的な処理がされてれば。
(というかそれが理想かな)

本来、ユニーク制約を貼るってのはとても簡単なこと。
ERDツールでちょっとカラム選択して制約作っておけば済む話。
実はそれだけで、運用後のメリットが享受できてるわけですね。

削除履歴テーブルなんてもの作ってられないというなら、
そこはやっぱりトレードオフ、そのリスクは背負わないといけない。
でも、リスクは背負いたくない。

「削除フラグがfalseのときだけFOOとBARがユニーク」
っていうユニーク制約が貼れたらいいのになぁって、
何人もの人から聞いた言葉ですが、まあDBMSにはないです。
かといって、トリガーでぐちゃぐちゃ頑張りたくない。

頑張ってユニーク制約

回避的なやり方をよく見かけます。

削除フラグだけじゃなく、削除日時みたいなカラムを用意して、
有効なデータには固定の "9999/12/31" とか入れて、
削除されたデータには削除した日時をそのまんま入れる。
「FOOとBARと削除日時」でユニーク制約。
アプリが有効なデータを登録するときにマジック日付を
しっかり固定で登録してくれさえすれば成立します。

回避的ってなんか後ろ向きな感じですが、
そして「うへぇ」って叫んじゃいそうなやり方ですが、
でも、やらないよりはマシかもなぁとは思います。
登録や更新が色々なプロセスから実行されて、
構造が複雑なテーブルなら、しのご言ってられないって。

その削除フラグ、どういう…?

自分もまだ思考中のテーマです。
そもそも論理削除に関しては、取り扱いが難しいと思っています。
みんな気軽に論理削除フラグを追加するけど、

o ほんとにその削除フラグ必要?
o その削除フラグはどういうときにtrueになるの?
o 検索する側は、どういうときにその削除フラグを見るの?

って、見直して欲しい場面が多々あります。
(全テーブル削除フラグとかって、ほんと憂鬱に...)

jflute流で言えば、削除フラグにはDBコメントが絶対に必須です。
単にポツンと無言で存在している削除フラグほど、
プログラマーを惑わせるものはないと考えています。

ユニーク制約の大切さ

そして...

ユニーク制約をどう貼るか!?

DB設計の中ではマニアックな要素だと思いますが、
これが運用後のメンテナンスで発揮するんですねー。
でも、貼ってしまえばあまりに当たり前のことなので、
「ユニーク制約様のおかげです」って声に出して言わないので、
やっぱりまあ地味なことには変わりはないんでしょうけど。

とりあえず DBFlute ではユニーク制約違反を、
EntityAlreadyExistsException
というクラスでcatchできるようにしています。
画面ごとに細かく制御しないにしても、
統一的に「そのデータ既にあるよんメッセージ」を出す画面に
遷移さえしていればって感じで。

注文は削除されません

…
…

ちなみにこちらの考え方、とても共感しています。
  => データの削除は非推奨 | InfoQ

…
...

追記: その後、論理削除の記事を見つけた

(2015/3/24)

DB設計するときは、
面倒がらずにしっかり考えて、
空間をデザインしていきたいものですね。

// DELETE_FLAG を付ける前に確認したいこと | Qiita
http://qiita.com/Jxck_/items/156d0a231c6968f2a474

// 論理削除が奪うもの | 泥箱的なメモ
http://dekasasaki.tumblr.com/post/69487259373

mikeshimuramikeshimura 2012/11/16 04:57 志村です。この要件については、DB上Unique制約を可能にするため、次の方法をとっています。delete flagは、int型とし、作成時 0をセットします。削除時は、そのビジネスキーでのmaxを検索し、max +1をセットします。従って、例えば最初のデータを削除すると1になり。この状態で二番目のデータを追加する時は、0にそのデータを削除する時は2になります。ユニーク制約は、ビジネスキー + delete flagで作ります。 これでわざわざ別テーブルを作らず対応していまそ。

imaginatorimaginator 2012/11/16 08:40 PostgreSQLの場合は、一意な部分インデックスで削除フラグを考慮したユニーク制約が実現可能のようですね。
以下のスライドで知りました。(ご存じかもしれませんが)
http://www.slideshare.net/syachi/ss-8808413

jflutejflute 2012/11/17 00:50 mikeshimuraさん、こんばんは
なるほど、フラグ自体にユニークにする仕組みを組み込んでしまうのですね。
実際の現場で利用されているノウハウを共有して頂けるのはとてもうれしいです!
ありがとうございます。

imaginatorさん、こんばんは
おおっと、これは知りませんでした。DBMS側で用意されているとうれしいですね。
あとで dbflute-postgresql-example で試してみようかなぁと思います。
しかもこのスライドもまたとてもおもしろいですねー。ありがとうございます。

Facebookにて、simosakoさんからコメント頂きました。
DB2にはv10.1から「タイムトラベル照会」という、
削除された過去のデータを検索できる機能が追加されたようです。
具体的な使い方とかまで追えてないですが、こういう方向性の
アプローチもうれしいものですね。ちょっとずつDBMSも現場の
定型悩みをデフォルトで解決できるようになってくれればとっ!

// IBM DB2のタイムトラベル照会機能 - Japan
http://www-06.ibm.com/software/jp/data/db2/linux-unix-windows/time-travel-query.html

jflutejflute 2012/11/17 01:04 > > http://www.slideshare.net/syachi/ss-8808413
> しかもこのスライドもまたとてもおもしろいですねー。ありがとうございます。
スライドの中身にもちょっと言及。
PKの更新は別の面で落とし穴ありそうで現場ではやりたくない感じですね。
(連番の仕組みにも左右されるかな...やはりPKは固定の方がリスク少ない)
トリガーはやっぱりいざってときにアプリでその依存度を切り離せないのが、
痛い痛いってなる可能性があるのと、やはり管理ですね。
似たようなことやるなら、Javaのテーブル対応クラスでやりますね。
しかしまあ、でもほんとよく考えられてるって感心するスライドです。

exampleexample 2013/05/12 00:44 そもそも有効Table作っときゃいいだけなんじゃないですかね?

こんなTableが有ったとして
create table Master
(
id serial primary key,
column1 text,
column2 text,
column3 text
);

こんな感じで生存しているRecordのIDだけを保持する有効Tableを用意します。
create table ActiveMaster
(
 integer references Master( id ) on delete cascade
);
Masterの検索削除を行う際は、この有効Table使って操作します。例えば削除する場合は、ActiveMasterのRecordだけをdeleteします。


ぱっと見、削除Flagと大差ありませんが、ActiveMasterに対しinsertとdeleteを行うことで追加削除ができ、やや直感的になります。また、検索時に分散Indexなんかを使わなくとも削除した項目を検索対象にしないので高速です。参照制約も活用しやすくなりますよ。

exampleexample 2013/05/12 00:49 × integer references Master( id ) on delete cascade
○ id integer references Master( id ) on delete cascade
間違いがあったので訂正です。

jflutejflute 2013/05/12 22:08 exampleさん、ありがとうございます。
なるほど、また違った角度のアプローチですね!
パフォーマンスが良さそうなのがGoodですね。
ただ、業務カラムに対してユニーク制約を貼りたいので、
有効テーブルにPKだけでなくナチュラルキーも入れたいですね。
あと壁は、テーブルが増えるってところでしょうかね、きっと。
(insert対象が増えるとか、開発者に慣れてもらうとかろ)

exampleexample 2013/05/12 23:19 insertが増えるのは確かに不便ですね。一行増えるだけですが。

一意制約についてはこんな感じですかね。
create table ActiveMaster
(
 id integer primary key references Master( id ) on delete cascade,
 column1 text unique references Master( column1 ) on update cascade
);

実際にはData量が増えてしまうのでtext系ではなくinteger系だけに制約を掛ける様に工夫します。

jflutejflute 2013/05/13 00:08 そうですねー、text系のユニークよりも、
業務的なキーだけ担保したいって感じですね。

ConditionBeanだと、基点テーブルを常に
Activeの方にしていればいいって感じかなぁ...

ActiveMemberCB cb = new ActiveMemberCB();
cb.setupSelect_Member();
cb.query().queryMember().setMemberName_PrefixSearch("S");
List<...> memberList = memberBhv.selectList(cb);
for (ActiveMember activeMember ; memberList) {
Member member = activeMember.getMember();
...
}

insert は、Activeの方も登録。
update は、気にせず (UQはcascade)。
delete は、Activeの方を消す。

購入一覧で削除会員のものは除外したいときは、
ActiveMemberのIDでIsNullかExists。