変更履歴を保存する際のデータベース設計

Question

データベースに登録された情報を変更する際、変更前の情報も確認できるようにコピーとして保存しておこうとしています。

現状は登録情報と履歴は同じテーブル posts で管理し、変更履歴のレコードだけは「parent_id」として最新のデータのIDをもたせることで区別しています。parent_id が null のものは最新の情報となります。

最新のレコードと履歴のレコードを並べて表示することでタイトルなどに変更があったことを知ることができるようにするつもりです。

テーブルが１つだけならこれでも実現できるのですが、１つのレコードは複数の画像を持つため、images テーブル側に関連するレコードの id を持たせています。

この場合履歴のレコードを見ても画像に関する情報は含まれていないため、画像に変更があったかどうかを知ることができません。

履歴を保存する際に変更前の関連画像のIDすべてをJSONなどの形で posts.images カラムに保管すれば一応変更の有無を知ることはできますがあまりきれいなやり方ではないような感じがします。

将来的に画像以外に関連するテーブルが増えていく可能性も考慮するならどういった設計にするのがいいのでしょうか？

---

追記

テーブル構造は概ね下記のようになっています

テーブル posts
id, title, content, parent_id

テーブル images
id, post_id, url

Comments

[m.ts10806]

現在のソースコードおよびテーブル定義をご提示ください。

[workr]

テーブル定義を追記しました。よろしくお願いいたします。

Answer 1

履歴はテーブルをわけてtriggerでやると効率的です
imageは何をどう管理しているかよくわかりませんが
ファイルはデバイス上に置いて情報だけDBで管理するのでしょうか？
とりあえず以下３つは必要だと思います

• ファイル名
• 投稿日時
• 削除済みフラグ

また以下があると表示がラクです

• 縦横サイズ
• mime情報
• ファイルサイズ

その他、サムネイル情報やコメントなどあれば便利ですし、運用方法によっては
アクセス制限情報なども必要でしょう

Comments

[workr]

すみません、説明に不足があったので質問に追記しました。変更を記録するのはpostsのタイトルや本文などの各カラムと、imagesテーブル（関連画像）の増減の両方となります。imagesテーブルはファイルのURLを保存しています。

Answer 2

この場合履歴のレコードを見ても画像に関する情報は含まれていないため、画像に変更があったかどうかを知ることができません。

履歴を取得するクエリを工夫すれば、
現状の設計で変更があったかどうか確認できるかと思います。

たとえば、以下のようなデータがあったとします。

テーブル posts

id	title	content	parent_id
1	first	変更前	-
2	second	変更後	1

テーブル images

id	post_id	url
1	1	apple.png
2	1	lemon.png
3	2	apple.png
4	2	banana.png

このときに以下のクエリを実行して確認します。

SELECT posts.*, images_wk.urls
FROM posts, (
  SELECT post_id, GROUP_CONCAT(images.url) AS urls
  FROM images
  GROUP BY post_id
) AS images_wk
WHERE posts.id = images_wk.post_id

するとこのような形で確認できます。

id	title	content	parent_id	urls
1	first	変更前	-	apple.png,lemon.png
2	second	変更後	1	apple.png,banana.png

---

もし url ではなく images.id で比較したいのであれば、
imagesテーブルにも同じように parent_id を追加して
以下のようなクエリに変更すればよいかと思います。

SELECT posts.*, images_wk.urls
FROM posts, (
  SELECT post_id, GROUP_CONCAT(IFNULL(images.parent_id, images.id)) AS urls
  FROM images
  GROUP BY post_id
) AS images_wk
WHERE posts.id = images_wk.post_id

補足

前述したとおり履歴データ保持の設計としては現状どおりで問題ないと思います。

もし、比較する項目やデータが多岐にわたり大量件数を扱うようになるケースや、
パフォーマンスやスピードを要求される機能であれば、
（予め作成した比較結果を格納する）中間テーブルを別途作成するとよいと思いますが、
こういった中間テーブルはトレードオフで闇雲に作りすぎるとチューニングしずらくなり、
新たに機能追加する際の工数がかさむことになるので用途に応じて使い分ける必要があります。