前回はルックアップの機能を説明しました。象徴的な画像を再掲しておきます。この場合、販売明細の商品IDが更新されれば、商品ID同士で商品の表を参照し、マッチしたレコードの商品名と単価を、同じ名前の商品明細テーブルのフィールドに値を複製するという仕組みでした。
まず、これを見ると、正規形の議論で出てきたように、「商品」を別の表に分けてあるのに、また、販売明細に商品名と単価が登場してきており、正規化されていない状態に戻すのかということが懸念されるでしょう。ですが、これは前提条件によって正規化は崩れたとも言えるし、崩れてないとも言えます。
まず、前提が「単価は一度設定すれば、未来永劫変わらない」という場合はどうでしょう？おそらく、単価が変わったら、商品番号が変わる、あるいは商品そのものを違うものとして認識するというルールで行くのなら、「商品」の表は、初期値に固定されるでしょう。そうなると、販売明細に「商品名」「単価」があるのは、第三正規形の規則に崩れます。つまり、{商品ID}→{商品名, 単価}という関数従属性、すなわち、商品IDが決まれば、商品名と単価が一意に定まるという状況がデータによって作られていると言えるからです。言い換えれば、このような前提がある場合、ルックアップする必要はありません。必要な時にテーブル結合すればそれで必要なデータは得られます。また、ルックアップして複製してしまうと、「間違ったので修正したい」という場合の修正作業が煩雑になってきて、更新不整合問題が発生します。これらは正規化の議論で出てきたことです。
一方、前提が「単価は時々変更されます」という運用状況ではどうでしょう？ ルックアップをしない場合は、単価を変えると、古い販売データまでみんな変わってしまいます。一方、ルックアップによりその時点での単価の複製をフィールドに保持しておくと、「商品」の単価を変更するだけで、まさにその時点の単価がコピーされるということです。営業担当がサボって単価変更前の納品書を作っていない場合には、ルックアップで新価格が複製され都合が悪いかもしれませんが、気付けば手作業で修正もできるので運用上問題ないと思われるかもしれません。このように、その時点の値を保持するということは、時間軸上に変化するデータであっても、ルックアップで複製を残すことでその時点での正しい値を複製して残しておくということができており、もしかすると概ね問題ないのかもしれません。
ただ、ここは議論のしどころです。ちょうど、製品の1つがある日に変更されてしまった結果の「販売明細」の表があるので、それをよく見ると、{商品ID}→{商品名, 単価}という関数従属性は確かにないかもしれません。1行目と4行目については同一商品IDに対して「単価」が異なるので、関数従属性はないと判断できます。しかしながら、よく考えると、「商品の単価は時期に応じて変化する（決定される）」という見方をすると、{(販売日), 商品ID}→{商品名, 単価}という関数従属性はあるのではないかと見ることができます。販売日に()を書いたのはちょっとした気持ちで、「直接照合はできない」ということを意味するものとしてください。具体的にどうするかということは後で考えるとして、時期と商品から、商品名と単価が決まるというデータの関係性を見出したとします。ここでは商品名は変化ありませんので、商品名は商品IDから一意に決定され、要するに名前が決まったら絶対に変えないということにしておきます。そうすると、{商品ID}→{商品名}、および{(販売日), 商品ID}→{単価}という、2つの関数従属が「販売明細」に存在します。
つまり、主キー以外のキーフィールドによる関数従属が２つも存在しているようなので、結果的に3つのテーブルに分割できるというのが第三正規形の考え方です。もちろん、上記の2つの関数従属性のキーは、主キー「販売明細ID」のスパーキーではないので、ボイス-コッド正規形を満たしていないという見方もできます。この表は、このように、一定の前提条件を考慮すれば、明らかに正規形を満たしておらず、表の分割をする強いモチベーションになります。
ただ、ここで、販売日をそのまま扱うのかという問題があります。日付をドメインに考えたとき、これも正規形の議論での会議室予約の問題として出てきましたが、無限大に取り得る値を取り、全部の値を書き並べることは現実的ではないようなことが起こり得ます。日付は年月日時分秒の複合オブジェクトなどと考えると非常に複雑だと思ってしまいますが、一方で、日付は整数と同じように、連続しつつ、ステップで変化するという特徴があります。整数の桁のように、すべて同じルールで桁が変わるわけではないし、閏年など面倒なルールもありますが、連続しつつ、ステップであるという点だけを見れば、整数と同じ判定ができます。そこで、こうした日が絡むものは、「期間」という概念で包括してしまうという手法が考えられます。実際、単価の変化を「正確に」追いたいとしたら、単価が変わった日と、その単価が終わった日を記録するのがまずは妥当と考えます。終わった日は次の単価の前の日という考え方もできるのですが、データベースはレコードをまたがった判断はあまり得意ではありません。ここでは、開始日と終了日があるという考え方を取り入れて考えれば、今までの商品IDの照合のような=演算子ではなく、開始日<=販売日 AND 終了日>=販売日 といった判定を行うことで、単価の表を別に用意して照合可能にすることができるのではないかと考えてみます。つまり、次のような表の分割ができるということに目を付けます。
元の販売明細の表を作る手順について、商品は商品IDで照合するのは今まで通りです。ここで、単価は{(販売日), 商品ID}→{単価}という関数従属だったので、2つのフィールドに関わる照合が必要です。つまり、商品IDが一致するもの、かつ、「開始日<=販売日 AND 終了日>=販売日」という式がキーフィールドの照合に相当する検索処理になるわけです。具体的に見てみましょう。販売明細の1行目と4行目に商品IDが102のレコードがありますが、1行目は11/9です。なので、開始日がそれよりも後でかつ終了日がそれよりも前のものとしては、単価の表の2行目だけがあり、2行目のみにマッチします。1レコードへのマッチなので、単価は800円と決定できます。一方、販売明細の4行目も同じ商品IDですが、マッチするのは単価の4行目だけなので、単価は845円に決定できます。SQLで表の合成を記述すると、次のようになります。そのまま動くかどうかは微妙ですので、考え方の確認としてご覧ください。
SELECT 販売明細ID, 販売日, 販売先顧客名, 販売明細.商品ID, 個数, 商品名, 単価
FROM 販売明細
INNER JOIN 商品 ON 販売明細.商品ID = 商品.商品ID
INNER JOIN 単価 ON 販売明細.商品ID = 単価.商品ID
AND 開始日<=販売日 AND 終了日>=販売日3つの表を結合する場合は、このように、FROM句にJOINを複数記述します。順序に依存するような場合には ( ) も使いますが、この場合は商品も単価も1レコードないしはマッチしないということを想定しているので、前から順に表の結合を行うことで問題はありません。
単価の表について、候補キーを考えると、商品IDと販売日がキーですので、商品IDがあるのは良いとして、販売日はフィールドにありません。ですが、判定の材料として、開始日と終了日があるので、これらはキーフィールドの可能性があります。ここで、開始日と終了日を「正しく入力する」、言い換えれば「重複する期間がないように入力している」という仮定を置けば、商品IDと、日付のどちらか一方で候補キーが成り立ちそうです。終了日は、他のレコードの状況から決められるとしたら、ある種の従属性があると言えます。単純な式では無理としても、アルゴリズムは書ける範囲だと判断すれば、開始日のみで候補キーを構成するキーフィールドになり得ると考えることができます。また、以下に記載するように、終了日は修正の必要性があるので、主キーとしては好ましいとは言えません。ということで、商品IDと開始日を主キーとしました。もちろんNOT NULLや重複を許さないインデックスの追加も必要なら行いますが、終了日を正しく入れないと判定が崩れる点では、データベースのサポートはそこまでは及ばないところになります。
ここで、終了日の2999-12-31ってなんだと思われるところでしょう。仮に、これをExcelの表で作ったら、きっと多くの方は、現在使用している単価は「終了日」は空欄にしましょう。という聞かないと分からない、だけど1度聞くとすぐに理解できるルールでデータ入力されると思います。もちろん、そうしたいところですが、SQL文の単価より後のON以下の条件がもっと複雑になりそうなので、現在使用している単価は、筆者の生存期間から推定して永遠に思えるずっと先の日付として、2999-12-31を指定することにします。どうしても空欄を採用したい場合は、若干複雑になる検索式を記述する方もありますが、空欄なら2999-12-31を自動的に埋めるビューを回避するなどして、NULLを回避する方法もあるかもしれません。なお、この前提（終了日と開始日に必ず日付が入っている）を成り立たせるためには、新たな単価を入力したときに、以前の単価のレコードの「終了日」フィールドを更新しないといけなくなります。
かくして、ルックアップを駆使して作った表も、やっぱりテーブル分割できるじゃないか、やっぱり正規化は素晴らしい！とも言えるのですが、そうともそうとも言えない可能性もあることを次回に説明します（なんか、このテーマ長いですね）。ところで、今回の単価テーブルはなんだか汎用性高そうですが、そうでもないので気をつけてください。常に前提があるのだということを認識するのが重要です。例えば、ここまでの前提で開発を進めているのに、「いくつかの商品は、一定の期間割引します」みたいな話が出てくると、いきなり前提が崩れます。この二重価格？の根源はなんだろうかと調査すれば、Amazenだけ特定の土日に安くしないとダメなんだとわかり、そうなると、単価の関数従属性は{(販売日), (販売チャネル？), 商品ID}→{単価}のようにややこしいキー構造になるのかもしれません。これは、「それ以外のチャネル」をどううまくデータ構造に埋め込むかが勝負な感じです（そうしないと、全チャネルをリストアップすることになる）が、結構複雑そうですし、実装のやりやすさなども設計に影響しそうです。システムの設計変更も、変更前と変更後でどのような前提条件の変化があるのかを意識すると、見通しが良くなる場合もあります。ともかく、どんな場合にもうまくいく設計というのはおそらくありません。