読者です 読者をやめる 読者になる 読者になる

そろそろ履歴データについて真面目に考えてみていいんじゃないの

WEB+DB PRESS Vol.75の「理論で学ぶSQL再入門/履歴データとの上手なつきあい方」が面白かったと感想を書こうと思っていたらもうVol.76が出そうなのでいい加減慌てて書きます。

さてこの記事では、リレーショナルモデルが苦手とするデータ構造の1つとして履歴データを挙げています。

もしかすると「履歴データ」であるということを気づかずにデータベースの設計、クエリの記述をしたことがあるかもしれません。

この記事ではショッピングサイトの価格表を例としています。 価格表が常に現在の価格のみを扱うのであれば問題ありませんが、ある期間に価格を変えたことも価格表に含めるのであればそれは「履歴データ」となります。記事から一部引用するとこんな感じ

item price start_date end_date
懸垂マシーン 18000 2010-01-01 2011-12-31
懸垂マシーン 20000 2012-01-01 2013-12-31

また、俗に論理削除などと呼ばれる「削除フラグ」の導入も、ある意味「履歴データ」の一種といえるかもしれません。 個人的には論理削除はアンチパターンっぽいと思っているのですが、多くのシステムで採用されています。 伝統的なシステムでは、期末処理のような運用で論理削除に伴う問題を解決してきたのではないかなあと想像します(そんな色んなシステムに関わった経験があるわけではないですが)。

このようなデータがどのように問題を引き起こすかについては、WEB+DB PRESS Vol.75をご覧ください。 簡単にまとめれば、1つのテーブルに現在の状態と過去の履歴という2種類の事実が格納されている、外部キー制約がめっちゃ使いづらくなるというところが欠点です。

以降は記事からちょっと離れた話題です。

期間データ型

商品の価格がある期間においてこの値であった、という事実を表すには、商品と期間の組み合わせが一意にする必要があります。商品はIDなどを使うとして、開始日~終了日をもつ期間を一意にするには、その期間が重なっていないことをあらわせなければなりません。 後述するSnodgrassの本ではPERIOD型というデータ型とその演算子が登場します。

PERIOD '[1997-01-01 - 1997-12-31)'

括弧が釣り合っていないのは打ち間違いではなく、その値を含むか否かを表しています。上記の例は1997/01/01~1997/12/31の直前までを表しています。

PostgreSQLには、より汎用的な期間を表す範囲型があります。

2つの時間軸

さらに問題を複雑にするのは、データには2つの時間軸が存在しているということです。すなわち、その価格がいつ有効であったかをあらわす時間と、DBに対する変更が行われた時間です。 後述のWikipediaの記事では前者をValid Time、後者をTransaction Timeと呼んでいます。マーチン・ファウラー先生は「時間は4次元どころか5次元だったんだよ!!」とMMR風のことをおっしゃっているようです。 過去に入力した売上データが入力ミスだったことがわかったとき、それを修正するとシステム上で一体何が起こるんでしょう。

DB2のタイムトラベル照会

使ったことは無いのですが、DB2にはタイムトラベル照会やテンポラル表と呼ばれる機能があるそうです。

基礎表に対してINSERT/UPDATE/DELETEを行うと自動的に履歴表に履歴が記録され、さらに基礎表に対するSELECT文で任意の時点でのデータを参照できるという、ちょうどGitなどのバージョン管理システムのような機能です。

このページの「5 新しい運用管理ツールとアプリケーション開発の生産性向上」のPDF58ページ目からがわかりやすいと思います(それ以外にわかりやすい説明をいまいち見つけられなかったので、そんな一押しの機能でもないのかもしれません)。 88ページ目では一例として、このようなSQLが紹介されています。

-- 2012/06/08時点で$500未満で提供される旅行名を照会する
SELECT trip_name FROM travel_sys FOR SYSTEM_TIME AS OF '2012-06-08'
WHERE price < 500.00;

FOR SYSTEM_TIME AS OFの部分が呪文で、前述のTransaction Timeにあたります。FOR BUSINESS_TIME AS OFとするとValid Timeになります。

これだけ見るとシンプルかつ強力です。しかし複数のテーブルが関連しあったシステムで、どこまで履歴を管理すべきかということを想像すると、今までとは違った設計ノウハウが必要になりそうな気もします。

Temporal Data

さてこのようなデータをどのように扱うかという話題はRDBMSの世界の内外であったようです。以下いくつか情報源を挙げますが私は英語が不得意なのでほとんど理解してません。

まず英語版Wikipedia

前述のDB2のような機能をSQL標準に入れようという試みは以前から行なわれているようです。現在のステータスについてはよくわかってません。

この分野ではRichard T. SnodgrassのDeveloping Time-Oriented Database Applications in SQLという本が有名らしく、以下のページからPDFでダウンロード可能です。

Martin Fowlerは"Development of Further Patterns of Enterprise Application Architecture"の中で、この話題をオブジェクティブな観点から扱っています。

まとめのようなもの

力尽きたのでそろそろやめます。履歴データはシステム設計をしていれば頻繁に出てくるパターンのわりに、あまり議論の対象になっていなかったように思います。率直に言えば現時点ではどうするのがベストか私にはわかりません。これをきっかけに多くの方がこの話題に興味を持っていただけたらいいなーと思っています。

WEB+DB PRESS Vol.75

WEB+DB PRESS Vol.75