変更履歴を持つテーブルの設計

ある日のできごと

少し前、「ブログの記事のようなものを、履歴を残しつつ編集できるようにするにはどのようなテーブル設計が良いか？」と尋ねられたことがありました.
その時, まず思いついた（というか見聞きしたことがある方法）のは以下の様な2通りの方法だった.

• 記事テーブルにバージョン番号を持たせる方法
• 記事テーブルとは別に, だいたい同じ構造の履歴テーブルを持つ方法

こられの手法のメリット・デメリットについて, すこし考えていきたいと思います.

その1 記事テーブルにバージョン番号を持たせる方法

概要

この方法では, 記事テーブルは一つだけ用意し, 更新される度に新しいレコードを追加していきます.
主キーはidとなるが, これはサロゲートキーで, 本当の主キーは「記事グループid + verison」の複合主キーとなっています.
記事の最終更新日時は, 最新Versionのレコードのinserted_atで判断する.

この方法の良い点は, テーブルを一つしか使わない為, テーブル構造を変更する時にこのテーブルさえ気をつければ良いので運用上は楽そうに見える点である.

一方で, このテーブルを使うアプリ側から見てみると, 使いづらいところがいくつかある.
例えば, 記事の一覧表示画面を考えてみよう.
この画面で表示する記事は, 当然各記事の最新状態のものを指すわけだから, 記事テーブルから最新のVersionを持つレコードだけを抜き出してやる必要がある.
具体的なSQLとは

SELECT 
  *
FROM 
  記事 a 
WHERE 
  (group_id, version) = 
    (SELECT 
       group_id
       , max(version) 
     FROM 
       記事 b 
     WHERE 
       a.group_id = b.group_id 
    GROUP BY
       group_id)

こんな感じになると思う.
このSQL, 皆さんが普段使っているデータベースアクセス用ライブラリでは扱いやすいものでしょうか?
少なくともアクティブレコードパターンをベースにしているライブラリでは, 結構面倒なことになるんじゃないかと思います.

もっとも, この問題は上記SQLを使ったViewを作って, 一覧取得時はそのViewからレコードを取得するという方法があるので致命的な問題ではないのですがもっと厄介な問題があります.
それは, この記事レコードを参照する他のテーブルがあった場合, 記事を更新する度にそちらのテーブルのレコードも更新しないといけなくなるという問題です.

つまり, 記事テーブルを参照しているコメントテーブルがあったとしましょう。

このコメントテーブルの記事idフィールドには, 親となる記事テーブルのidが入るわけですから
親の記事更新され新しいレコードが追加されたならば, やはりこのコメントレコードの記事idも新しいレコードを参照するように更新してやる必要があります.
今はコメントテーブルのみが記事テーブルを参照しているからいいのですが, 実際はもっと多くのテーブルが参照するでしょうし, 将来的にもどんどん参照するテーブルが増えていくことは十分考えられます.
テーブルが追加される度に, 気をつけながら記事テーブルの更新ロジックを修正していくのは手間なだけでなく, うっかり修正漏れが発生するバグの原因にもなります.

ここで, 「記事テーブルのidに対して参照を張っているから問題なのであって, 記事グループidに貼れば問題ないのでは?」と考えた人もいると思います.
たしかに, 記事グループidは更新されても変更されることはないので, 記事テーブルに対する参照を変更する必要はなくなるかもしれません.
しかし, 一般的（と私が考えている）なテーブル設計では, idをPKとし, 他のテーブルからの参照はこのIDを使うという形になっており, 今回問題にしているDBでも, この記事テーブル以外の部分ではそうなっているでしょう.
それなのにもかかわらず, この記事テーブルまわりだけルールを逸脱しているのが一貫性を損ねますし, プロジェクトに新しく入ってきた人に対していちいち, なぜこの部分だけこのようになっているかの経緯を「口伝」していく必要が発生し,
そのうち失伝し, バグが発生するという未来が十分に予想されます.

まとめ

• メリット
  1.使用するテーブルが一つなので, テーブル変更時に問題が起こりにくい.

• デメリット
  1.記事一覧を取得するのが少し面倒
  2.記事更新時に, 記事のidを参照しているテーブルをすべて更新する必要がある

その2 記事テーブルとは別に, だいたい同じ構造の履歴テーブルを持つ方法

概要

この方法では, 最新の内容を保持する記事テーブルと, 更新履歴を保存する記事履歴テーブルを別々に用意して使用する.
記事テーブルには, 記事に関わるすべての最新情報を格納し, 履歴テーブルは記事テーブルの更新可能部分の履歴を保持しておく.
記事更新時は, 記事レコードを更新した上で, 新たな履歴レコードを一件追加する.

その1の方法と対比してメリット・デメリットを考えてみると, メリットはアプリケーション側からの扱いやすさが考えられます.
記事一覧を取得するにも, 特定記事の履歴一覧を取得するにも, どちらも複雑なSQLは必要にならず
アクティブレコードなどでも特に工夫なく使うことができると思われます.
また, その1であったような他のテーブルからの参照に関わる問題も発生しません.

一方でデメリットは, テーブル構造の変更を行う際には, 記事と記事履歴テーブルはセットで考えていかないと行けない点です.
この部分はその1で書いたものと同じように口伝として言い伝えられることになりがちなので
バグの原因にもなっていくでしょう.
また, 最新の記事データが, 記事テーブルと履歴テーブルの両方に重複してあるというのも
ちょっとダサいかなとも思います.

まとめ

• メリット
  1.アプリ側から扱いやすい

• デメリット
  1.テーブル構造の変更時に, 複数のテーブルに同じ変更を加えて行かなければならない

その3 記事テーブルに履歴テーブルの最新レコードへの参照を持たせる方法

概要

この問題を考えている最中に思いついた方法がこれ.
その２の方法と似ているが, 異なるのは

• 記事の情報は履歴テーブルにしか持たず, 記事テーブルには最新履歴レコードに対する参照をもたせる
  という点.

こうすることによって, その２であったテーブル変更に関わる重複を削除しつつ, その1であった記事テーブルに対する参照の問題を回避することができます.
記事一覧を取得する際も

SELECT
  記事編集履歴.*
FROM
  記事
  INNER JOIN 記事編集履歴 on 記事.最新履歴id = 記事編集履歴.id

とかくだけなので, アプリ側からの使用も問題無いでしょう.
(Viewを作ってしまうのもいいでしょう)

問題となるのは, 記事テーブルと履歴テーブルが互いに参照してしまっている点.
こういうテーブルは扱ったことはないので, イマイチどんな問題が潜んでいるか把握できていない.
相互参照という文字列がすでに不吉な予感を感じさせる.実際コワイ.

まとめ

• メリット
  1.アプリ側でも運用側でもそこそこ扱いやすい(ように思える)

• デメリット
  1.テーブル間に相互参照が発生してしまう

結局どの方法が良いのか?

評価しかねるその3をのぞいて, その1とその2の手法を比較すると, その2の方法を推したいです.
それは, 私が主にアプリ側を担当することが多いプログラマだからというのもありますが, 単純にその2の方法のデメリットがまだ影響範囲が限定されているだろうと考えられるからです.
その1の方法を選択し, 実際に何処かのタイミングでバグが発覚した場合, 他に同様のバグがないか調べるとかちょっと気が遠くなりそうですよね（普段から単体テストを細かく書いていくとかしてないならなおのこと）

もちろん, その1のデメリットが問題にならない場合もあります.
例えば, あとで集計したり分析したりしたいという目的で集めているログデータなどを保存したい場合は, 特に何処かのテーブルから参照されたりすることもないでしょうし, 最新情報だけ取りたいってのはもう集計後のデータの話になっているでしょうから, 上記の問題は問題となりませんので, わざわざデメリットを背負ってまでその2の方法を選ぶ必要はないでしょう.
（実際, この目的でつかために作られたGoogleのBigtableなどはこういう設計になっていたと思います.)

まあ, 何事も場合によるってことですね（毒にも薬にもならないような結論）