使える論理削除への道(1) それは論理削除の問題なのか


削除フラグ(というか「論理削除を削除フラグだけで実装すること」)批判は何度も見てきたが
PostgreSQLアンチパターン
これ見るともはや論理削除自体が闇扱いになってしまったようだ。


闇だろうと何だろうと論理削除(というドリルが提供する穴)は要件の実装に必要なので、このへんの議論はあまりまじめに追ってこなかった。
いつだったか「削除フラグはバグの温床だからやめろ」という主張を読んでおぉなるほどと思い、ではどうやって論理削除を実装するのかなと思って続きを読むと「『ほんとに削除したデータが必要ですか?』とユーザに確認して、物理削除に変えさせてもらう」と書いてあってズコーとなったことがあるが、要件削っていいなら実装上のどんな問題も消えるわけで、何かもう別世界の議論で自分の仕事には関係ないと思っていた。
が、これだけ繰り返し批判されているからには、論理削除を正しく使う方法なり条件なりを明らかにしておかないと不安だ。
まずは最近話題になった上記プレゼン資料(以下「資料」)を導きの糸にして、論理削除の何が問題なのかを理解したい。


資料は論理削除の3つの問題点を指摘している。

  • クエリが複雑になる
  • UNIQUE制約が使えなくなる
  • 複雑な表示条件の原因

これらは論理削除に不可避の問題なのだろうか?

「クエリが複雑になる」→ パーティショニング

「削除フラグはクエリを複雑にする」という指摘には、以前から「生きているデータだけ切り出すビューを作ればいいんじゃないの」と思っていたが、資料のP-45以降にそれではダメだと書いてあった。

  • それらのビュー同士を結合するクエリはパフォーマンスに難がある(=いらないデータを物理削除したテーブルを参照するより、遅い)
  • パフォーマンス問題はインデックスやマテリアライズドビューで解決できない
  • 論理削除データの件数は増え続けるので、問題は悪化する一方になる

とのことだが。


商用DBであればパーティショニングがサポートされているので、削除フラグのON/OFFでパーティションを割ったテーブルに、生きているデータだけ切り出すビューをかぶせれば、指摘されている問題は解決する。つまり、死んだデータが0件から10億件に増えても、生きているデータだけを抽出/結合する速さは変わらない。


PostgreSQLも(かなり変わった形で)パーティショニングをサポートしているので、同じことができるような気がするが、資料では検討されていない。
PostgreSQLには「パーティションをまたがるUNIQUE制約が作れない」という制約があるらしいので、後段の議論との整合性で論外とされたのかもしれないし、全然関係ないかもしれない。わからない。


とにかく「クエリが複雑になり、パフォーマンスも下がる」というのは、自由にパーティションを切れないDBMSに言えることであって、論理削除自体の問題ではない。当該資料はPostgreSQLアンチパターンなので「削除フラグはダメだ」という結論でよいかもしれないが、一般論と誤解してはならない。

「UNIQUE制約が使えなくなる」→ Null-Key

本来、論理削除でキー重複が発生することはない。論理削除とUNIQUE制約は普通に両立する。
が、論理削除の概念を「データの指す対象が削除(=社員なら退職、ユーザなら退会、商品なら廃版、...)されたら、削除フラグを立てること」だけでなく「データの更新履歴をマスタテーブル本体に累積し、過去の履歴には削除フラグを立てること」にまで拡張すると、キー重複が発生し、UNIQUE制約との兼ね合いが問題になる。


論理削除と更新履歴は分けて考えなくてはならないと思うがそれは置いて、資料の議論に乗っかるなら、更新履歴とUNIQUE制約/外部キー制約の両立にはNull-Keyの技法を使えばよい。
資料の例で言えば

  • name
  • name_NK ←ヌル・キー

の2列を用意し、まず両方に同じユーザ名を設定する。データが最新版から過去の履歴に変わるタイミングで、name_NKにNULLを設定する*1
Unique制約・外部キー制約(たぶん遅延制約にする必要がある)はname_NKについて設定し、主キーは無し(か、name列+何か)にする。
PostgreSQLをはじめ、大半の実装ではNULLの重複はUNIQUE制約にひっかからないので、これで問題を回避できる。


Null-Keyなんて聞いたことないしそんな妙なことしたくねえなぁと思うかもしれないが、これは20年以上前から*2使われている由緒正しいテクニックなので、経験ではなく歴史に学ぶ賢者ならむげにできないはずだ。俺は使ったことないけど。

「複雑な表示条件の原因」→ ?

ここは資料だけ見ても意味がわからなかった。
プレゼン聴かないで「1つのデータを取るために関係するtableが多すぎる」原因が論理削除である理由がわかった方は居ますか。

「処方箋: 有効なデータのみを残す 例えば削除済みTABLEを作る」

これが処方箋になるのはどういう場合だろうか。
「論理削除はUNIQUE制約が使えないから駄目だ」というからには、処方箋ではUNIQUE制約が機能しなくてはならない。
が、削除済みテーブルに削除データを移動したら、元テーブルにUNIQUE制約は残っていても、その制約は半分死んでいる。deleted_usersに移動した退会ユーザのユーザID(=name列)の値を、usersに再度挿入されることを拒否できないから。
これで問題が起きないのは、UNIQUE制約の付いたキー値を使い回せる場合だけだろう。


資料の例はブログシステムなので問題なさそうだ。ユーザID=xxxxが退会したら、ブログエントリも全部削除テーブルに移動し、次にユーザID=xxxxが新規登録されたら、まったくの別人として扱えばよい。これに対して、キー値の使い回しができない場合は、「有効なデータのみを残す」アプローチは処方箋にならない。
仮に「論理削除はすべて悪だ」と信じて商品マスタの削除フラグを廃止し、廃版商品は削除済み商品テーブルに移したとする。
廃版商品の商品コードは、商品マスタ本体のUNIQUE制約では拒否できない。結果、廃版商品と同じ商品コードの新商品を作ることができてしまう。
受注明細(本体であれ、削除済みテーブルであれ)に載っているその商品コードは、新商品を指すのか、売切れ直前の廃版商品を指すのか、もはや知る方法がない。

まとめ

論理削除を削除フラグだけで実装するのはいろいろ問題があるが、それは論理削除の実装方法の問題であって、論理削除自体の問題ではないような。

*1:もちろんユーザが退会した場合はNULLを設定せず(=外部キー制約により設定できない)、削除フラグだけ立てる

*2:1993年初版の佐藤正美「クライアント/サーバデータベース設計テクニック」にNull-Keyの解説がある

主キーが「圧倒的多数の関数従属性が成立しないことを保証する」ものならば、受注ヘッダに顧客名を置いてはならないのだろうか


渡辺先生がミステリアスな記事をupしている。

http://watanabek.cocolog-nifty.com/blog/2014/08/post-b7d8.html
つまり主キー設計というものは、「いくつかの関数従属性が成立することを分析する仕事」というよりは、
むしろ「圧倒的多数の関数従属性が成立しないことを保証する仕事」である。その責任の重大さがおわかりだろうか。

これはわからない。
情報処理試験なんかに出てくる主キー設計手順では、テーブル上の複数の候補キーから主キーを選択するのではなかったか。
であれば「主キーじゃない候補キー → その他の属性」という関数従属性がテーブル内にあるのは普通のことではないのか。


具体例を考えてみる。
元記事のセミナーの例に倣って、こんな受注ヘッダを想定する。

{ 受注番号(PK), 顧客番号(PK), 顧客名, 受注日時 }

実データにおいて、関数従属性

(受注番号, 顧客番号)→顧客名

が成り立つだろう。また同時に

(受注日時, 顧客番号)→顧客名

も成り立ってしまう(元記事における"{a,d}→c"のケース)。
この関数従属性が「禁じられている」と考えるなら、何らかの手当(顧客名か受注日時を外出しにするとか?)をしなくてはならないが、向こうの流派ではいったいどうするのだろうか。

マスタとトランザクションの違い

くどいが「受注ヘッダに顧客名は要らないだろ」という人のため補足すれば、現実世界の顧客名は時間とともに変わるから、俺は上記の受注ヘッダにおいて

顧客番号→顧客名

という関数従属性は成り立っていないと考えている。だから顧客名を受注ヘッダ上に置いており、「必要なら顧客マスタから拾ってくればよいデータ」ではないと考えている。またこれが受注ヘッダではなく顧客マスタであれば「顧客番号→顧客名」は当然成り立つと思っている。
これは我々の業界でいうマスタ/トランザクションの違いであり、オントロジーでいうendurant/perdurantの違いだ。
perdurantなもの(=出来事, イベント)は特定の時刻に固定されており、その属性は時刻を指定しなくては値を特定できない。

もしかして

渡辺先生もこんなことわかってて書いていて、元記事の「主キー」を「候補キー」と読み替えればいいだけの話なのだろうか。

「ドメイン駆動設計」感想(1) - なぜファットモデルになるのか


エリック・エヴァンスのドメイン駆動設計 (IT Architects’Archive ソフトウェア開発の実践)

エリック・エヴァンスのドメイン駆動設計 (IT Architects’Archive ソフトウェア開発の実践)


正月にこれを第2部まで読んだ感想を書こうと思って、何ともう2月後半になってしまった。
いろいろ考えさせられたことを忘れてしまう前に感想文を書きます。


(DBばっかりいじっててコードを書かない)俺みたいなのから見たオブジェクト指向設計の特徴に、「実体(エンティティ)の存在は認めても、関係(リレーションシップ)の存在をなかなか認めない」、つまり関係を極力クラスとして立てない、というのがある。
例えば、部門と社員の関係を「所属クラス」として独立させるより、オブジェクト間の関連=参照を握る/握られるで表現する(部門 has_many 社員)ことを好むのだ。
エリック・エヴァンスの考えるDDD*1もまた、この特徴を受け継いでいるように見える。


DDDのモデル

DDDがモデルから関係を排除している例を見てみましょう。
前掲書P-70に、銀行の口座振替のシーケンス図が載っている。
話に関係ある部分だけ切り出すと、こんな感じになる:

口座への入金・出金ロジック(=credit, debitメソッド)が、口座オブジェクト自身に取り付けられていることがわかる。


このシーケンス図では、2つの口座の残高を操作するメソッドは見えているが、「何月何日に、a口座からb口座に、いくら送金した」というトランザクションデータを生成するタイミングは描かれていない。よくわからないが、P-71に「紙幅の関係で、本来あるべき元帳オブジェクトや ... 金銭取引オブジェクトなどを省略した」という意味のことが書いてあるので、どこかで生成するつもりなのだろう。たぶん左端の「資金振替サービス」のtransferToメソッドの中で、ログみたいな処理の副産物として、トランザクションを生成するのではないか。
いずれにしても、このモデルにおいてはトランザクションは単なるデータであって、能動的なオブジェクトとして処理を駆動していない。

アナリシスパターンのモデル

入出金ロジックを口座に取り付ける以外にも、オブジェクト指向設計的にアリなモデルは存在する。
アナリシスパターン―再利用可能なオブジェクトモデル (Object Technology Series) P-113には、ファウラーお勧めのモデルとして、トランザクションが口座(同書の表現では「勘定」となっている)の明細(同じく「エントリ」)を生成する例が描かれている。
話に関係ある部分だけ切り出すと、こうだ:



DDD本とは操作の方向が逆になっていることがわかる。


ファウラーのモデルでは、口座自身ではなく、口座と口座の関係であるトランザクションが振替処理を主管する。
DDDのモデルなら「資金振替サービス」のレイヤにあたる何かに生成されたトランザクションオブジェクトが、2つの口座の明細を生成するのだ。
このトランザクションオブジェクトは、たぶん{ 日付時刻, 借方口座, 借方金額, 貸方口座, 貸方金額 }といったデータ項目を持つ永続化対象であり、DDDの分類で言えばサービスではなくエンティティに相当する。
ファウラーのモデルでは、口座オブジェクトは入金・出金ロジックを持たず、いわば「裏口から」勝手に明細を追加される受身の存在だ*2


ファットモデルの原因

DDD本とアナリシスパターン、それぞれのモデルを採用した場合の結果はどうなるだろうか。
集約(Aggregates)を重視するDDDの立場から見れば、口座明細は口座の後ろに隠れているべきものであって、口座を経由せずに誰かが勝手に口座明細を作成するのは問題あり、ということになるのかもしれない。DDDは、集約を迂回して必要なオブジェクトだけをつかむ書き方には冷淡だ。前掲書P-148にこんな風に書いてある。

クライアントコードがデータベースを直接使用していると、開発者は、集約のようなモデルの機能や、オブジェクトのカプセル化さえも迂回し、必要なデータを直接取り出して操作したくなってしまう。...開発者は、欲しいオブジェクトを何でも直接つかんでしまおうという気になる。

この点についてはDDDのモデルの方が優れているのかもしれない。が、よくわからない。正直言って集約というアイデアは、実践上どこまで貫徹できるのか怪しいと思う。


ファウラーのモデルが優れているのは、口座オブジェクトがより純粋・シンプルなところだ。
逆にDDDの口座オブジェクトはよりファットだ。振替処理というただ1つのユースケースを実装するために、口座には入金・出金ロジックと、たぶん口座明細を生成するロジックが追加されている。
ファウラーのモデルでは、これら全てが口座間の関係であるトランザクションオブジェクトに吸収され、口座自身の「純度」が保たれている*3


「口座が入出金ロジックを持っているのはまったく自然な設計であって、何の問題もない」と考える人もいるかもしれない。俺も自然な設計だとは思う。
しかし、特定のユースケースを処理するためのメソッドやメンバ変数の追加を無条件に許すと、口座のようなシステムの中核クラスはどんどん膨れ上がり、いわゆるファットモデルが誕生する。
そしてDDDを実践する場合、「自然な設計*4」を優先して、エンティティ間の関係を積極的にクラス化しなかったら、システムの規模拡大に伴ってファットモデルが発生するのは避けられないと思う。

*1:DDDにもいろいろあるんだろうけど、以下単にDDDと表記する

*2:この図で口座の残高属性が更新されていないのは、「残高はエントリを集計して出す導出項目」という解釈だからだろう。結果、振替処理に口座はまったく参加しない

*3:私はT字形ER手法の考え方に従い、エンティティが他のエンティティとの関係を内部に取り込んでいることを「純度の低下」と考えている。例えば社員クラスに「所属部門」という名前の、部門クラスのメンバ変数があったら、それは所属という関係を内部に取り込んでヨゴレていると見なす。純度が下がって何が悪いかというと、クラスが際限なく肥大するのが良くない

*4:最近はメンタルモデルとか言うのだろうか

生きているうちに自然キーvsサロゲートキー問題に決着を付けたい(1)


営業のアプローチ方法にA,Bがあるとして(例えば「礼状は手書きで出す」「礼状は印刷して出す」とか)、Aの成約率が10%でBのそれが5%なら、「Aしかやらない」というのは悪くない選択だろう。だが我々の仕事は営業とは違っている。


ある量産品の製造方法にA,Bがあるとして、Aの良品率が98%でBのそれが95%なら、Aで製造するのが正しいだろう。だが我々の仕事は量産品の製造とは違っている。


システム開発における失敗プロジェクトは、営業における失注や工場における不良品のようなものではない。つまり、事業を行う上での必要経費ではない。
だから、A,Bどちらの開発技法を採用すべきかを、プロジェクトの成功率で決めることはできない。
仮に自然キーを採用したプロジェクトの成功率が5割で、サロゲートキーを使った場合のそれが9割だったとして、「すべてのシステムにサロゲートキーを使う」という判断は間違っている。1割のプロジェクトが確実に失敗するからだ。もしその1割に自然キーがフィットしていたとすれば、完全な判断ミスをしたことになる。
この1割の存在を云々することはサロゲートキーに対する批判・攻撃ではない。逆です。それはサロゲートキーという技法を安全に使うために絶対必要な何かだ。


みんな大好きクレイトン・クリステンセンがこのあたりのことを繰り返し書いている*1
クリステンセンは「現実を観察して理論を作ったら、次はその理論に対する反例を探せ」と言う。
自分の理論を支持する事例などいくら集めても意味がないのだ。理論が失敗する事例を積極的に探して、失敗する条件を明らかにすることで初めて理論は改善される。
つまり「こうすれば、結果は何%の確率でこうなる。結果には...という傾向が見られる」という相関関係の記述から、「〜という条件が揃ったところででこうすれば、結果は必ずこうなる」という「条件付き因果関係の言明」に理論が進化する、のだそうだ。
後者の理論があって初めて、我々は(全体の傾向ではなく)個々の事例の結果を予測することができるようになる。

これから書くことはサロゲートキーへの批判ではない

必要なのは、技法が通用する時・しない時の、正確な場合分けだ。
これは、その技法を使っている人にはできないことだと思う。
例えば「自然キーはいつ変わるかわからないから、すべてのテーブルにサロゲートキーを振るべきだ」と信じている人は、世の中に「(正当な理由をもって)サロゲートキーを振ることが不可能なシステム」があることに気付いていないだろう。内側から限界は見えないのだ。
よって、ある技法の限界を探るには、その技法を使っていない人の協力が必要になる。


ところで現状の俺は完全な自然キー派だ。
いま運用担当しているお客さん(2社)のシステムは、1つは本番DB内のテーブル数が900本、もう一つは2000本以上あるが、サロゲートキーを付加しているテーブルは1つもない。
サロゲートキー派の人には地獄のような状態に見えると思うが、これらはいわゆる情報系のシステムであって、基幹系からデータの供給を受けて動いているので、自前でサロゲートキーを振ることが不可能*2なのだから仕方がない。
近日中に、そういう立場からサロゲートキーの適用範囲を云々するつもりだが、これは既述の通り自然キー派によるサロゲートキーへの批判・攻撃ではない。違います。違うんです。

*1:教育×破壊的イノベーション~教育現場を抜本的に変革すると、もう一冊何かに書いているのを見た

*2:不可能だとわかってもらえるだろうか

事実と真実はどう違うか。また裁判官のキャリブレーションについて


俺が通ってるジムのステアマスターにはテレビが付いていて、いつもテレビ見ながらをステアマスター漕いでるんだけど、この前北斗の拳の再放送見ようと思ってテレビ点けたら放送大学の講義をやっていて、これがえらく面白かった。
それは科学哲学の講義で、テーマは「事実と真実はどう違うか」というものだった。
話の要点は以下の3つだ。

1. 真実は観測できない

先生はこんなフリップを出して話を始めた。
「何かを観測して得たデータを事実と呼ぶならば、事実には必ず誤差が含まれています。事実の集積から、何らかの手続きで誤差を消去して抽出したものを真実といいます」

ガタガタのヒストグラムという事実を集めて、そこから滑らかな曲線を描く真実を取り出すわけだ。


先生の定義によれば、真実は事実のように直接観測できるものではない。
現実世界で見ることも、手で触れることもできないのだ。この点、事実よりも思い込みや妄想に近い存在だ。真実なのにな!
思い込みと真実を分けるポイントは、事実に立脚しつつ、その誤差を消去できているかどうかだ。

2. 平均では誤差は消えない

誤差を消すにはどうするか。先生は「平均という手段がある」という。
サンプル数や測定回数を増やしていけば、測定値の平均はある値に収束していく。なるほどその値が真実なのだなと思って聞いていたら、先生は「それだけでは問題がある」と言った。
誤差には偶然誤差と系統誤差があり、算術平均で消せるのは(真実の値の±両方向に均しくばらついている)偶然誤差だけであって、系統誤差は消えないのだ。
系統誤差とはすべての測定にかかっている一定方向の歪み、つまりバイアスのことだ。
昔俺の実家にあった体重計は、何も載せていないときに+2kgを指していた。この2kgが系統誤差だ。
この体重計を使って俺の家族の平均体重を算出したらどうなるか。家族全員を計ってみても、結果は真実から2kgずれているだろう。
系統誤差を放置すると、観測範囲を目いっぱい広げても真実に辿り着かないのだ*1

3. 測定の前にキャリブレーションせよ

先生は「系統誤差を排除するために、測定器をキャリブレーションすることが必要です」と言った。
キャリブレーションとは「値が分かっているものを測定器に測らせてみて、正しい値を出力するかチェックし、調整する」ことだ。
何も載っていない体重計が+2kgを指すなら、0kgを指すように調整してから測定を始めるのだ。

キャリブレーションのないプロセスは真実を出力しない

俺は系統誤差とキャリブレーションという言葉を知らなかった(=概念が頭になかった)から、先生の話はたいへん衝撃的で、ステアマスターを踏みながらウーンと考え込んでしまった。
いまの話は、つまり

  • キャリブレーションのないプロセスでは系統誤差が温存される
  • 系統誤差を温存するプロセスに事実を入力しても、真実は出てこない

ということだろう。ところが我々のまわりには明らかにキャリブレーションのないプロセスがたくさんあって、事実を入力して何らかの判断を出力している。これらはすべて系統的に間違っているのではないか。
会社の中で言えば、経営判断全般をはじめ人事・採用・見積りなど、キャリブレートされていない活動がたくさんある。


会社の外で言えば、刑事裁判なんかどうか。
非常識な判決を書くと左遷される、といった圧力は裁判官にもかかっていると思うが、それはキャリブレーションとは言えない。
裁判官に対するキャリブレーションとは「無罪(有罪)だとわかっている被告を与えてみて、正しく無罪(有罪)判決を書くかテストする」ことだ。
だが裁判の外側で有罪/無罪を決めることはできないから、テスト用の被告を用意することは不可能で、裁判官をキャリブレートすることはできない。
よって、裁判官の書く判決には系統誤差が温存されている。ということになるのではないか。


...等など、色々なものが疑わしく見えてきて、実は上の話はもう4ヶ月ぐらい前のことなんだけど、未だに世界の見え方が変わったままになっている。

*1:ここで思い出したのが「観測範囲が狭い」という言葉だ。偏った意見に対して「サンプル数を増やせ」という意味で使うのだが、観測している本人の系統誤差を正さないなら、観測範囲を広げても何にもならないのだ。それどころか逆に偏見が強化されかねない

うっかりコンサルタントを名乗ると税金取られるのか?


個人事業税てあるでしょう。何となく役所の許認可事業が対象だと思ってたんだけど、埼玉県のホームページ見たらぜんぜん違っていて、

実はしれっとコンサルタントとか入ってるのな。
ということは、個人事業の開業届に「システムコンサルタント」とか「ITコンサルタント」と書くと事業税の請求が来て、代わりに「システムエンジニア」て書いておけば事業税がタダになるのだろうか。あと「事業の概要」欄に「...およびシステムコンサルティング」とかうっかり書いたらどうなるのか。それを根拠に税金払えって言われるのか。
と思って検索してみたら何かはっきりしないのな。何かSEって届け出したのに「SEの仕事はコンサルタント業だから」という理由で*1税務署から請求来た人も居るっていう。何なんだ。税務署の裁量なのか。

*1:んなわけあるか

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のデータが整合しなくなることは無視しないでいただきたい。それが全業務に影響しないことを保証できる人はお客さんの中にも居ないだろう