自分の中でDBでの論理削除の話とかで盛り上がったので、その中で論理削除が必要になるときと、その要件に対して実際どうするか少し書いとく。
巷で流行ってるようなので波に乗っておくか、という感じだけども、その辺の話とは全く関係なし。
「論理削除を使って何したいってのがあると思うけど、他の方法で良いのでは」といったことを言いたい感じ。
DBで論理削除を要求する要件
知ってる範囲だと、以下の2つ以外に聞いたことはない。
- ログとして残したい
- 操作をなかったことにしたい、元に戻したい
1つ目はまだわかるが、2つ目は論理削除を使う理由にならない、といった感じ。
(最初に)自分は論理削除はあり得ないよ派
論理削除という言葉を使っているときは、まずは要件を聞き出す。そうすると、それって「履歴」と「状態」として持つべきでは、となると思う。
「履歴」と「状態」とあるけどそれを「論理削除」という1個でうまくやろうとしてるのでは。
「だからなんだよ」とか「そうだよ」というとそうなんだ、という感じになってしまう。どっちでもできるからね。
でも論理削除は言葉が良くない。「履歴」や「状態」ほど目的が明確じゃない。開発者の間でもこの情報はどう扱っていいか困る。
長くなりそうなので手短にまとめると、論理削除のこういうところが嫌です。
- 名前から目的が明確にならない
- バグの要因の一つ (認識の祖語を生みやすいし、SQLを注意して書かないと実装バグを起こしやすい。)
- テストするにも論理削除状態のデータを1個置いてテストするようなことをしないといけない。
なので、自分は論理削除はあり得ない派。
でも「論理削除」を使うなとは言えません。実際その仕組みでうまく回ってお金を生み出して動いているシステムがあるからです。
会社や客先でヘコヘコして仕事してる自分なんかより、論理削除はよっぽど金になっています。
ログとして残したい
削除操作を受け付けたのでレコードを削除する、というのは普通。でも、いつ、どういう状態で、なにした？って情報は必要になることが多い。(おもにバグ調査)
そのときは論理削除でデータ上は削除扱いだけど、運用で見える形になってるとありがたいことがある。
ただし論理削除に慣れてきてしまうと、本当に削除していいのか判断ができなくなり、とりあえず論理削除を設計に盛り込むようになる。
他のシステムもやってるしーみたいなノリで入れ始める。
論理削除を使い始めると、以下の情報もほしくなるので、たいていレコードは大きくなる。
- いつ論理削除されたか
- 誰が論理削除したか
方法: 論理削除を管理するカラムを追加し、BOOLEAN値で持たせ、DELETEではなくUPDATEする
0なら未削除、1なら削除済み
- 利点
- わかりやすい論理削除
- 欠点
- SELECT文の条件で意識しないといけない(この操作のときはチェックすべきなのか否か)
- 複数の状態持たせなくてよいのか？(その時は出てこなくても、しばしば出てくる。)
欠点が大分致命的で、人為的ミスを起こしやすくする。
採用する場合、しっかりテストしましょう。
方法: 論理削除を管理するカラムを追加し、数値で持たせ、DELETEではなくUPDATEする
0なら未削除、1ならユーザ削除、2なら管理者による削除・・・という感じで意味を持たせる。
ただし、0, 5, 6のときは未削除だとか、この操作のときに3である場合は未削除とする、と判定しなければならなくなる悲しい事は起きないようにすること。*1
- 利点
- どういう理由で論理削除されたかわかる！
- 欠点
- SELECT文の条件で意識しないといけない
論理削除、bool値じゃなくて数値で持たせたら便利なんじゃ・・・！って思った場合、まず状態遷移にその状態を加えることを検討してほしい。
方法: 論理削除はせず、履歴テーブルで変更を履歴として保持する
トリガを仕込んでDELETE時に別テーブルにINSERTする方法。
- 利点
- SELECTで論理削除を意識しなくてよくなる
- 欠点
- 「トリガは遅い」派がいると徹底抗戦しなければならない。
- 事実、UPDATEより遅いことが多い。が、ベンチマークや要件をきっちり整理すると、だいたい要件を十分に満たせるのでこれを突きつけると撃退できる。
- ものぐさには2つのテーブルを行ったり来たりする気力はないかもしれない。(自分はそう思います)
- 「トリガは遅い」派がいると徹底抗戦しなければならない。
番外編: DBでログを持たない
この発想に至るのは「ログは1か所で管理しろ」という点に尽きる。つまり、DBはログを管理する場所ではない。
テキストベースのログとDBの論理削除されたレコードのタイムスタンプ比較して多分このレコードはここの操作にあたるな？とアタリをつけて調べるのは辛い。
ログは大量に出るので時系列で追おうとすると、途端に辛くなりますが、ユーザの識別子(ID)とかをログに出すようにすればgrepすればある程度は追いやすくなるし、最近はJSONにしてDBにぶっこんで調べるというのも実用レベルで聞くし、ログはログでまとめたほうが良いのではと思ってる。
実際にやったことはないので、有用性はまだわかってないけど・・・
操作をなかったことにしたい、元に戻したい
まずテキストエディタでさえ「名前を付けて保存」した操作を「元に戻す」ことはしない。
元に戻すには間にどういう操作があり、どれを元に戻すのか、順番は大丈夫かなど、いろいろ気にしなきゃいけない。場合によっては元に戻せない操作だってある。
と、まぁこれは要件しだいなので、論理削除以前の問題でもある。
逆に要件が決まれば論理削除でできる。どれだけ大変かはわからないけど。
でもやっぱり履歴のようなテーブルを持ったほうがいいと思う。
番外編: 操作時にユニークIDを発番して、かつ、論理削除状態にする
仕事でAの操作してたらA`, A,B,C,Dの操作をしてたらD`,A`を行う、といったことをやらなければならなくて、辛い思いしてた。
そのときの思いつきレベルで、うまくいくことは全然確かめてないのだけど、こんな方法はどうかなーと思ったので。
利用者単位でデータが扱える前提として、こんな操作履歴を管理するテーブルを作る。
| 利用者ID | ユニークID | 操作内容 | 操作時間 |
例えば、利用者Aが会員登録・会員情報更新・会員情報更新・商品購入・会員解約と操作をしたら、こんな感じになる
| 利用者ID | ユニークID | 操作内容 | 操作時間 |
| 利用者A | 1 | 会員登録 | 2015/02/01 |
| 利用者A | 2 | 会員情報更新 | 2015/02/02 |
| 利用者A | 3 | 会員情報更新 | 2015/02/03 |
| 利用者A | 4 | 商品購入 | 2015/02/04 |
| 利用者A | 5 | 会員解約 | 2015/02/05 |
で、各テーブルは更新があったとき、上記のユニークIDを保持するテーブルを書く。
例えば、契約者の情報ならこう。
| 利用者ID | ユニークID | 名前 | 住所 | ステータス | 論理削除 |
| 利用者A | 1 | Ryozi | Tokyo | 登録済み | 1 |
| 利用者A | 2 | Ryozi | Nagoya | 登録済み | 1 |
| 利用者A | 3 | Ryozi | Yamanashi | 登録済み | 1 |
| 利用者A | 5 | Ryozi | Yamanashi | 解約済み | 0 |
解約をなかったことにしたい場合、契約者のテーブルと操作履歴を利用者IDとユニークID結合する。
で、解約の操作と、その前の操作のユニークIDを得る(上記の契約者のテーブルであれば、解約の操作=5, その前の操作=3となる。操作時間の順番で決まる。)
その後、解約の操作=5については論理削除する。
その後、その前の操作=3の情報を使い、新たにレコードを追加する。(論理削除だけ変えてINSERT)
| 利用者ID | ユニークID | 操作内容 | 操作時間 |
| 利用者A | 1 | 会員登録 | 2015/02/01 |
| 利用者A | 2 | 会員情報更新 | 2015/02/02 |
| 利用者A | 3 | 会員情報更新 | 2015/02/03 |
| 利用者A | 4 | 商品購入 | 2015/02/04 |
| 利用者A | 5 | 会員解約 | 2015/02/05 |
| 利用者A | 6 | 会員解約キャンセル | 2015/02/06 |
| 利用者ID | ユニークID | 名前 | 住所 | ステータス | 論理削除 |
| 利用者A | 1 | Ryozi | Tokyo | 登録済み | 1 |
| 利用者A | 2 | Ryozi | Nagoya | 登録済み | 1 |
| 利用者A | 3 | Ryozi | Yamanashi | 登録済み | 1 |
| 利用者A | 5 | Ryozi | Yamanashi | 解約済み | 1 |
| 利用者A | 6 | Ryozi | Yamanashi | 登録済み | 0 |
商品購入操作があるが、これは会員解約時に影響があったわけではないので、更新は特にしない。
結局、会員解約がどういう操作かによるが、上記の単純な例ではうまくいくのでは・・・
めんどくさくなって途中であまり考えずに書いた。多分うまくいかないと思うけど。
*1:もしそういう設計見かけたら設計者を○しても良い。実装バグで問題を起こして大混乱招いた。お偉いさんに状況説明するのも辛かった。前任者をまじで○したかった。
