質問: データベースに登録された情報を変更する際、変更前の情報も確認できるようにコピーとして保存しておこうとしています。 現状は登録情報と履歴は同じテーブル posts で管理し、変更履歴のレコードだけは「parent_id」として最新のデータのIDをもたせることで区別しています。parent_id が null のものは最新の情報となります。 最新のレコードと履歴のレコードを並べて表示することでタイトルなどに変更があったことを知ることができるようにするつもりです。 テーブルが1つだけならこれでも実現できるのですが、1つのレコードは複数の画像を持つため、images テーブル側に関連するレコードの id を持たせています。 この場合履歴のレコードを見ても画像に関する情報は含まれていないため、画像に変更があったかどうかを知ることができません。 履歴を保存する際に変更前の関連画像のIDすべてをJSONなどの形で posts.images カラムに保管すれば一応変更の有無を知ることはできますがあまりきれいなやり方ではないような感じがします。 将来的に画像以外に関連するテーブルが増えていく可能性も考慮するならどういった設計にするのがいいのでしょうか? --- 追記 テーブル構造は概ね下記のようになっています テーブル posts id, title, content, parent_id テーブル images id, post_id, url
回答: > > この場合履歴のレコードを見ても画像に関する情報は含まれていないため、画像に変更があったかどうかを知ることができません。 > > > 履歴を取得するクエリを工夫すれば、 現状の設計で変更があったかどうか確認できるかと思います。 たとえば、以下のようなデータがあったとします。 テーブル 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 ``` 補足 前述したとおり履歴データ保持の設計としては現状どおりで問題ないと思います。 もし、比較する項目やデータが多岐にわたり大量件数を扱うようになるケースや、 パフォーマンスやスピードを要求される機能であれば、 (予め作成した比較結果を格納する)中間テーブルを別途作成するとよいと思いますが、 こういった中間テーブルはトレードオフで闇雲に作りすぎるとチューニングしずらくなり、 新たに機能追加する際の工数がかさむことになるので用途に応じて使い分ける必要があります。