過去に編集したデータを復元や参照する方法

Question

タグでもある様にあDBはMySQLを使用してます。

１つ質問点がありますので、質問させていただきます。

前提・実現したいこと

PHPで削除する機能。例えば掲示板での削除機能ではデータベースでのデータを直接削除せずに論理削除機能（削除フラグをたてる）を使う事ができるとおもうのですが、投稿の編集をした際には元の投稿内容に上書きされてしまいます。
よって、復元したり参照はできません。
実際にはデータを上書きして保存せず、上書きされたというフラグを立て、ユーザーには上書きされたように見せ、かつ過去の編集履歴もすべて閲覧できる様にしたいです。

また、皆さんはどの様な方法で過去の編集履歴等を復元・参照しているのでしょうか？

#実装内容
teratailでも、質問・回答の過去の編集履歴が全て閲覧可能ですが、その様な機能を実装したいです。

試した事

どの様な処理の流れになっているのかも疑問点なのですが、自分で一度流れを書き出してみました。

・編集フォームを押すとその投稿の編集フラグが立つ。
・編集フラグが立っていればその投稿を上書きして保存する。
・編集フラグが立っていればその元の投稿を表示しない。

編集後のデータを別のカラムに格納するとして仮にユーザが５回編集したとするならその元の投稿と５回分のカラムが必要になってしまうので、その方法ではやはりないのでしょうか・・

Answer 1

少なくとも、今自分が開発しているサービスでは以下のER図のようにしてますね。
（黒塗り多めですみません。

（一応公開しても大丈夫なER図です。

わかりにくかったので実際のデータっぽく書いてみました。（大雑把です。イメージです。

Comments

[退会済みユーザー]

ご回答ありがとうございます。

提示してもらったER図により、イメージがつきやすかったです。
あくまで、原本のデータは親エンティティであって、編集履歴は子エンティティである関係性といった認識でよろしかったでしょうか？

[kyoya0819]

いえ、親自体は何も（本文やタイトルなど）データを持ちません。
すべて子が持っています。

簡単に言うと、編集による「上書き」や「更新」と言う概念が一切ない構造です。

[退会済みユーザー]

>簡単に言うと、編集による「上書き」や「更新」と言う概念が一切ない構造です。
参照先が、違うといった事だという事でしょうか？

[kyoya0819]

簡単に画像を作ってみました。

[kyoya0819]

（自分の用件ではこのDB構造が楽なのでこうしています。）

[退会済みユーザー]

とてもわかり易いご回答ありがとうございます。
誤解していた様ですが、誤解が解けた様です。

私の場合、画像でいう左には、元となる投稿データが格納されていたのですが、その様な方法もあるのですね。勉強になりました。

しかし、提示してもらったテーブル構造の場合どの様に参照するのでしょうか？
投稿IDとはまた別でIDをふる必要がある様に見えるのですが。

[kyoya0819]

今回は略していますが、1番右のテーブルにもIDは振ってあります。

参照時は右側のテーブルのID（PK, AI）で降順にし、最初に投稿IDと一致したものを取得するようにしています。

[退会済みユーザー]

なるほど。

分かりやすくご回答していただき、ありがとうございます。

[kyoya0819]

ちなみに、補足ですがその後開発が進むにつれて、さらなるスピード向上のためにこの手法ではない手法をとるようになりました。

Answer 2

ほぼ同じ構造で履歴番号を持たせたテーブルを用意しておき、対象テーブルに変更(CRUDのR以外)があったときに現在の情報を履歴情報として移動する

or

テーブルに履歴番号カラムを追加。
対象テーブルに変更(CRUDのR以外)があったときに最新の情報を現在の履歴番号+1で保存し、通常見られるのは履歴番号のMAXだけにする

お好きな方で。

Comments

[退会済みユーザー]

>現在の情報を履歴情報として移動する
ここの操作をSQL文ではどのように指定すれば良いのでしょうか？

[m.ts10806]

SELEC INSERT

[m.ts10806]

SQL一発でも不可能ではないですが無理にしようとしなくても、要は「変更しようとしているレコード+履歴番号で別テーブルにデータを追加する」ができればいいので、かんがえられるやり方でやってください。
HOWにばかり注視していては本質を見失います。
WHAT,WHYを大事に

[退会済みユーザー]

すいません。
質問が適切ではなかった様です。

正しくは、変更があった際という時の操作はどの様に指定すればよかったのでしょうか？

[退会済みユーザー]

コメントが重複してしまい、(15:06の記事を)確認する前にコメントを投稿してしまいました。

一度、「変更しようとしているレコード+履歴番号で別テーブルにデータを追加する」の部分を構築していきます。

[Orlofsky]

マスターテーブルと履歴テーブルをいっしょに設計するのが主義って人にぶち当たったことがあります。履歴を見るには便利ですが、通常のSQLはいちいちインラインビューでMAXで引っ張ってきて、、、とっても複雑なSQLになるし、パフォーマンスが悪くなったかと。
まともに動かないシステムで給料をもらうのを良しとしないわたしはさっさと降りました。

[退会済みユーザー]

Orlofskyさん
インラインビューでMAXで引っ張ってきて。という処理は複雑な様な気がします。
m.ts10806さんの提示してもらった、上の方法で実装してみます。

ただ、m.ts10806さんの提示してもらった履歴番号のMAXだけにするというのは、私が質問文で過去全ての履歴を見る事ができる機能と記述したので、おそらく履歴を見るのに便利な為その様に回答してくださったのだと思います。

[yambejp]

maxを取るのではなく、表示用フラグ（カラム）をたてたほうが楽ですね
最終投稿時に同じ文書idの既存の表示フラグを消して、
自分自身にフラグをたてる
表示するのはフラグが立っているものだけにすれば、
シンプルな構造になります

[退会済みユーザー]

様々な方法があるのですね。

yambejpさんの提示してもらった方法は別で表示用のカラムを作成するという方法なのですね。
その様な思考には至らなかったので、そちらも実装したいと思います。

[Orlofsky]

論理削除フラグなり履歴テーブルなりの両方で履歴情報も含んだ複数のマスターをJOINできる、ある程度のデータを用意してSQLを書いて実行計画を確認したり、処理時間を実測すると良いです。

[m.ts10806]

やはりデータの状態とかあまり考えないままHOWばかりに注力しようとして混乱しているように見受けられます。
まず手書きでもExcelでもいいので書き出してみては？そうやって整理することで管理しやすい方法も考えられると思います。
履歴管理はあとからの変更が難しいものなので「同じようにやりたい」だけで通せるかは要件次第です。

[退会済みユーザー]

一度Excelでやってみますね。

[m.ts10806]

要件定義、設計ができれば良いので手段は問いません。

Answer 3

文書idに対して投稿日時と文書内容を順次追加してけばよいでしょう