極北データモデリング このページをアンテナに追加 RSSフィード

2013-03-20

[][]SQLアンチパターン健忘症サロゲートキー」の提案

SQLアンチパターン

SQLアンチパターン

本書の著者はサロゲートキーに対して消極的なのだから、「サロゲートキーの使い方がおかしい」とか言うのはお門違いなのかもしれないが...



健忘症サロゲートキー

SQLアンチパターン」第3章の記述を総合すると、著者はサロゲートキーについて以下のように考えていると思う。

  1. 自然キーの一意性・不変性が当てにならない場合に「自然キーの変更の影響を受けないようにする」という目的でサロゲートキーを導入する。
  2. 自然キーの重複を防ぐために、自然キーにUNIQUEインデックスを振ることを推奨する。
  3. 自然キーの代わりにサロゲートキーを外部キーにする。自然キーは他のテーブルに転記しない。

以上の3つが揃った設計に「健忘症サロゲートキー」パターンという名前を付けてみたい。



何が健忘症的か

上記の設計方針には2つ問題がある。


■自分で立てた前提を忘れている

「自然キーの不変性・一意性は当てにならない」と宣言したことを忘れて、あとで「自然キーの重複を防ぐためにUNIQUEインデックスを付けましょう」と矛盾したことを言っている。UNIQUEインデックス付けたら一意でない自然キーが書けないじゃないの。

サロゲートキーに関して、この間違いを犯している議論は非常に多い。


私自身はサロゲートキーを使わないのでどれが主流なのかわからないが、この矛盾を避けながら自然キーの重複を防ぎ得る立場はいろいろある:

  1. 自然キーの不変性は信じないが一意性は信じる
  2. 自然キーの不変性・一意性を信じるが、複合自然キーを排除してアプリを書きやすくするためだけにサロゲートキーを導入する
  3. UNIQUEインデックスは使わず、意図しない自然キーの重複をアプリケーションで排除する

1.は「自然キーには『桁数の拡張』や『複合キーを構成する列の追加』などの変更が発生するが、『同じキー値の重複』だけはあり得ない。一意性だけは常に保障される」と考えるということ。同じキー値の使い回しがあると破綻する。一意性も疑うのであれば3.しかないと思うが、これはサロゲートキーの導入はタダではない=追加コストがかかること認める立場だ。


■過去の事実を復元できない

日付を属性に持たないデータについて、サロゲートキーは「自然キーの値の変更に強い」という利点を持っている。

例えば[部門マスタ]と[課マスタ]が 1:N の関係にあるとき、課マスタが部門の自然キー[部門コード]を持っていたら、部門コード体系の変更(桁数拡大など)があったときに、部門だけでなく課マスタまで洗い替えの必要が生じる。

部門マスタにサロゲートキー[部門ID]を付けてこれを課マスタに転記していれば、部門マスタ自体の洗い替えのみで作業が完了する。


問題はトランザクション=「日付」を属性として持つデータの扱いだ。

健忘症サロゲートキー」パターンでは、トランザクションからマスタの自然キーを排除するので、マスタ側の自然キー値の変更に伴い、トランザクション上の日付時点の自然キー値が不明になってしまう。

前回書いた

受注明細に商品マスタのサロゲートキー[商品ID]を載せて自然キー[商品コード]を排除した場合、過去の受注の商品コードを復元できない

といった問題だ。


これは、逆に利点とされることがあるのは承知している。

「ある商品の商品コードが何回変わっても、商品IDで受注明細を検索すれば、当該商品の受注数が漏れなく取れる」とか。

しかし受注明細上の商品コードは、受注日や配送先住所と同じく、業務担当者から見えている過去の事実なのだから、勝手に変えてしまってよいものではない。

この問題を避けながらサロゲートキーを使う方法はこんな感じだろうか:

  1. トランザクションからは他マスタの自然キーを排除しない。受注明細なら[商品ID][商品コード]の両方を載せる
  2. 適用開始日・終了日を持つ[商品履歴マスタ]テーブルを導入する

「自然キーとサロゲートキーのどっちがよいか」みたいな話はもう聞きたくねえんです

私がサロゲートキーを使っていないのは、DWHの保守に仕事が偏っていることもあるが*1、それを導入したときのデータモデルを全体としてどうすべきなのかよく分からないからだ。


上に挙げた2つの問題への対応だけでも2×3=6通りの立場ができてしまった。さらに「そんなことは問題ではない。こんなアンチパターンは認めない」という人も居るだろう。実際「トランザクション上の自然キーは過去の事実だから云々」というと、商品コードぐらい新しいのに変わってもいいんじゃないの...みたいな反応が返ってきたり、問題が起きる具体例を挙げろと詰められたりすることがある。具体例ってあんたさっき自分で「自然キーは業務に密接に関係している(から変わりやすい)」て言ってただろ。密接に関係してる業務は大丈夫なのかよ*2


サロゲートキーの実践方法は恐らく人によってバラバラで、それを一括りにして「自然キーとどっちがよいか」みたいな話してもあんまり捗らない。

「この場合はサロゲートキーが有効で、問題点にはこのように手当てする。こっちの場合なら自然キーでも問題がなくて、...」といった、もうちょっと解像度の高い議論があればぜひ伺いたい。

*1:DWHにはデータが直接入力されず、正とされるデータがI/Fファイルに載って外部から飛んでくる。このときDWH側で独自のサロゲートキーを振ることにはいろいろ問題があって難しい

*2:具体例は別にないんだけど、過去に当該DBのデータを参照して作成したもの全て...Excel帳票なりBIツールのキューブなり紙の伝票なりと、現在のDBのデータが整合しなくなることは無視しないでいただきたい。それが全業務に影響しないことを保証できる人はお客さんの中にも居ないだろう

トラックバック - http://d.hatena.ne.jp/tgk/20130320/1363806187
Connection: close