SQLアンチパターン「健忘症的サロゲートキー」の提案
- 作者: Bill Karwin,和田卓人,和田省二,児島修
- 出版社/メーカー: オライリージャパン
- 発売日: 2013/01/26
- メディア: 大型本
- 購入: 9人 クリック: 698回
- この商品を含むブログ (46件) を見る
健忘症的サロゲートキー
「SQLアンチパターン」第3章の記述を総合すると、著者はサロゲートキーについて以下のように考えていると思う。
- 自然キーの一意性・不変性が当てにならない場合に「自然キーの変更の影響を受けないようにする」という目的でサロゲートキーを導入する。
- 自然キーの重複を防ぐために、自然キーにUNIQUEインデックスを振ることを推奨する。
- 自然キーの代わりにサロゲートキーを外部キーにする。自然キーは他のテーブルに転記しない。
以上の3つが揃った設計に「健忘症的サロゲートキー」パターンという名前を付けてみたい。
何が健忘症的か
上記の設計方針には2つ問題がある。
■自分で立てた前提を忘れている
「自然キーの不変性・一意性は当てにならない」と宣言したことを忘れて、あとで「自然キーの重複を防ぐためにUNIQUEインデックスを付けましょう」と矛盾したことを言っている。UNIQUEインデックス付けたら一意でない自然キーが書けないじゃないの。
サロゲートキーに関して、この間違いを犯している議論は非常に多い。
私自身はサロゲートキーを使わないのでどれが主流なのかわからないが、この矛盾を避けながら自然キーの重複を防ぎ得る立場はいろいろある：
- 自然キーの不変性は信じないが一意性は信じる
- 自然キーの不変性・一意性を信じるが、複合自然キーを排除してアプリを書きやすくするためだけにサロゲートキーを導入する
- UNIQUEインデックスは使わず、意図しない自然キーの重複をアプリケーションで排除する
1.は「自然キーには『桁数の拡張』や『複合キーを構成する列の追加』などの変更が発生するが、『同じキー値の重複』だけはあり得ない。一意性だけは常に保障される」と考えるということ。同じキー値の使い回しがあると破綻する。一意性も疑うのであれば3.しかないと思うが、これはサロゲートキーの導入はタダではない＝追加コストがかかること認める立場だ。
■過去の事実を復元できない
日付を属性に持たないデータについて、サロゲートキーは「自然キーの値の変更に強い」という利点を持っている。
例えば[部門マスタ]と[課マスタ]が 1:N の関係にあるとき、課マスタが部門の自然キー[部門コード]を持っていたら、部門コード体系の変更(桁数拡大など)があったときに、部門だけでなく課マスタまで洗い替えの必要が生じる。
部門マスタにサロゲートキー[部門ID]を付けてこれを課マスタに転記していれば、部門マスタ自体の洗い替えのみで作業が完了する。
問題はトランザクション＝「日付」を属性として持つデータの扱いだ。
「健忘症的サロゲートキー」パターンでは、トランザクションからマスタの自然キーを排除するので、マスタ側の自然キー値の変更に伴い、トランザクション上の日付時点の自然キー値が不明になってしまう。
前回書いた
受注明細に商品マスタのサロゲートキー[商品ID]を載せて自然キー[商品コード]を排除した場合、過去の受注の商品コードを復元できない
といった問題だ。
これは、逆に利点とされることがあるのは承知している。
「ある商品の商品コードが何回変わっても、商品IDで受注明細を検索すれば、当該商品の受注数が漏れなく取れる」とか。
しかし受注明細上の商品コードは、受注日や配送先住所と同じく、業務担当者から見えている過去の事実なのだから、勝手に変えてしまってよいものではない。
この問題を避けながらサロゲートキーを使う方法はこんな感じだろうか：
- トランザクションからは他マスタの自然キーを排除しない。受注明細なら[商品ID][商品コード]の両方を載せる
- 適用開始日・終了日を持つ[商品履歴マスタ]テーブルを導入する
「自然キーとサロゲートキーのどっちがよいか」みたいな話はもう聞きたくねえんです
私がサロゲートキーを使っていないのは、DWHの保守に仕事が偏っていることもあるが*1、それを導入したときのデータモデルを全体としてどうすべきなのかよく分からないからだ。
上に挙げた2つの問題への対応だけでも2×3＝6通りの立場ができてしまった。さらに「そんなことは問題ではない。こんなアンチパターンは認めない」という人も居るだろう。実際「トランザクション上の自然キーは過去の事実だから云々」というと、商品コードぐらい新しいのに変わってもいいんじゃないの...みたいな反応が返ってきたり、問題が起きる具体例を挙げろと詰められたりすることがある。具体例ってあんたさっき自分で「自然キーは業務に密接に関係している(から変わりやすい)」て言ってただろ。密接に関係してる業務は大丈夫なのかよ*2。
サロゲートキーの実践方法は恐らく人によってバラバラで、それを一括りにして「自然キーとどっちがよいか」みたいな話してもあんまり捗らない。
「この場合はサロゲートキーが有効で、問題点にはこのように手当てする。こっちの場合なら自然キーでも問題がなくて、...」といった、もうちょっと解像度の高い議論があればぜひ伺いたい。
