「有効期間」を含むテーブルとの参照関係

　一次識別子に「有効期間」が含まれることがある。そんなテーブルに対して関連を張ってゆくと正規化違反を生じてしまうケースがある。これを避けるためには「動的参照関係」の知識と、これに対応した開発基盤が必要だ。よほど単純なＤＢでない限り「動的参照関係」のデータ要件は出現するものなので、実務者としてはしっかり理解しておきたい。

　まず、有効期間とキーとの関わりを整理しておこう。「商品値引テーブル」を例にした場合のモデリングパターンをいくつか挙げよう。{...}内はキー（一次識別子）を表している。

(1)開始日・終了日ともに属性

[商品] {商品ID},商品名,...
＋　　　12345　全自動漬物石ＴＳ１００
｜
└―…[商品値引] {商品値引ID},商品ID,開始日,終了日,値引率,...
　　　　　　　　　　　　　000001　　12345　07/01　08/31　　15
　　　　　　　　　　　　　000002　　12345　08/01　09/30　　10

(2)開始日がキーに含まれる

[商品] {商品ID},商品名,...
＋　　　12345　全自動漬物石ＴＳ１００
｜
└―∈[商品値引] {商品ID,開始日},終了日,値引率,...
　　　　　　　　　　　　12345　07/01　08/31　　15
　　　　　　　　　　　　12345　08/01　09/30　　10

(3)開始日・終了日ともにキー

[商品] {商品ID},商品名,...
＋　　　12345　全自動漬物石ＴＳ１００
｜
└―∈[商品値引] {商品ID,開始日,終了日},値引率,...
　　　　　　　　　　　　12345　07/01　08/31　　15
　　　　　　　　　　　　12345　08/01　09/30　　10

　「商品値引テーブル」について、(1)では「商品ID」が属性項目として置かれているいっぽう、(2)と(3)では一次識別子に包含された形で置かれている。つまり「商品テーブル」との関係において、(1)は参照関係で、(2)と(3)は親子関係である。具体的に言えば、(1)において開始日も終了日も後で変更できるし、(2)では終了日のみ変更可能で、(3)ではいずれも変更不可ということだ。

　何の変哲もなさそうだが、危うい問題が含まれている。インスタンスとして示したように、いずれのパターンでも８月１日における全自動漬物石ＴＳ１００の値引率が１０％でもあり１５％でもあるという事態を招いている。矛盾だ。

　これを避けるために、複数の値引率が見つかった場合の「優先順位」を決めておくといった工夫も考えられるが、この場合なら「商品値引メンテナンス」のプログラムで有効期間の重なりが生じないようにあらかじめチェックするのが手っ取り早い。ただし(2)については、次のように重なりが生じ得ないようなモデルにすることも可能だ。

(4)終了日を導出項目として置く

[商品] {商品ID},商品名,...
＋　　　12345　全自動漬物石ＴＳ１００
｜
└―∈[商品値引] {商品ID,開始日},(終了日*1),値引率,...
　　　　　　　　　　　　12345　07/01　(07/31)　　　15
　　　　　　　　　　　　12345　08/01　(*Max)　　 　10

*1.同一商品IDを持つレコード群の中での次の開始日の前日

　(4)では「終了日」が導出項目として置かれている。こうすれば、複数の値引率が対象になる事態は生じ得ない。ただしこのモデルでは、いったん最初の開始日が到来したら、それ以降に「商品値引が適用されない期間」が存在できない。「商品値引が適用されない期間」を置きたいのであれば、必要に応じて値引率０％の期間を示すレコードを挿入すればよい(5)。

(5)値引率０％の期間を示すレコードを挿入

[商品] {商品ID},商品名,...
＋　　　12345　全自動漬物石ＴＳ１００
｜
└―∈[商品値引] {商品ID,開始日},(終了日*1),値引率,...
　　　　　　　　　　　　12345　07/01　(07/31)　　　　15
　　　　　　　　　　　　12345　08/01　(08/31)　　　　10
　　　　　　　　　　 → 12345　09/01　(*Max)　　　　　0

*1.同一商品IDを持つレコード群の中での次の開始日の前日

　以上の違いは本記事のテーマにおいて重要ではないので、とりあえず上掲(2)のパターンにもとづいて本論に移ろう（それらのどのモデルを前提にしても次の議論の本質には影響しない）。「商品値引テーブル」を参照先とするようなテーブルを考える。単純な「受注テーブル」を例としてそれらの関係を見よう。

(6)商品値引と受注の関係

[商品値引] {商品ID,開始日},終了日,値引率,...
＋
｜
└―…[受注] {受注ID},顧客ID,受注日,商品ID,開始日,...

　(6)の受注テーブルは受注IDをキーとしていて、顧客ID、受注日、そして商品値引テーブルに対する外部キーとして商品IDと開始日とを持っている。そして、業務ルールとして「受注においてどの期間の商品値引が適用されるか」が明らかになっているはずで、ここでは「受注日」にもとづいてそれが決まるとしよう。そのような基準日は、管理項目としてテーブル上に保持されていなければならない。

　見たところ何ともなさそうだが、やっかいな問題を含んでいる。受注データを新規追加すると、受注テーブルの開始日には受注日に応じた値がセットされているはずだ。では、いったん追加したあとで受注日の値を更新したらどうなるか（受注日と受注登録日とが異なると考えれば、事態としてじゅうぶんあり得る）。

(7)受注日を変更する

[商品値引] {商品ID,開始日},終了日,値引率,...
＋　　　　　　12345　07/01　　07/31　　15
｜　　　　　　12345　08/01　　08/31　　10
｜
└―…[受注] {受注ID},顧客ID,受注日,商品ID,開始日,...
　　　　　　　　　34567　11111　07/10　12345　07/01
　　　　　　　　　　　　　　　　　　→08/10　　　→?????

　入力間違いに気づいて、受注日を07/10から08/10に変更したとする。この場合、適用されるべき商品値引の開始日が、07/01から08/01に切り替えられなければならない（結果的に値引率は１５％から１０％となる）。なぜならそれが業務ルールだからだ。しかし、ユーザにその整合性維持の責任を負わせるわけにはいかない。なぜならこれはシステムの設計上の問題だからだ。すなわち、このモデルでは開始日が受注日と商品IDに関数従属している、つまり正規化違反している。

　この問題を避けるために、整合性維持については受注メンテナンスのプログラムやトリガーに面倒見てもらうという手もあるが、じつはモデルのレベルで対処できる。(8)のように、「受注日と商品値引テーブル上のレコード群にもとづいて導出される項目」として「開始日」を置けばよい。これによって、受注日の現在値にもとづいて商品値引テーブル上の値引率が正しく対応されるようになる。

(8)開始日を導出項目として置く

[商品値引] {商品ID,開始日},終了日,値引率,...
＋　　　　　　12345　07/01　　07/31　　　15
｜　　　　　　12345　08/01　　08/31　　　10
｜
└―…[受注] {受注ID},顧客ID,受注日,商品ID,(開始日*1),...
　　　　　　　　　34567　11111　08/10　12345　(08/01)

*1.商品IDと受注日と商品値引データを参照して導出される

　つまり(8)は、「導出項目を含む外部キー」の事例なのである。このような外部キーで構成される参照関係を私は「動的参照関係」と呼んでいる。どういうわけか「動的参照関係」はほとんど語られることがないのだが、特殊なデータ要件ではなく、一定以上複雑なデータベースであればふつうに出現する。

　蛇足ながら、複合キーを避けてサロゲートキーを導入することによって、この種の問題を避けられると勘違いされることがある（上掲(1)を前提にしたような形になる）。この例での「受注日が変更されたときにそれに対応する商品値引が適用されない」という問題は、サロゲートキーを用いた場合でもふつうに生じる。むしろ、サロゲートキーを使うことで問題が見えにくくなる。見えにくくなっただけで、問題が存在しないなどと考えてはいけない。

　残念なことに、「動的参照関係」が話題にならないゆえなのか、開発基盤のレベルでも対応が遅れている。「導出項目」が対応されていたとしても、導出項目にもとづく動的参照関係を構成できるような開発基盤やフレームワークはごく少ない。

　業務システムの特徴のひとつが「複雑なＤＢ構造を伴う」という点だ。そうであるなら、複雑なＤＢ構造にともなう実装上の配慮を少しでも減らせる（言い換えれば、正規形のまま実装できる）――開発基盤はそういうものでなくてはならない。そして、言うまでもなく、開発基盤での対応が遅れていることが「動的参照関係」をモデリングのレベルで無視していいことの理由にもならない。案件開発者だけでなく、開発基盤の開発者も参考にしてほしい。

本ブログでの参考記事：
「動的参照関係」を手なづける