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

2015-12-08

[]使える論理削除への道(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を設定せず(=外部キー制約により設定できない)、削除フラグだけ立てる

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

2014-08-27

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


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

http://watanabek.cocolog-nifty.com/blog/2014/08/post-b7d8.html

つまり主キー設計というものは、「いくつかの関数従属性が成立することを分析する仕事」というよりは、

むしろ「圧倒的多数の関数従属性が成立しないことを保証する仕事」である。その責任の重大さがおわかりだろうか。

これはわからない。

情報処理試験なんかに出てくる主キー設計手順では、テーブル上の複数の候補キーから主キーを選択するのではなかったか。

であれば「主キーじゃない候補キー → その他の属性」という関数従属性がテーブル内にあるのは普通のことではないのか。


具体例を考えてみる。

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

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

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

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

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

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

も成り立ってしまう(元記事における"{a,d}→c"のケース)。

この関数従属性が「禁じられている」と考えるなら、何らかの手当(顧客名か受注日時を外出しにするとか?)をしなくてはならないが、向こうの流派ではいったいどうするのだろうか。


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

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

顧客番号→顧客名

という関数従属性は成り立っていないと考えている。だから顧客名を受注ヘッダ上に置いており、「必要なら顧客マスタから拾ってくればよいデータ」ではないと考えている。またこれが受注ヘッダではなく顧客マスタであれば「顧客番号→顧客名」は当然成り立つと思っている。

これは我々の業界でいうマスタ/トランザクションの違いであり、オントロジーでいうendurant/perdurantの違いだ。

perdurantなもの(=出来事, イベント)は特定の時刻に固定されており、その属性は時刻を指定しなくては値を特定できない。


もしかして

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

2014-02-23

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



正月にこれを第2部まで読んだ感想を書こうと思って、何ともう2月後半になってしまった。

いろいろ考えさせられたことを忘れてしまう前に感想文を書きます。


(DBばっかりいじっててコードを書かない)俺みたいなのから見たオブジェクト指向設計の特徴に、「実体(エンティティ)の存在は認めても、関係(リレーションシップ)の存在をなかなか認めない」、つまり関係を極力クラスとして立てない、というのがある。

例えば、部門と社員の関係を「所属クラス」として独立させるより、オブジェクト間の関連=参照を握る/握られるで表現する(部門 has_many 社員)ことを好むのだ。

エリック・エヴァンスの考えるDDD*1もまた、この特徴を受け継いでいるように見える。



DDDのモデル

DDDがモデルから関係を排除している例を見てみましょう。

前掲書P-70に、銀行の口座振替シーケンス図が載っている。

話に関係ある部分だけ切り出すと、こんな感じになる:

f:id:tgk:20140224013432p:image:w360

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


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

いずれにしても、このモデルにおいてはトランザクションは単なるデータであって、能動的なオブジェクトとして処理を駆動していない。


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

入出金ロジックを口座に取り付ける以外にも、オブジェクト指向設計的にアリなモデルは存在する。

アナリシスパターン―再利用可能なオブジェクトモデル (Object Technology Series) P-113には、ファウラーお勧めのモデルとして、トランザクションが口座(同書の表現では「勘定」となっている)の明細(同じく「エントリ」)を生成する例が描かれている。

話に関係ある部分だけ切り出すと、こうだ:


f:id:tgk:20140224013141p:image:w360


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


ファウラーのモデルでは、口座自身ではなく、口座と口座の関係であるトランザクションが振替処理を主管する。

DDDのモデルなら「資金振替サービス」のレイヤにあたる何かに生成されたトランザクションオブジェクトが、2つの口座の明細を生成するのだ。

このトランザクションオブジェクトは、たぶん{ 日付時刻, 借方口座, 借方金額, 貸方口座, 貸方金額 }といったデータ項目を持つ永続化対象であり、DDDの分類で言えばサービスではなくエンティティに相当する。

ファウラーのモデルでは、口座オブジェクトは入金・出金ロジックを持たず、いわば「裏口から」勝手に明細を追加される受身の存在だ*2



ファットモデルの原因

DDD本とアナリシスパターン、それぞれのモデルを採用した場合の結果はどうなるだろうか。

集約(Aggregates)を重視するDDDの立場から見れば、口座明細は口座の後ろに隠れているべきものであって、口座を経由せずに誰かが勝手に口座明細を作成するのは問題あり、ということになるのかもしれない。DDDは、集約を迂回して必要なオブジェクトだけをつかむ書き方には冷淡だ。前掲書P-148にこんな風に書いてある。

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

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


ファウラーのモデルが優れているのは、口座オブジェクトがより純粋・シンプルなところだ。

逆にDDDの口座オブジェクトはよりファットだ。振替処理というただ1つのユースケースを実装するために、口座には入金・出金ロジックと、たぶん口座明細を生成するロジックが追加されている。

ファウラーのモデルでは、これら全てが口座間の関係であるトランザクションオブジェクトに吸収され、口座自身の「純度」が保たれている*3


「口座が入出金ロジックを持っているのはまったく自然な設計であって、何の問題もない」と考える人もいるかもしれない。俺も自然な設計だとは思う。

しかし、特定のユースケースを処理するためのメソッドやメンバ変数の追加を無条件に許すと、口座のようなシステムの中核クラスはどんどん膨れ上がり、いわゆるファットモデルが誕生する。

そしてDDDを実践する場合、「自然な設計*4」を優先して、エンティティ間の関係を積極的にクラス化しなかったら、システムの規模拡大に伴ってファットモデルが発生するのは避けられないと思う。

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

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

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

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

2014-01-04

[][]生きているうちに自然キー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:不可能だとわかってもらえるだろうか

2013-10-28

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


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

それは科学哲学の講義で、テーマは「事実と真実はどう違うか」というものだった。

話の要点は以下の3つだ。


1. 真実は観測できない

先生はこんなフリップを出して話を始めた。

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

f:id:tgk:20131028124753p:image:w640

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


先生の定義によれば、真実は事実のように直接観測できるものではない。

現実世界で見ることも、手で触れることもできないのだ。この点、事実よりも思い込みや妄想に近い存在だ。真実なのにな!

思い込みと真実を分けるポイントは、事実に立脚しつつ、その誤差を消去できているかどうかだ。


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

誤差を消すにはどうするか。先生は「平均という手段がある」という。

サンプル数や測定回数を増やしていけば、測定値の平均はある値に収束していく。なるほどその値が真実なのだなと思って聞いていたら、先生は「それだけでは問題がある」と言った。

誤差には偶然誤差と系統誤差があり、算術平均で消せるのは(真実の値の±両方向に均しくばらついている)偶然誤差だけであって、系統誤差は消えないのだ。

系統誤差とはすべての測定にかかっている一定方向の歪み、つまりバイアスのことだ。

昔俺の実家にあった体重計は、何も載せていないときに+2kgを指していた。この2kgが系統誤差だ。

この体重計を使って俺の家族の平均体重を算出したらどうなるか。家族全員を計ってみても、結果は真実から2kgずれているだろう。

系統誤差を放置すると、観測範囲を目いっぱい広げても真実に辿り着かないのだ*1


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

先生は「系統誤差を排除するために、測定器をキャリブレーションすることが必要です」と言った。

キャリブレーションとは「値が分かっているものを測定器に測らせてみて、正しい値を出力するかチェックし、調整する」ことだ。

何も載っていない体重計が+2kgを指すなら、0kgを指すように調整してから測定を始めるのだ。


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

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

いまの話は、つまり

ということだろう。ところが我々のまわりには明らかにキャリブレーションのないプロセスがたくさんあって、事実を入力して何らかの判断を出力している。これらはすべて系統的に間違っているのではないか。

会社の中で言えば、経営判断全般をはじめ人事・採用・見積りなど、キャリブレートされていない活動がたくさんある。


会社の外で言えば、刑事裁判なんかどうか。

非常識な判決を書くと左遷される、といった圧力は裁判官にもかかっていると思うが、それはキャリブレーションとは言えない。

裁判官に対するキャリブレーションとは「無罪(有罪)だとわかっている被告を与えてみて、正しく無罪(有罪)判決を書くかテストする」ことだ。

だが裁判の外側で有罪/無罪を決めることはできないから、テスト用の被告を用意することは不可能で、裁判官をキャリブレートすることはできない。

よって、裁判官の書く判決には系統誤差が温存されている。ということになるのではないか。


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

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

2013-03-24

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


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

f:id:tgk:20130325015336p:image:w640

実はしれっとコンサルタントとか入ってるのな。

ということは、個人事業の開業届に「システムコンサルタント」とか「ITコンサルタント」と書くと事業税の請求が来て、代わりに「システムエンジニア」て書いておけば事業税がタダになるのだろうか。あと「事業の概要」欄に「...およびシステムコンサルティング」とかうっかり書いたらどうなるのか。それを根拠に税金払えって言われるのか。

と思って検索してみたら何かはっきりしないのな。何かSEって届け出したのに「SEの仕事はコンサルタント業だから」という理由で*1税務署から請求来た人も居るっていう。何なんだ。税務署裁量なのか。

*1:んなわけあるか

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

2013-03-05

[]SQLアンチパターン「IDリクワイアド」の再検討


SQLアンチパターン

SQLアンチパターン

話題のSQLアンチパターンの目次に「アンチパターン:すべてのテーブルにID列を用いる」とあるのを見て、大胆にもサロゲートキーを否定しているのかと思って読んでみたが、どうも主張がはっきりしない。論点が尽くされていないような...


SQLアンチパターン」の主張

第3章には以下のようなことが書いてある。


「IDリクワイアド」アンチパターン

IDリクワイアドは「すべてのテーブルに"id"という列名の無意味な連番の列を追加し、PRIMARY KEY制約を付与する」というパターンのこと。


何がいけないのか
  • 自然キーにUNIQUE制約を付けないなら、自然キーの重複を許してしまう
  • 自然キーにUNIQUE制約を付与して、しかもID列を主キーにするならば、キーが冗長になってしまう(ただしキー重複を許すよりはよい)
  • idという列名では何のIDなのか分かりにくいし、結合時にUSING句が使えなくて不便
  • 複合主キーが使いにくいからという理由で、id列を主キーにするのは良くない

どうすべきか
  • idという列名を止めて、xxx_id(customer_idとか)にしましょう
  • O/Rマッパーがそれを要求している場合はアンチパターンを使ってもよい(ただし列名はxxx_idにしよう)
  • 自然キーの一意性・不変性があてにならない場合は、ID列のようなサロゲートキーを導入してもよい
  • 複合主キーは必ず排除すべきものではない。使うべきときは使いましょう


感想

■複合主キーを推す理由が書かれていない

著者は複合主キーを擁護し「複合主キーが使いにくいからという理由で、ID列を主キーにするのは良くない」と言っているが、読み返してみても何が良くないのかよくわからない。理由が書かれるべき位置には

開発者の複合キーへの拒否反応は、数学者が2次元や3次元の座標を使うことを

拒否し、物体が1次元に存在するかのようにして計算することに似ています。

これによって幾何や三角法を単純化できることは事実ですが、対象とすべき

現実世界の物体を正確に表すことはできません。

とあるが、主キー制約を複合キーからID列に移動しても「対象レコードを正確に指示する」ことはできるので、これは比喩が噛み合っていない。

そもそも開発者が複合キーに拒否反応を示す理由は、それをアプリケーションで扱うのが面倒くさいから=余計な工数が発生するからでしょう。

実際に工数がかさんでいるのなら、拒否反応を無視してよいわけがない。


■結局、アンチパターンの追認になっていないか

「自然キーの一意性・不変性があてにならない場合、サロゲートキーを導入してよい」とあるが、「IDリクワイアド」アンチパターンを積極的に使う開発者は、もともと自然キーの一意性・不変性を信用していないのだから(そうですよね?)、このガイドは「IDリクワイアドは常時使用可能」と解釈されるのではないか。


■サロゲーキーを使う場合、過去の事実をどうやって復元するのか

サロゲートキーを外部キーにして、自然キーを他のテーブルに転記しない場合、過去の事実をどうやって復元するかが問題になる。

例えば、商品マスタの自然キーである[商品コード]を受注明細に記載せず、代わりにサロゲートキーである[商品ID]を載せた場合、受注時点の商品コードを復元できる保証がなくなる。

商品コードの不変性があてにならないと考えて商品IDを導入したのだから、受注明細と商品マスタを結合して商品コードを取得したのでは、前提とやってることが矛盾してしまうからな。

これについては、トランザクションに自然キーも転記する(ただし外部キー制約は付けない)か、商品マスタを履歴化(各行に適用開始日・終了日を付ける)するなどの処置が必要だと思うが、私はサロゲートキーを使わないので、どうするのが常識なのかわからない。

著者は「こういう場合はサロゲートキーを使っていい」とは書くが、上記の問題を含めて、サロゲートキー導入時の考慮事項をあまり検討してくれない。


■IDリクワイアドのその他の問題点

個人的には、IDリクワイアドについては「DBMS実装の事情を考えずにID列を必ず主キーにする」点が問題だと思う。

主キーなんてあってもなくてもいいものなのだから、わざわざDBMSが困るような形で付与すべきでない。

例えば

  • 主キーを勝手にclustered indexにしてしまうDBMS
  • 主キーを構成する列でなくてはパーティショニングのキーにできないDBMS

などを使うのであれば、自然キーを主キーにしておかないと、パフォーマンス問題に対する打ち手を失うことになるだろう*1


主キーはあってもなくてもよいと書いたが、これは本書の主張ではない。著者は逆に、

  • すべてのテーブルには主キーが必要である
  • 外部キー参照されるものは主キーでなくてはならない

と考えている模様。

すべてのテーブルには、重複行を防ぎ、個々の行を識別するための主キー制約が必要です。

主キーは外部キーから参照されることで、テーブルの関連付けを行います。

主キー制約が重要になるのは、次のような必要があるときです。

・行の重複を避けたい

クエリで個別の行を参照したい

・外部キー参照をサポートしたい

大した話ではないが、ここに挙げられているのは「主キーが必要な理由」になっていない(ぜんぶUNIQUEインデックスで実現できるから)とは言わせていただきたい。

細けえなぁと思われるかもしれないが、前述の通り主キーにはDBMSによって変な制約が付くことがあるので、ある程度注意して使わなくてはならない。

主キー重視派の著者は「このテーブルには主キーは必要ない」という開発者の発言に対して「間違っている」「勘違いしている」と書くが、その人「UNIQUEインデックスがあるから主キーは要らない」と言ってたんじゃないの、とちょっと思った。

*1:前者は自然キーでの効果的なindex range scanができない。後者は例えば「日付でパーティショニング」といったことができない

2012-11-20

[]年間の生活費を60万円減らす方法


去年3LDKから1DKに引っ越したら家賃が5万円安くなった。

前の家では日当たりの悪い2部屋は物置、和室は洗濯物を干す場所にして、居間に万年床を敷き、夫婦二人でほとんど布団の上だけで生活していた。

テーブルには読みさしの本や仕事の資料が山積し、ソファは服やバッグの置き場所と化して座ることもできず、布団の上にちゃぶ台を置いて飯を食っていたら、ある時気付いたのだ。

俺たち、半分ぐらいの広さの家に住めるんじゃないのかっ...!

ちょうど向かいに新築のアパートが建ったのでさっそく内見して寸法を測り、持ち込めそうにないものを狂ったように捨てたり売ったりして引っ越した。

1DKに相変わらず万年床を敷いてその上だけで生活している。前の家とまったく変わらない。エアコンが最新型になったのでかえって快適になったぐらいだ。

猛烈に捨てたり売ったりしたものは小林よしのりの本とか(妻がコヴァなのだ)15インチのCRTディスプレイとか、2人暮らしなのになぜか3つあるスーツケースとか、誰が買ったのかわからない鉄アレイとか、こんなもののために年間60万払っていたのか俺は...と愕然とする面子だ。


努力せずに生活費を下げたいなら、狭い家に引っ越すという手は非常によい。

何も節約しないで今まで通りの生活ができるからだ。思い切って面積が半分ぐらいの物件に移っても意外と困らないからやってみるといい。


1DKに二人で住むなんてあり得ない、という反応をされることもあるが、そういう時は妻の仕事上の大先生(先生の先生)の話をして認識を改めてもらう。

この人は新婚当時三畳一間の物件に夫婦2人で住んで、なおかつ一人居候の男を住まわせていたのだそうだ。

この話には感動するポイントが2つあって、それは三畳間に居候を迎える大先生の度量の広さと、もう一つは居候の精神力だ。

普通ものすごく居ずらいだろ。新婚夫婦と川の字になって寝る根性が貴方にはあるか。

2012-09-18

[]「等しい」と「重複している」の違い。それらとUNIQUE制約の関係


SQLを使っていると、あたかもNULLがNULLに等しいかのように見える場面が多々ある。

例えば DISTINCT や GROUP BY で複数のNULLが1個に集約されるとか。あるいは集合演算子(UNION, EXCEPT, INTERSECT)でのNULLの扱いとか。

SQL92の解説書

によれば、NULLとNULLは等しくはないが「重複」はするのだそうだ。

列col1とcol2が等しいか、ともにNULLならば、それらは重複していると判定される。

DISTINCTやGROUP BY, 集合演算子は重複を排除するものであり、GROUP BYが複数のNULLを1行に集約するからといって、それは複数のNULLが「等しい」からではない。...ということらしい。


ではSQLの規格は、「等しいこと」と「重複」のどちらに基づいて一意性を判断するよう求めているか。

SQL92には、UNIQUE制約は「いかなる2行も、nullではない同じ値を持たないとき」に満足される、と書いてある。

つまり「重複」ではなく「等しいこと」に基づいて一意かどうかを判断せよ、ということで、やはり前回見た「NULLがUNIQUE制約に縛られない」というMySQLの挙動は、SQLの規格に適合しているようだ。