歴とは
- 様々な理由からレコードの履歴を残しておく仕様
素朴な実装
- 歴番号を持たせる
- 歴番号はPKの一部にする
よく見かけるが様々な問題点がある
素朴な実装の問題点
- 最新レコードの取得にサブクエリが必要になる(最大歴番号を探す必要がある)
- INSERT時にもサブクエリが必要になる(最大歴番号+1)
- JOINキーが常に複数
とにかくSQLが面倒になるし、サブクエリは実行速度の面でも不利
代替案１：歴番号を逆順で持つ
- 最新が 0 になるようにする
- INSERTするときは、既存レコードの暦番号を+1でUPDATEしてから、最新レコードの暦番号を0でINSERT
- 利点：INSERTはやや手間がかかるが、SELECTがめちゃくちゃシンプルになる
- 問題点：歴番号をPKに含んでいる場合、PK値が変わってしまう
→ 対策：サロゲートキー(代替PK)の導入
代替案２：最新フラグ
- 歴番号に加えて最新レコードフラグを持ち、最新のものをtrueとする
- INSERTするときは、既存レコードのフラグをfalseにUPDATEしてから、最新レコードをINSERT
- 利点：INSERTは手間がかかるが、SELECTがめちゃくちゃシンプルになる
- 問題点：INSERT時に最大歴番号を取得する手間が解消されない
代替案３：歴テーブルの導入
- メインのテーブルには最新のレコードしか格納しない（歴番号は不要）
- 新しいレコードにUPDATEするときに、別テーブルに現在のレコードをコピーする
- 利点１：SELECTがこの上なくシンプルでミスが起こり得ない
利点２：レコード数が最小 - 問題点：・・・テーブルが増える、くらいか？
考え方は論理削除にも適用可能！
最後に
- テーブルを再設計できる機会は少ないと思いますが、問題点を認識しておくのは大事
- 改善の機会が訪れたときに備えて、良い設計を勉強しておきましょう
- RDBの設計パターンってほんっと面白いですよ！