« 「モデリング&プロトタイピング手法」事例紹介 | トップページ

2017.05.23

単独主キーでもDB設計は楽にならない

 我ながらしつこいが、どんな開発環境においても「id」のような単独主キーを用いる設計手法の危険性について、もう一度指摘したい。この手法を「単独主キー主義」と呼んでおくが、これに関して「複合主キーを用いると主キーが変化しやすいから」とか「単独主キーのほうが仕様変更に対応しやすい」といった擁護意見がある。それらの主張が無効であるばかりか有害でさえあることを説明しよう。

 なお、私はこの議論で「だからナチュラルキーが正しい」と言おうとしているのではない。じっさいのところ私はナチュラルキーを主キーにすることに反対しているが、それは本稿の主張と矛盾しない(参考記事)。ここで私が言いたいのは、複合主キーを用いたオーソドックスな設計手法を身につけたうえで、利用する開発環境の制約にもとづいて慎重に正規化崩しできるようになろう、ということだ。

■サンプルのデータ要件

 複合主キーを要求するデータ要件として、前の記事で挙げた素朴な例をここでも用いることにする。仕入先と商品と契約開始年月の組み合わせに関数従属する契約仕入単価の例である。関数式と制約をモデル([…]はテーブル名、{…}は主キーを表す)とともに示すと次のようになる。

 F(仕入先id、商品id、開始年月)= 契約仕入単価
 ユニーク制約:仕入先idと商品idと開始年月で値が重複してはいけない
 更新不可制約:仕入先idと商品idと開始年月の値が変化してはいけない

 オーソドックスなモデル:
 [商品仕入契約]{仕入先id、商品id、開始年月}、契約仕入単価

 「ユニーク制約」がなぜ必要かというと、上記の関数従属性が成り立つことを保証するためには、識別子(仕入先id,商品id,開始年月)の値に対応する従属子(契約仕入単価)の値が1個に定まらなければいけないからだ。テーブル中に変数の組み合わせが重複して存在することを許せば、そのことが保証できなくなる。

 「更新不可制約」の必要性についてはどうか。識別子は他テーブルとの関係を維持するための基礎となるものなので、レコード上のその値が変化すれば、他テーブル上のレコードとの既存の対応関係が失われる。ゆえに、レコードが追加されてから削除されるまで、識別子の値が変更されることは禁止されなければいけない。

■複合主キーを用いた場合の変遷

 さて、このデータ要件が最初から上記の「オーソドックスなモデル」の形で仕様化されるとは限らない。たとえば次のような経緯をたどったりする。

(1)[商品仕入契約]{仕入先id、商品id}、契約仕入単価
   ↓
(2)[商品仕入契約]{仕入先id、商品id}、契約仕入単価、開始年月
   ↓
(3)[商品仕入契約]{仕入先id、商品id、開始年月}、契約仕入単価

 まず最初に、「仕入先と商品毎に仕入単価が決まります」というユーザの発言を字義通りに受け取って(1)のように実装される。カットオーバー後に、開始年月(契約の発効年月)を管理する必要があることがわかって、(2)のように修正される。さらにその後、開始年月が主キーに含まれるべきであることがわかって(3)のように修正される。想像してもらえばわかるように、(2)→(3)では主キーの作り変えが起こるので、多大な修正コストがかかる。

 ではこれを「複合主キーを用いたばかりに、主キーが変化してひどい目にあった事例」とみなしてよいものだろうか。少なくともこの例では、データ要件が変化したわけではなく、設計者の洞察やスキルが足りなかっただけなのである。契約仕入の一般的なあり方に通じていれば、いくらユーザが「仕入先と商品毎に仕入単価が決まります」と言おうが(*1)、最初の時点で(3)にたどりつける。

 「複合主キーを用いたばかりに、主キーが変化してひどい目にあった事例」の多くは、このような「(a)データ要件に対する洞察が甘かった事例」か、「(b)変化に影響されにくい設計上の工夫が足りなかった事例」に分類できるのではないだろうか。もちろん、「(c)データの管理方針として関数従属性が変化した事例」も起こり得るが、それほど頻度が多いわけではない。

■単独主キーを用いた場合の変遷

 百歩譲って(c)の事例が多いとして、だからといって単独主キーだけを使って設計することで対処しやすくなるかというと、残念ながらそうはいかない。むしろ、混迷の度合いが深まる。単独主キーを用いた場合、たとえば以下のような経過をたどる(<…>は主キー以外のユニーク制約を示す)。(※ユニーク制約を(1),(2),(3)と合わせたほうがよいと指摘されたので(4)と(5)を修正した。おかげで対比がより明確になった)

(4)[商品仕入契約]{id}、契約仕入単価、<仕入先id、商品id>
   ↓
(5)[商品仕入契約]{id}、契約仕入単価、開始年月、<仕入先id、商品id>
   ↓
(6)[商品仕入契約]{id}、契約仕入単価、<仕入先id、商品id、開始年月>

 (4)→(5)の変化は上掲の(1)→(2)に対応するもので、属性項目の追加だけなので(1)→(2)と同様にたいしたコストはかからない。では、(2)→(3)に対応する(5)→(6)はどうだろう。主キーは変わらないので、(2)→(3)よりもコストはかからないように思えるかもしれない。しかし現実は甘くない。

 すなわち、(5)→(6)でユニーク制約を変更する際に、その制約に違反する既存レコードをなんとかしなければいけない。同時に、そのテーブルを参照する他テーブルがあれば、該当レコードとの関係を調整しないとデータの整合性が失われる。そのためのコストは(2)→(3)の場合とまったく変わらない。むしろ、不整合を機械的に検出できないという点でやっかいで、対応関係を個々に調べて丹念に評価しなければならない。余計な項目(id)を扱う羽目になるという意味でも面倒だ。

■データの不整合が「ユーザ側の問題」にされる

 より深刻な問題は、(5)→(6)の仕様変更とそれにともなうデータ移行の必要性が、設計者自身によって気づかれにくい点だ。単独主キー主義にこだわれば、そもそも単独主キー以外のユニーク制約を考慮する習慣が身につかないつきにくい。なぜなら、かれにとってすべてのテーブル上のレコードは「永続化されたオブジェクト」であって、例外なく単独主キー(オブジェクトID)で識別可能なものだからだ。(4),(5)、(6)に付加されているユニーク制約を含め、せいぜいその必要性を第三者に指摘されて、「あ、必要なのね。では付与しましょうか」と受動的に反応するしかない。

 第三者の指摘であっても、それに反応して仕様変更してくれるのであればまだ良い。重複レコード(矛盾レコード)が見つかっても、設計者はそれを「設計上の問題」ではなく「運用上の問題」とみなしてしまうかもしれない。そのような変なデータを入力したユーザが悪い、というわけだ。

 さらに、それらの項目には「更新不可制約」も付与されるべきなのだが、その必要性が認識されないままに項目値が変更されれば、広域のデータ不整合が生じる(*2)。ところが、これがまた大変わかりにくい不整合なので、「なんとなく変なデータがあるような気がする」といった印象をユーザに与える程度で、その対応もユーザまかせにされてしまいそうだ。

 ユニーク制約を構成する項目の値を更新するようなアプリなんてわざわざ作らないから大丈夫、という意見があるかもしれない。だが問題は、そのようなアプリを作らないことが「開発環境やDBによって保証されているわけではない」という点だ(*3)。

 すなわち、カットオーバー当初はそこらへんが周知されているとしても、保守フェーズにおいて、要員が交代するだけでなく、さまざまな環境を介してアプリが作られ始める。その過程でユーザが「複社購買になったので、仕入先を切り替えられるようにしたい」などと言い始めたとしたらどうか(頓珍漢な提案だが、このような言い方をユーザはふつうにするものだ)。既存レコード上の仕入先を変更することをがデータ管理上許されないことを、保守担当者はユーザに対して論理的に説明できなければいけない。そうでなければ、かれは仕入先が変更可能であるようなアプリをむざむざと用意するだろう。

 それでデータの整合性が崩れた場合、誰の責任になるのだろう。本来はユーザの責任ではなく、そのような提案を受け入れた保守担当者(設計者)の責任である。しかも、繰り返すが、その結果として起こる不整合は非常にわかりにくく、一見すると何も問題が起こっていないように見える。かくして整合性がゆっくり失われ、その実態も原因もよくわからないという最悪のデータ状況に陥る。

 以上をもって「単独主キーのほうが仕様変更に強い。複合主キーでは仕様変更の影響が深刻化する」などと天真爛漫に語っていいものだろうか。DBレベルの仕様調整の必要性を設計者が気づかずに、発生する不整合の責任を誰かに押し付けただけの話ではないのか。しかも悪いことに、設計上の失敗が失敗として認識されず、設計者に対するフィードバックが起こらない。そのDBを最初に設計した技術者は、今日も別のプロジェクトで同じようにハタ迷惑な設計を生み出しつつ「何事もシンプルイズベストさ。じっさい単独主キーで問題が生じたことはないよ。なんで複合主キーにこだわるかね」と自分の仕事に満足して美味いビールを飲むのだろうか。

■適切に物理設計できれば大丈夫なのか

 「必要なユニーク制約も更新不可制約も考慮しているから、単独主キーだけでも問題はない」と考える方がおられるかもしれない。そのやり方を「単独主キー専用環境」で実施するのであれば問題はない。私自身、単独主キー専用環境をつかう羽目になったら、議論の余地なくそのようにするだろう。しかし「すべての環境」でそのやり方にこだわるとしたら、どう考えてもおかしい。

 なぜか。必要なユニーク制約や更新不可制約を考慮できているのであれば、少なくとも論理設計の段階では複合主キーを含めた形で構想しているということだ。そのうえで、単独主キーに差し替えた形の物理設計を用意するというのは、実装環境の制約があるゆえの「本来ならばやらなくていい手順」なのである。その必要もないのに、いちいち単独主キーを組み込んで物理設計を別途作るというのは、いたずらに仕様情報の管理コストを増やすだけでメリットは何もない。複合主キーを許容する環境を使えるのであれば、論理設計どおりに実装すればよろしい。けっきょく、「論理設計(理想)=物理設計(現実)」であることが理想なのである。なんと当たり前の話だろう。

 ようするに、単独主キーを活用してもDB設計の難しさは緩和されないということだ。むしろ、単独主キーを使うことで問題が見えにくくなる分、より高度なスキルと慎重さが求められる。とどのつまり、「単独主キー専用環境」だろうと「複合主キー許容環境」だろうと、「(複合主キーを要請し得る)関数従属性」にもとづく正規化と正規化崩しのオーソドックスな設計スキルが求められるということだ。「単独主キー専用環境」は、神経質に使う限り良いものなのだろう。しかし、用いる環境に関わらず単独主キーを置くことにこだわる「単独主キー主義」は混乱しかもたらさない。


★イベントのお知らせ
前回記事の公開直後に、ここらへんの問題を議論するために、IT勉強宴会の緊急イベントが大阪で開催されました。たいへん有意義だったので、東京でもIT勉強宴会と「超高速開発コミュニティ/モデリング分科会」の共同主催で6月28日(水)の夜に「単独主キー主義の是非を問う」を開催することになりました。「我こそは単独主キー主義者なり」という方もふるってご参加ください。対立を煽るのではなく、違いを理解して互いの開発スキルを向上できる場にしたいと思います。


*1.主キーに含まれる項目の値が変更されることを抑止することは、本来であればDBMSの基本機能として提供されるべきだが、そのような「お行儀の良いDBMS」は現状では例外的である。したがって更新不可制約については、開発環境が提供する機能として期待されざるを得ない(関連記事)。
*2.これはユーザが嘘をついたとか不正確なことを言ったということではない。年月で契約仕入が切り替わることは彼らにとって「言わずもがな」のことであるに過ぎない。そして、言わずもがなの事情がDB構造に重大な影響を及ぼすことを、彼らは想像できない。これは、ユーザの語りは重要な手がかりだが、それらを字義どおりに鵜呑みにすべきではないということでもある。だからこそ、DB設計を担当する者には広範囲の業務知識が求められる。「ユーザがこうしてくれと言ったからこうした」の言い訳は通用しない。
*3.たとえば「仕入先id」なり「商品id」なり「開始年月」の値が更新された場合、そのレコードの主キー(id)を参照している別テーブルのレコードとの関係がどうなるかを想像してみてほしい。両者の間には不整合が起こっていると考えられるが、その事実を認識しづらい。一見すると何も障害が生じているようには見えないため、問題は対処されずに潜航して慢性化する。その責任は設計者にある。

|

« 「モデリング&プロトタイピング手法」事例紹介 | トップページ

コメント

単独主キー派です。

一つ質問なのですが、もし、(5)から(6)でデータ重複エラーが発生する可能性があり、それこそが「id主キー」の弱点だとおっしゃるのであれば、同様の問題は(2)から(3)でも発生すると思うのですがいかがでしょうか。

投稿: Error401 | 2017.05.29 18:25

コメントありがとうございます。おっしゃるように、重複エラーが発生するという点では(2)→(3)も(5)→(6)もいっしょで、それが単独主キー主義の弱点ということではありません。重複エラーに対処するためのコストは同等です。言い換えれば、単独主キーを常に付与することで何かが楽になるわけではないということでもあります。

では弱点は何かというと、「本来必要とされるユニーク制約や更新不可制約を見落としやすくなる」点にあります。しかも、見落としてたとしても、一見するとあたかも何も問題が生じていないように見えてしまう。

いっぽう、ユニーク制約や更新不可制約をきちんと見極めたうえで単独主キーを常に付与しているのだとすれば、当初の論理設計のとおりに実装できればいちばん楽です。環境を問わずに常に単独主キーを付与するやり方にこだわる必要はありません。それが記事の論旨です。

投稿: わたなべ | 2017.05.29 21:29

お世話になっております。さて,「単独で識別できるものには,単一のid(自然キーでない=業務の都合で変更されることがない)を付与して識別し,組み合わせで識別すべきものには,参照する先のインスタンスのidの組合せ(必要に応じてシーケンスorタイムスタンプなどを追加)により複合主キーを構成して,それを用いて識別するように設計しましょう。」という渡辺先生のご主張は完全に理に適っており,納得できるものだと思っています。

ただ,複合主キーの悩ましい点として,①複合主キーを外部キーとして参照するには,その参照キー(外部キー)も複合項目になってしまうということがあります。例えば,掲載例のように「仕入先idと商品idと開始年月の組み合わせ」で識別されるべき契約の存在を前提とする取引は,自然と「仕入先idと商品idと開始年月の組み合わせ」をその主キーの一部として持つようになるはずです。

さらに,この取引を前提とするさらなる取引は,この取引の識別子を自ずとその主キーの一部として持つようになるはずです。かくして,複合主キーがどんどん雪だるま式に長くなっていく悩ましい問題があるように思います※1。

また,②複合主キーの場合,そのカラムの並び順に関する問題があるように思います。同じ例で,本来「仕入先idと商品idと開始年月の組み合わせ」で識別されるべきエンティティは,それらの並び順を気にせずに定義してしまったとしても問題が生じては困ります。けれども,あるテーブルでは「仕入先id,商品id,開始年月」の順に定義されており,別のテーブルでは「商品id,仕入先id,開始年月」の順で定義されているような状況は容易に起こり得ます※2

そこで,質問させていただきたいのですが,渡辺先生はこのような問題について日常どのように対処されていますか?複合主キーが使える環境であっても,どこかのタイミングで単独主キーに置き換えたりされる場合もあるのですか?

※1実際に,固定資産税のドメインをモデル化したところ,(固定資産の)所有は住民idと固定資産idおよび年度の組み合わせで識別され,それを前提に課税(キーは,住民idと固定資産idと年度と課税日時)し,課税を前提に納通(キーは,住民idと固定資産idと年度と課税日時と納通日時)し,納付された場合はよいけれど,されなかった場合は,納通を前提に督促(キーは,住民idと固定資産idと年度と課税日時と納通日時と督促日時)し,...とやっていると催告あたりでは非常に長い複合主キーになってしまいました。私は,どこかで間違ったのでしょうか。

※2まあ,次のようにSQLで常に項目名と値を指定してアクセスしている分には問題ないと思うのですが,
select 契約仕入単価 from 商品仕入契約 where 仕入先id = 100 and 商品id = 500 and 開始年月 = "2016-04"
JDBCなどを経由してカラムの並び順に依存したアクセスが行われる場合もありますので,心配なところです。

投稿: いだあきお | 2017.06.04 08:20

いださん

識別子を構成する項目が多くなりがちという話ですね。結論から言えば「事実であればそれが正しいし、そうすべき」です。柱の強度を保証するために24本の鉄筋が必要であれば、それが正しいしそのように設計されるべきです。

とはいえ、これは原則論で、2つの理由からそれほど問題にはなりません。まず、項目数はそれほど多くはならないという点です。私の経験では最多で7個の項目が複合されていました。言い換えれば、それ以上の項目が複合されているとすれば、何らかの錯誤があると疑っていい。

もうひとつの理由はサロゲートキーです。トランザクション系の設計ではサロゲートキーを適宜組み込むことで、レコードの取り回しが楽になります。

かといって、年がら年中サロゲートキーを組み込むやり方ではコストがかかり過ぎる。サロゲートキーを組み込むためには、ユニーク制約や更新不可制約を考慮する必要があって、これは効果にともなう報いのようなもので余分なコストがかかります。

「サロゲートキー」は適切に利用されることで効果が出る重要なテクニックですが、年がら年中単独主キー(id)ばかりを置くやり方では、支払うべきコストが曖昧になってしまうのではないか。結果的にDB設計が簡単な作業だと勘違いされて、そのとばっちりが非常にわかりにくい形でユーザ企業にふりかかる。それが「単独主キー主義」で作られたDBの実態ではないかと考えるわけです。

投稿: わたなべ | 2017.06.05 09:33

お世話になっております。ご多忙中,ご回答感謝申し上げます。

「柱の強度を保証するために24本の鉄筋が必要であれば、それが正しいしそのように設計されるべきです」

相変わらず胸のすくような表現をされますね。お陰様で,勇気づけられました。まあ,自分のモデルが業務の要請に照らして妥当であることを常に確認しながら設計を進めてはいるものの,識別子が長くなってくると,本当にこのモデルは要求の実現に対して便利なモデルになっているのだろうか,と不安になることがしばしばありました。なぜなら,業務要件に対して,厳密だけど使いにくいモデルと,多少厳密性には欠けるけれど使いやすいモデル,というものが実はあって,実務では後者のモデル方が歓迎されるような印象を持っていたからです。

投稿: いだあきお | 2017.06.12 05:41

コメントを書く



(ウェブ上には掲載しません)




トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/87674/65318408

この記事へのトラックバック一覧です: 単独主キーでもDB設計は楽にならない:

« 「モデリング&プロトタイピング手法」事例紹介 | トップページ