データベース: 履歴データについて
Created at Thu, Apr 9, 2015RDBで履歴データを保持する方法についてまとめました. そもそも履歴データは, リレーショナルモデルとは相性が悪いので, これが正解!みたいなものはないです.
履歴データとは?
スーパーの商品の値段のように, 時間の経過とともに属性の値が変化していくデータがあります. そのようなデータを履歴データと呼びます.
フラグを付ける
最初に一番オーソドックス?な, フラグをつけて管理する方法です.
productsテーブル
| id | name | price | active |
|---|---|---|---|
| 1 | 卵 | 200 | 0 |
| 2 | 卵 | 250 | 1 |
| 3 | 肉 | 100 | 1 |
activeなデータを取得するときは, SELECT * FROM products WHERE active = 1; とします.
この方法の利点はパッと見に分かりやすいことだと思います. しかし, この方法はバッドノウハウだとされています. 理由を以下にあげます.
- activeフラグはカーディナリティが低く効率が悪い
- テーブルサイズが時間ともに肥大化し, 検索効率が下がる恐れがある
- activeの更新は, アプリ側 もしくは, トリガーで制御しなければいけない
単純で分かりやすいですが, あまり使わないほうが良い方法のようです.
範囲を指定する
次は範囲を指定する方法です.
productsテーブル
| id | name | price | start_date | end_date |
|---|---|---|---|---|
| 1 | 卵 | 200 | 2015-03-22 | 2015-04-06 |
| 2 | 卵 | 250 | 2015-04-07 | 9999-01-01 |
| 3 | 肉 | 100 | 2015-02-01 | 9999-01-01 |
activeなデータを取得するときは, SELECT * FROM products WHERE NOW() BETWEEN start_date AND end_date; とします.
この方法も分かりやすいのですが, バッドノウハウです.
- テーブルサイズが時間ともに肥大化し, 検索効率が下がる恐れがある
- start_date, end_dateの値を決めるロジックを, アプリ側 もしくは, トリガーに組み込む必要がある
- end_dateに非常に大きな値 or NULLを入れる必要がある
NULLはRDBにとって癌のようなもので, 出来る限り除去したほうが良いとされています. そういう意味で, あまり良い方法とは言えません.
active, inactiveなデータを分割
テーブル分割をする方法です. RDBっぽい解決方法です.
productsテーブル
| id | name | price | start_date |
|---|---|---|---|
| 2 | 卵 | 250 | 2015-04-06 |
| 3 | 肉 | 100 | 2015-04-01 |
old_productsテーブル
| id | name | price | start_date |
|---|---|---|---|
| 1 | 卵 | 200 | 2015-03-01 |
この方法の場合, 頻繁に参照するであろうproductsテーブルのサイズが小さくなるので検索パフォーマンスが安定します. しかし, productsテーブルから, old_productsテーブルに移すロジックを, アプリ側 or トリガーで制御する必要があります.
upcomingテーブルを使う
上記の方法の拡張版です. 過去のデータだけでなく, 未来のデータをupcoming_productsテーブルに入れる方法です. テーブルの更新を自動的にやりたいときに有効です.
productsテーブル
| id | name | price | start_date |
|---|---|---|---|
| 2 | 卵 | 250 | 2015-04-06 |
| 3 | 肉 | 100 | 2015-04-01 |
old_productsテーブル
| id | name | price | start_date |
|---|---|---|---|
| 1 | 卵 | 200 | 2015-03-01 |
upcoming_productsテーブル
| id | name | price | start_date |
|---|---|---|---|
| 4 | 肉 | 200 | 2015-05-12 |
| 5 | 卵 | 400 | 2015-05-13 |
バッチ処理を行い, upcoming_productsテーブルの start_date が有効になったら, productsテーブルに移動します.
外部キー制約への対応
上記のようなルールでテーブル分割をすると, products.idを外部キーに指定し, 運用することが困難になります(頻繁にold_productsテーブルに移動されるため). そこで, マスターテーブルを作ってしまう方法があります.
master_productsテーブル
| id |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
master_products.idテーブルを外部キーに指定して運用します. これで外部キーの問題が解決出来ました.
まとめ
履歴データを保持する方法を紹介しました. 個人的には最後の方法が好みなのですが, idのみを保持するテーブルが出来てしまい, リレーショナルモデル的にどうなの?という気がしています. 何か他にいい方法があったらTwitterでぜひ教えて下さい. なんでもします.