SQLアンチパターン 幻の第26章「とりあえず削除フラグ」
265 likes159,469 views
SQLアンチパターン 26章「とりあえず削除フラグ」 2015/08/31 @ GMO Yours #ronsakucasual https://atnd.org/events/68902
SQLアンチパターン 幻の第26章「とりあえず削除フラグ」
- 1. 和田 卓人 (@t_wada) Aug 31, 2015 @論理削除 Casual Talks SQLアンチパターン 幻の26章 「とりあえず削除フラグ」 #ronsakucasual
- 2. 和田 卓人 id: t-wada @t_wada github: twada
- 7. 諸君は自らの経験からいくらか学ぶことがで きるという、全く愚かな考えであろうが、 余はむしろ他人の失敗を学ぶことで、自分の 失敗を回避することを好む。 ─オットー?フォン?ビスマルク Nur ein Idiot glaubt, aus den eigenen Erfahrungen zu lernen. Ich ziehe es vor, aus den Erfahrungen anderer zu lernen, um von vorneherein eigene Fehler zu vermeiden.
- 9. 0. 名前 1. 目的 2. アンチパターン 3. アンチパターンの見つけ方 4. アンチパターンを用いても良い場合 5. 解決策 アンチパターンの構成
- 11. 0. 名前 1. 目的 2. アンチパターン 3. アンチパターンの見つけ方 4. アンチパターンを用いても良い場合 5. 解決策 アンチパターン: とりあえず削除フラグ
- 13. 0. 名前 1. 目的 2. アンチパターン 3. アンチパターンの見つけ方 4. アンチパターンを用いても良い場合 5. 解決策 アンチパターン: とりあえず削除フラグ
- 14. CREATE TABLE Bugs ( id SERIAL PRIMARY KEY, bug_code VARCHAR(20) NOT NULL, date_reported DATE NOT NULL, summary VARCHAR(80), description VARCHAR(1000), is_deleted TINYINT(1) DEFAULT '0', ... ); 1/true の場合削除されていると見なす アンチパターン: 削除フラグの導入
- 15. アンチパターンのメリット(?) 簡単に元に戻せる気がする なんとなく安心 UPDATE Bugs SET is_deleted = 0 WHERE bug_code = ‘hoge' AND is_deleted = 1
- 16. アンチパターンとは何でしょうか。それは、 問題の解決を意図しながらも、しばしば他の 問題を生じさせてしまうような技法を指しま す。 ─ Bill Karwin よかれと思って裏目 に出てしまうもの
- 17. アンチパターンにより起こること 常に WHERE 句が必要 コードが削除フラグだらけ 認識の齟齬を生みやすい SELECT bug_code, date_reported, summary FROM Bugs WHERE is_deleted = 0
- 18. class Bug < ActiveRecord::Base default_scope ->{ where( is_deleted: false ) } … アンチパターンにより起こること よかれと思ってコードレベルでデフォル トを変えたらバグがゴロゴロ
- 19. SELECT bug_code, date_reported, summary FROM Bugs WHERE is_deleted = 0 ORDER BY id DESC LIMIT 1 アンチパターンにより起こること データ不整合と 場当たり的クエリの巣窟
- 21. あるエンティティ定義に、論理削除有無を設 定する属性が定義されている時点で、開発者 は『ああ、この表のデータって削除していい んだ』という暗黙の了解に思考を縛られる ─ @dekasasaki 泥箱的なメモ ? 論理削除が奪うもの http://dekasasaki.tumblr.com/post/69487259373/論理削除が奪うもの アンチパターンにより起こること
- 22. 0. 名前 1. 目的 2. アンチパターン 3. アンチパターンの見つけ方 4. アンチパターンを用いても良い場合 5. 解決策 アンチパターン: とりあえず削除フラグ
- 24. アンチパターンの見つけ方 Q: この is_deleted 列はどういう目的で必要なのですか？ A: データ上は無い事にしたいけど、実際のデータは消したく ないからです (http://qiita.com/Jxck_/items/156d0a231c6968f2a474 より) Q: なぜこのテーブルにも削除フラグが付いているのですか？ A: プロジェクトのルールで、全てのテーブルに削除フラグを 定義することになっているんです Q: この is_deleted2 というカラムは何者ですか? A: ああ、それは管理用フラグです。管理者が非公開設定にし たときに true になります。 is_deleted との組み合わせで表 示を制御します
- 25. 0. 名前 1. 目的 2. アンチパターン 3. アンチパターンの見つけ方 4. アンチパターンを用いても良い場合 5. 解決策 アンチパターン: とりあえず削除フラグ
- 26. アンチパターンを用いても良い場合 論理削除(UPDATE)は物理削除(DELETE)より も大概の場合速い 物理削除(DELETE)と天 に掛ける際に、高ト ラフィックのサイトで UPDATE ベースの解を 採用することはある ……ただし、できればフラグ以外の実現方法で
- 27. 0. 名前 1. 目的 2. アンチパターン 3. アンチパターンの見つけ方 4. アンチパターンを用いても良い場合 5. 解決策 アンチパターン: とりあえず削除フラグ
- 28. 私の経験上は、ユーザーから「論理削除」という言葉を聞いたことが ありません。 次のような要件は、聞いたことがあります ?社員が退職（転属）する ?（売掛金の回収を諦めて）売上を打ち消す ?「お知らせメッセージ」を公開日がくるまで非表示にする ?既読メッセージを表示しない ?保存期間が過ぎたアンケート結果をオペレーターが見れなくする ─ @ledsun 論理削除フラグという名の死亡フラグ - @ledsun blog http://ledsun.hatenablog.com/entry/2015/03/27/015203 解決策への糸口
- 29. 解決策1: (問題解決になっていないが)せめて削除日にしてみる CREATE TABLE Bugs ( id SERIAL PRIMARY KEY, bug_code VARCHAR(20) NOT NULL, date_reported DATE NOT NULL, summary VARCHAR(80), description VARCHAR(1000), deleted_at DATETIME, ... ); Rails の論理削除プラグインの多くがこの設計 (機械的な WHERE 句は減らないが、プラグインに書かせる) しかしカラムに NULL が入るとインデックスを使えないデメ リットがある
- 30. 解決策1: (問題解決になっていないが)せめて削除日にしてみる CREATE TABLE Bugs ( id SERIAL PRIMARY KEY, bug_code VARCHAR(20) NOT NULL, date_reported DATE NOT NULL, summary VARCHAR(80), description VARCHAR(1000), deleted_at DATETIME, ... ); Rails の論理削除プラグインの多くがこの設計 (機械的な WHERE 句は減らないが、プラグインに書かせる) しかしカラムに NULL が入るとインデックスを使えないデメ リットがある 問題解決に なっていない
- 31. 解決策1: もうちょっとマシに CREATE TABLE Bugs ( id SERIAL PRIMARY KEY, bug_code VARCHAR(20) NOT NULL, date_reported DATE NOT NULL, summary VARCHAR(80), description VARCHAR(1000), closed_at DATETIME NOT NULL DEFAULT ‘9999-12-31 23:59:59’, ... ); ドメインの言葉(closed_at)を使いつつ、加えてカラムに NOT NULL 制約を付ける(未来日のマジックナンバー)
- 32. 解決策1: もうちょっとマシに CREATE TABLE Bugs ( id SERIAL PRIMARY KEY, bug_code VARCHAR(20) NOT NULL, date_reported DATE NOT NULL, summary VARCHAR(80), description VARCHAR(1000), closed_at DATETIME NOT NULL DEFAULT ‘9999-12-31 23:59:59’, ... ); ドメインの言葉(closed_at)を使いつつ、加えてカラムに NOT NULL 制約を付ける(未来日のマジックナンバー) まだ問題解決に なっていない
- 33. We won t support soft-delete at all. If you want to implement a soft-delete alike behaviour its probably a good idea to look into the State pattern instead. ? Doctrine 2 Behaviours in a Nutshell http://www.doctrine-project.org/2010/02/17/doctrine2-behaviours-nutshell.html 解決策2: それはフラグではなく状態である
- 34. CREATE TABLE Bugs ( id SERIAL PRIMARY KEY, bug_code VARCHAR(20) NOT NULL, date_reported DATE NOT NULL, summary VARCHAR(80), description VARCHAR(1000), status VARCHAR(20) NOT NULL DEFAULT ‘NEW', ... FOREIGN KEY (status) REFERENCES BugStatus(status) ); IsDeletedフラグを使う代わりに、Dahan氏はデータの状態を表 すフィールドを保持することを提案している。 例えば、有効、中止、キャンセル、廃止予定のような状態だ http://www.infoq.com/jp/news/2009/09/Do-Not-Delete- Rails のプラグインでは AASM が便利 解決策2: それはフラグではなく状態である
- 35. 解決策3: 履歴テーブルに移す CREATE TABLE BugHistories ( bug_id INTEGER PRIMARY KEY, bug_code VARCHAR(20) NOT NULL, date_reported DATE NOT NULL, summary VARCHAR(80), description VARCHAR(1000), archived_at DATETIME, ... ); 二つのテーブルの間の整合性はトリガー等で 保つ (詳しくは『理論から学ぶデータベース実践入門』を)
- 37. アプリケーションは現実を何かの業務等々の 観点で抽象化したものであり、それが扱うデー タは事実に忠実にモデル化されたのなら残り 続けているはずなのです。現実から事実を消 し去ることは不可能。 ─ @dekasasaki 泥箱的なメモ ? 論理削除が奪うもの http://dekasasaki.tumblr.com/post/69487259373/論理削除が奪うもの 解決策4: そもそも削除も更新もしない
- 38. T字形ER手法というのをベースにしたテーブル設計をしていて、そこでかなり鍛え られたわけですが、その時にはだいたいこのような原則を叩きこまれました。 ?テーブルに状態を持たせない ?究極には機械が認識するキーと、人間にとって意味のあるデータだけのエンティティ だけですべての業務のデータを構成できる ?日付を持つデータはイベント(これもひとつのエンティティ) ?NULLのデータは絶対に持ってはならない ?テーブルはでかく作るな、小さく作れ ?テーブル同士の関連は直接持つな、関連を表すテーブルを作れ ?1:1の関連になったとしても、イベントとそれに付随するデータは分離しろ ?データが増える？金と物理で殴れ(ディスク増強しろ) ─ @mike_neck 論理削除が云々について - mike-neckのブログ http://mike-neck.hatenadiary.com/entry/2015/03/24/231422 解決策4: そもそも削除も更新もしない
- 41. 解決策5: オペミスを防ぐには 「誤った操作をなかったことにしたい、すぐ に元に戻したい」の解が無い これは難しい課題 ? 間違えにくい UI を作ったり ? 「確認画面」を用意したり
- 43. ? 「とりあえず」が思考停止 ? 全てのテーブルに削除フラグはおかしい ? 「削除」は設計不足を示す ? お客様は本当に「削除」と言っているか? ? 「フラグ」以外もある ? 状態遷移で考えるほうがマシ ? 更新/削除をしない世界もある ? それでもよく考えた末の削除フラグなら OK. まとめ: とりあえず削除フラグ
- 44. まとめ: 二つの世界 Web システムにおける RDBMS はトランザ クショナルなキャッシュとしての側面と永続 的データストアとしての両面を持っている 企業システムにおける RDBMS は企業活動に おいて発生した事実を余さず記録するトラン ザクショナルで永続的なデータストアとしての 側面が強い
- 45. ご清聴ありがとうございました