DBのCRUDでUとDは必要ないという話を聞きましたが具体的にどう実装するの？

Question

"CRUD is Dead"なんて言われてる方もいらっしゃるようでCRUDのUとDを禁止する流れが存在するようです。

以下のようなサイトでも言われているように、CRUDのUとDを利用せずデータをログのように扱う方法は関連するデータ（特に履歴）が失われないために好感が持てました。

http://tanakakoichi9230.hatenablog.com/entry/6715376804
http://qiita.com/Jxck_/items/156d0a231c6968f2a474
http://mike-neck.hatenadiary.com/entry/2015/03/24/231422

現在MySQLを利用してウェブサービスを作成しようと考えているのですが、実際にUとDを利用しないDB設計をするにはどうすれば良いのかというところでつまづいてしまいました。

自分で考えたものは5つあります。

①
createdカラムを挿入し、通常Updateしていた部分をInsertで代用する。
この際、更新されないデータも全て最新のデータを複製する。
データ取得時には最新のデータを参照する。

②
createdカラムを挿入し、通常Updateしていた部分をInsertで代用する。
この際、更新されないデータにはNULLを指定する。
データ取得時にはカラム毎のnullではない最新データを参照する。

③
createdカラムを挿入し、通常Updateしていた部分をInsertで代用する。
この際、更新されないデータにはNULLを指定する。
また、最新データ参照用テーブルを作成しトリガーを利用してInsertがあった場合に
自動的に最新データ参照用テーブルの該当するIDのデータをUpdateする。
もちろん、データ取得時には最新データ参照用テーブルを利用する。

④
アクションログ用テーブル（action_id, target_db, target_column, value, created）を作成し全てのデータ操作をログとして残し、ログデータが追加された場合にトリガーを利用して最新データ参照用テーブルをupdateする。（valueの型をどうするべきかは不明。おそらくTEXT型）

⑤
アクションログはRDBではなくログデータとしてディスクに書き込み、RDBは通常通りCRUDする。また、アクションログを書き込むプログラムをモジュール化し、dataModule.set(id, name, value);などの関数を利用してデータを書き込む際には自動的にログファイルとRDBの両方が書き換えられるようにする。（そのモジュールにログファイルからDBを作成できる機能などあると良いかも）

①は無駄に容量を食うのであまり良くないと思っています。②に関しては「NULLは使うな」と良く言われてるのでそれがひっかかります。あとデータを取得する際に複雑なクエリを発行することになりそうです。③はNULL利用が不安なことは変わりありませんが、データ書き込みと取得で異なるテーブルを使うので複雑クエリ問題は解消しそうです。④この方法が良いんじゃないかと個人的に思っていたのですが、value型をどうするかという問題が残っています。またtext型を利用した場合、大量のログがtext型でinsertされることを考えると少し不安になります（全く問題ないのかもしれませんが）⑤については最終的にこれが一番良いのではないか？と思って現在検討している方法になります。しかしこれだと今までずっと"CRUD is Dead"の考え方できたのに、結局CRUDでDB操作しちゃってますね（汗。しかしログファイルを取ることで、何かあった時にはログファイルからDBを復元したり、調査が必要になった時にログファイルを確認したりできるのでCRUDのUとDは使ってますけどUとDを使わなかった時のメリットは得ることができているといった感じでしょうか。

このように幾つかの方法を考えたのですが、そもそも一般的なベストプラクティスがあるのではないか？と思いましたので是非そういった参考にできるサイトや、俺だったらこうするという意見がございましたら教えていただきたいと思っています。

よろしくお願いいたします。

Comments

[yuba]

MySQLに限ったトピックではないので「SQL」タグがよろしいかと思います。

私も数年来このテーマを考えていますがまだうまい実装にたどり着けていないのでいい回答が付くのを期待しています。

[Panzer_vor]

興味深いテーマですね。履歴管理での一番の王道パターンは「履歴管理したいテーブルに対応した履歴テーブルを作成する(品目マスタと品目変更履歴テーブルみたいな感じで)」かなと思われますが、
これより優れた解があるのかは気になるところです。

後、原則削除を行わない場合はデータとしては増え続けて要領圧迫されるので、
定期的なデータ退避処理は必要不可欠となりますね。

[退会済みユーザー]

NULLに更新した場合と更新しなかった場合の判別をどうつけるか。

Answer 1

CRUD is Dead がよくわかってないのですが、なんとなく論点が違うような気がして書き込んでみます。
CRUD is Dead ってつまり、「外部キーを結合してデータを取得した時に、最新のスナップショットで見える必要は無いんじゃね？」って話かなと思いました。

少し例を考えて見ます。

「社員レコードには所属組織という属性があり、これは組織レコードへの外部キーです」ってなったときに SELECT JOIN すれば社員の所属組織名や組織のボスの社員IDが取れるというデータベースを作るとします。
ここで、とある組織の組織名を変更するケースを考えます。

• 従来のデータベース設計では、組織レコードの組織名フィールドの値を変更するだけですみます。そして、その後は社員のIDからその所属組織を取ると、最新の値が見えるわけです。
• 更新削除なし設計では、操作として新しい組織名を持つ組織レコードを作成します。そして所属している社員についても新しいレコードを作成し、その所属組織フィールドには新しい組織レコードのIDを格納します。

ここまでですと、後者の方は処理量が増えているだけで何のためにそんなことをしてるのかわかりませんが、社員マスタを参照するアプリのことを考えると、そうでもなくなります。
たとえば、出張命令ワークフローのアプリの出張命令書レコードに申請者IDや承認者IDのフィールドが社員レコードへの外部キーであった場合、 select join して、その人たちの所属組織名をひっぱると最新の組織名が見えて良いのでしょうか？出張命令書のような文書であれば、その命令書がワークフローを流れた時点の組織名がとれがほうが良いでしょう。したがって、従来型の設計の場合は、出張命令書レコードに組織名フィールドを持ち、命令書が発行された時点の組織名をコピーしておくことになるでしょう。しかし、更新削除なし設計であれば、このフィールドも処理も不要なわけです。これが更新削除なし設計のメリットではないでしょうか。

こんどはデメリットになるケースを考えてみましょう。利用者毎に自分と親しい社員だけを登録するアドレス帳のアプリを考えてみましょう。アドレス帳レコードは社員レコードへの外部キーだけを持てばよいでしょう。従来型のデータベース設計であれば、SELECT JOIN で常に最新のデータが取れるので問題ありません。しかし、更新削除なし設計の場合は、社員マスタが更新される毎にアドレス帳レコードを更新する必要があります。アドレス帳のアプリは、常に社員マスタの更新通知を受け取って必要に応じてアドレス帳を保守する必要があるわけです。

更新削除なし設計だと、あきらかにシステム間の結合度が上がって不利なようにみえますが、いや、そんなアプリのほうが少ないだろ！っていうのが CRUD is Dead ってことかなって思いました

技術的解説
ここからは、質問いただいたのでしかたなく（ちょっと私のビジネスのコアコンピタンスに触れそう・・・）
まず、技術的にはレコードのIDを生成する方法について考える必要があります。外部キーに使うIDなので当然一意のものを生成する必要がありますが社員番号など業務上のIDとは別に生成する必要があります。このIDの生成について2案あります。

• UUID(RFC4122)を使う→分散環境でも確実に一意のIDを生成することができ、ロックマネージャなどにお伺いを建てなくても生成できるメリットが有ります
• 更新毎に文脈通番＝CSN(Context Sequencial Number) を振り出すこととし、すべての更新処理においては、まず、通番を取得し、この通番に1回の更新内の連番を追加した番号を使います→CSNマネージャンという番号を振り出すサービスが必要であるが、更新の前後関係を CSN の大小で比較できるメリットが得られる

そして、前述したように、データベースをどう実装するか以前に、システム間連携の API/SPI の仕様の問題となります。社員マスタシステムに関しては、従来であれば、社員マスタシステムに対する問い合わせ処理をAPIで実装するだけでよかったのですが、更新削除なし設計では、更新通知を受け取る SPI (Service Proveider Interface)を実装する必要があります。（すいません、詳細省略）

つまり、更新削除なし設計については、データベースのスキーマ設計以前にシステム間連携の API/SPI の仕様の問題であると思います（たとえ、それが CSV+FTP の実装であっても）。実際に更新削除をしないデータベースをMySQL などの RDBMS 上に実装しようとすると、それなりに性能問題に対する技工が必要です。それこそ、みなさんの出番です(^_^)

Comments

[hojo]

貴重な意見ありがとうございます。

なるほど、ご指摘通りデータをログ（履歴的）として扱うという観点からCRUD is Deadについて見ることしかできていなかったように思います。

おっしゃっていただいている内容をまとめると「システムが過去のデータを参照しなければならないことがあるため書き換えたり削除したりする必要ないよね」っていうことですよね？

出張命令の場合、組織名が最新のものに変わってしまった時には「あの組織、名前変わったみたいだね」で済む話かもしれませんが、これが商品だったりした場合には、購入履歴を参照した時に購入時より値段が安くなってて「もっと高値で買ったはずなのに履歴見たら安く（今の値段）なってる」なんてことが起こってしまうかもしれません。これは絶対に起こしてはならない致命的欠陥だと思います。

特に私の質問文章の④や⑤なんかは、マスタデータをログ（またはログ専用テーブル）に置き換えるという話になっていたため、余計に論点がずれていると感じたのではないでしょうか。特に⑤に関してはもはやDBから参照不能になっていますから、今回の要件からしたら論外ですね。指摘を受け、⑤は無いかな、と思い始めています。

そうなると、①②③または④の構成の中でどれがいいのか？という話になってきますが、お話の中で出てきた「新しい組織名を持つ組織レコードを作成」また「社員についても新しいレコードを作成し、その所属組織フィールドには新しい組織レコードのIDを格納」についてはどのようにレコードを追加するのがより良いのでしょうか。また、どのようにデータを取得すればいいのでしょう？ここが今回の質問の根幹だと思っています。

[mit0223]

hojoさん、質問いただいて追記してみましたが、ほとんど回答になってませんね。すみません。ただ、社員マスタシステムを更新削除なし設計にすることと、それをデータベース上にどう格納するかという話は別のレベルの話だと思います。
直感的には、①＋最新スナップショットかなと思います。

Answer 2

ざっくり考えました。

• modifiedは必要無し
• 変動しないデータは絶対必要
• updateが禁止なのでupdateが無くても困らない運用にする
• 最新の私(データ)が最強

結果下記に落ち着きました。
ソシャゲの世界ではDBロックを避ける為にあえてこのようにしていると聞きます。

死ぬほどテーブルが増えそうですが、整合性は取れてそうですね。
Railsのようなフレームワークは基本的にID管理なのでそれに従っていますが、
idを消してuser_id, createdのユニークな複合インデックスで対応出来るかと思います。

SQL文は…MySQLだと明らか死ねる未来しかみえませんね。
Viewでも作るしかないですか？
案の一つとしてご笑納ください

users テーブル

• id (AI)
• created

user_names テーブル

• id (無くても可）
• user_id
• name
• created

user_mails テーブル

• id (無くても可）
• user_id
• mail
• created

Comments

[hojo]

なるほど。

1データ1テーブルという設計で全てのデータにcreatedが紐づけられるという設計ですね？

もちろん、1ユーザの1データを取得したい場合はそのまま最新の私（データ）を取得すればいいと思うのですが、複数のデータを一括で取得したい場合は、自分の直感を信じて最新データをJOINすれば良いということになるのでしょうか。

大抵の場合複数のデータを取得することが多いと思うので、この設計の場合SELECT文のクエリが全体的に複雑になる可能性が油断できないこの事情ということになりそうですね。

場合によっては、複雑なSQLが大量に量産され、罠ワナして燃えちゃうかもしれないので究極の選択は永遠のトラウマになる可能性もある...ということですね。

意見してくれてアリガト（Thank you!）

Answer 3

テーブル設計時にレコードの推移をログとして残したい場合に、何度かupdateとdeleteの無いテーブル定義をしたことがあります。（リンク先はかなり入り組んでそうなので、さっと斜めに読んでそっと閉じました。）

あるものは、データ更新が複雑でレアケースで整合性が取れないデータ登録がされる場合があり、適切に修正するために変更履歴をとっておくという方針だったりしました。

DBのあるべき性質特性の一つに、同じ内容のデータは一箇所にしか存在すべきではないというものがあります。これを原子性と呼びます。

通常のテーブル設計では、データの更新が必要な場合に該当レコードの該当項目にupdateをする事で原子性を保証します。または場合によってはdeleteを行います。

しかし、リンクにあるように、社員テーブルの様なものの場合、deleteしてしてしまうと退職ー＞復職した場合など不都合が生じることがあります。こういった場合、例えば社員のレコードは更新毎に複製して、insert_timestampなどの項目を付与して最後のものが現在の値を示すというものです。（①の方法です）

deleteの場合は、削除レコード（deleted = true）をinsertsするなどの方法がよいと思います。

②・③のNullを使う方法は、原子性を保つという意味では有効かもしれませんが、検索時にかなり開発・ストレージ・CPU・ネットワークなどに付加をかけるので現実的では無いと思います。

④・⑤は通常のCRUDを利用するので、DB登録時にログテープルにも書き込もうということ以上の意味はないと思います。

この方法では、当然ストレージを圧迫し、DBのレスポンスを低下させます。使用する際は限定的に行うか、DBMSをバッサリ諦めファイルシステムで管理するなど思いっきった方法をとるかする必要があります。

---

追記－気が付いたのですが、私が話した実例は基本的には過去のデータを利用する場合があるからという場合なので、そもそもdeleteは許されないケースですね。

そういう意味で、Jxckさんのエントリとほぼ同意見で、updateでいけるところはそのままでいいよというのがあります。また、修正のために履歴を残していたケースですが、適切なキーを付けたログでもよかったなあと思います。

別の場合で、頻度が多くないがユーザーが参照する場合があるので履歴でとっているというケースもあります。（これには、参照画面がある場合と調査依頼が必要な場合とがあります。）

まあ、update・deleteを使わないのは、工夫の余地はあるけれど、それで影響が出ない程度のものに限定して使っていました。

Answer 4

"CRUD is Dead"なんて表現があるんですね。初耳です。

ちょっと見てみましたが、"CRUD is Dead"というのは、
「DBは履歴型テーブルだけでシステムを作ろう」
「複雑な更新・削除・運用等がある更新型テーブルよりも、
履歴型テーブルONLYで設計した方が設計・管理・運用が簡単！」
と言うところでしょうか。

DBのCRUDでUとDは必要ないという話を聞きましたが具体的にどう実装するの？

⇒必要ないなんてことはないと思いますが、
仮にNGとした場合、全て履歴型テーブルとして取り扱うため、
最新情報の取得はSQL文を全てのテーブルに対して工夫して取得する形になります。

次に、マスターテーブルですら、履歴型テーブルにするという事なので、
マスターテーブルとのJOINも難しくなります。
マスター履歴テーブルとのJOINはせず、
JOIN元のテーブルにそのままの値で含める形の方が良い。

最新情報の取得方法は色々ありますが、UとDなしだと、
主キー(AUTO_INCREMENT)やUNIQUE KEYによる最新版取得が良いでしょうね。
最新日時でも大丈夫ですが、性能が出ない場合が多いです。

⇒"CRUD is Dead"がWebシステムで使えるジャンルとしては、
静的ページに近いもの（1テーブル1レコードの情報だけで1画面の情報を全て表示する）が多いシステム、
リスト画面がほとんど存在しないシステム、
削除処理がほとんど存在しないシステム、
大容量データを取り扱わないシステムあたりでしょうか。
（例：Web契約システムあたりは使えそう）

①～⑤

⇒①～⑤の中で、かつ、"CRUD is Dead"にこだわるなら①でしょうね。
②はSQLが複雑になる上に、性能が①より出ません。
③④⑤はUを使っているので、"CRUD is Dead"ではない（？）

⇒①②はUに対応しておりますが、
Dには対応していないように見えます。
そのため、DELETE_FLGのようなものも必要でしょう。
（例：マスター履歴テーブルにおいて、今後表示させたくない項目が存在する場合等）

PS:DROP TABLEならアリと言う事なら、運用によっては、
マスターテーブル等の更新型テーブルをバッチ処理等にて、
DROP CREATE INSERTで再作成する方法もあるかもしれません。

PS2：実装方法や使える例を記載しましたが、
"CRUD is Dead"なんて言葉に惑わされないのが良いと思います。
ちゃんと設計・管理・運用できれば、更新型テーブルを用いた方が性能が出る事の方が多い気がします。

Answer 5

面白い考え方があるんですね。

現在MySQLを利用してウェブサービスを作成しようと考えているのですが、実際にUとDを利用しないDB設計をするにはどうすれば良いのかというところでつまづいてしまいました。

IoT/M2Mなどのセンシングデータを扱う場合、収集データに関しては原則Cのみとなります。
その後、データを可視化するにあたり、時系列DBであればそこそこ性能は出ると思いますが、
性能が出ない場合は可視化の為に加工したデータを作成する必要があります。これもCのみとなりますね。

ですが、本当にCだけでは、ストレージを消費し、コストの増大に繋がります。

要件には2年を経過したデータは、データベースから削除する、等の記載があります。
実際にはファイルにバックアップして更に数年の保管を行ったり、削除対象期間のデータのトレンド情報として新たなデータを保存したり色々ありますが、元の蓄積データに関してはDが必要となります。

何でもかんでもCRUDのUDは要らないというよりも、
システムは何かしらの目的を達成するものなので、大枠としてこのデータはCRを採用するのはありではないでしょうか。

先ほど説明した保存期間内のセンシングデータや履歴データ等。

会員情報を例に取ると、
１．最新の会員情報T（保存期間退会後2年等）
２．会員情報更新履歴T（保存期間X年）
というような感じです。

１は最新の情報がわかるよう、その対象ビューが扱いやすいよう、最新のものだけを入れておきます。
2は会員の変更履歴がわかるよう、その対象ビューが扱いやすいよう、要件に従ったデータを入れておきます。

会員情報だけであれば、レコード数も知れていますが、
注文履歴、観閲履歴、ログイン履歴等は莫大な量になるかと思います。

データが増えればストレージ、パフォーマンスに影響してきます。

業務、要件ありきですので、開発しやすく、応答速度の速い設計を心がければ良いと思います。