Blog by msyk

ちょっと間が空いてしまいました。タイトルのテーマでサクッと書こうと思って調べ始めると、迷路に入ってしまいました。真面目に、全ての情報を書くのは多分、この話題はかなり辛いでしょう。特にMySQLの問題がありますから。ですので、いい感じのストーリにするのにちょっと時間がかかりました。マニュアルやブログを大量に読みましたが、なるべくシンプルにまとめたいと思っています。

文字列をフィールドに保存するとき、VARCHARかTEXTのいずれかの型を使うことになるでしょう。VARCHARはCHARACTER VARYINGの短縮形なのですが、VARCHARの方がお馴染みなので、こちらの表記にします。標準SQLではTEXTは定義されていないのですが、MySQL、PostgreSQLではこの2つの型のどちらかを使います。CHARという型もあるのですが、文字数を指定して定義し、足りない場合にわざわざ空白で埋めるようなことをするので結果的に固定長の文字列になりますが、現状のアプリケーションではそんなことは不要なことが多く、可変長の文字列型しかほぼ使わないでしょう。

そこで、２種類の型があるのですが、大きな違いはVARCHARは、VARCHAR(10)として、10文字（バイトではない）までの文字列を保存できるようなフィールドを用意するのに対して、TEXTは長さは指定せず、データベースの動作上の制限の長さまで入力できます。ここでいきなり例外が出ますが、PostgreSQLについては、VARCHARだけを書いて( )なしにすることで、長さの制限を無しにすることができるのですが、今回の記事では、VARCHAR(n)かTEXTかという表現で通します。

まず、２種類の型の大きな違いは、文字数の制限ができるかどうかです。文字数を制限したいかどうか？ 実は制限しなくてもいいような場面が多いと思われます。逆に制限してしまった方が、意図せず制限より長い文字列を保存できなくなってしまうという問題に遭遇します。しかしながら、都道府県を保存するのにTEXTを使うのかという、かなりどうでもいいような問題に頭を悩ませてしまいます。つまり、せっかく制限が可能なのに利用しないのかというジレンマにつきまとわれてしまいますが、論理的に考えれば制限が必要になることはあまりないと思われます。

ただ、処理速度が違うだろうということは気になります。先に結論を言えば、ほぼ同じだと言えるくらいでしょう。PostgreSQLはマニュアルに、この2つの型は目立った違いがないと書いてあります。MySQLについては、１レコードのブロック内に、実際のデータを並べるのが基本動作ですが、TEXTについては原則としてそのブロック外にデータを保存するので、要するにVARCHARよりもTEXTの方が処理に時間がかかる傾向があるはずです。しかしながら、実際の運用等でもそんなに大きな差があるようには思えませんし、ベンチマークを公開しているブログ記事などを見る限りは僅かな差です。長大なデータ処理をするような場合には違いが出るかもしれませんが、一般的なUIを絡むようなアプリケーションでは多分違いはほぼ顕在化しません。ネットワーク等の他の要因の方がよほど大きく効いてくると考えられます。パフォーマンスに違いがないのなら、制約の少ないTEXTの方がいいじゃないかと思うところです。

しかしながら、MySQLに微妙な違いがあって、VARCHARはDEFAULTによる既定値の指定が可能なのに対して、TEXTはそれができません。PostgreSQLはTEXTでもDEFAULTの指定は可能です。そして、なんと、MySQL互換のMariaDBも、TEXT型でのDEFAULT指定はできます。互換を謳いながらもより高機能になっています。

この既定値については確かにデータベース定義上で行っておけば、プログラムコードは一切なく、フィールドの初期値が決められて便利です。しかしながら、都度、初期値が違うようなことも多く、結果的にプログラムで初期値を決める必要がある場合もそこそこあると思われます。長さの制限ができるという意味では、確かに不要な長いデータを保存してしまう危険性は排除できますが、本当に長さの制限をしたい場合は、おそらくUIで確認をして、長さが超えていればそもそもデータベースに命令を送ることはしないようなシステムの動作をすることができるわけです。ということで、初期値も、長さ制限も、システム内部でのケアがどっちにしても必要だとすれば、２つの型をチョイスする積極的な理由にはなりにくいでしょう。

ちなみに、SQLiteではTEXTのみです。FileMakerでも「文字型」としてフィールド定義できますが、長さの指定はできません。そんなわけで、PostgreSQLだとTEXTでいいだろうということになりそうです。MySQLではDEFAULTを設定したいかどうかによって変わるけど、長さ制限する意図がない場合にはTEXTで良いという結論になりそうです。

ただし、TEXTにも制限があるということを理解しておく必要はあります。PostgreSQLは1GBまでなので、ある意味、滅多にそこまでは行かないでしょうけど、MySQLは64KBの制限があります。しかしながら、これはバイト数なので、4バイト表現のUTF-8が基本となっているMySQLだと、最大で15,000文字くらいになり、書籍1冊分のテキストは入らないかもしれないぞというサイズです。MySQLは最大長がもっと多い、MEDIUMTEXTやLONGTEXTという型があるので、場合によってはそちらを使うことを検討する必要があります。

MySQLについては、文字列の全てがインデックスに利用されるわけではなく、Ver.５.7以降では3072バイトまでになります。また、インデックスを作成するときに、最初から何文字まで取り出すのかということを、VARCHARでもTEXTでも指定は可能です。さらに、LIKE演算子での検索では、LIKE ‘A%’のような前方一致の検索の場合しかインデックスは使用しません。長い文字列を扱う場合はインデックス対象になる範囲があることや、LIKE ‘%A%’のような〜を含むの検索をしたときにインデックスが使われないということにも注意が必要です。長い文字列に対しての検索の効率を上げるには、FULLTEXTインデックスという機能を使う必要があります。クエリーのSQLも若干違うものを使う必要がありますが、データが大きくて検索が遅い場合には検討する必要が出てくるでしょう。

MySQLのことを調べていたら、懐かしい「8KBの壁」の話が出てきました。MySQLは、8KBの領域を使って1レコードを管理する方法が取られていますが、Ver.5.6までの標準のセットアップでは、フィールドを増やせば、「思ったよりも早く」この8KBを埋め尽くしていたのでした。その時は、1つTEXTのフィールドは8KBのブロックのうち768バイトを使っていたので、10個ほどフィールドを作ればアウトだったのです。Ver.5.7からはTEXTはブロック内の20バイト程度しか使わないので、まずまずたくさんのフィールドが定義できたのでした。MySQLにはこうした「意外な頭打ち」がいろんなところにあるので、使いこなしたい方は、まずはマニュアルを熟読することが必要になるでしょう。

以前にフィールドに入れるデータは、元々どんなデータが入るのかという母集団的な集合があって、そこからチョイスされたものが入力されるという「ドメイン」の概念を説明しました。母集団は「定義域」とも言われますが、記述可能、不可能があるとしても、ともかく概念としては、ドメインがある前提です。それぞれの要素を記述するために、「ラベル」としての文字列が使われることが多いが、本質は文字列を記憶しているのではなく母集団の中の1つの要素が記述されているということです。

ここで、名前を記録したいと思った時、まずは姓と名をどうしようということが思い浮かぶと思いますが、分離分割するとしても、「名前の１文字目」「名前の２文字目」…などとさらに分割するようなことは考えません。私の姓である「新居」は、おそらく、日本人の姓の定義域に定義があって、「新」と「居」に分離してしまうと、姓の定義域から遠い、単なる漢字1文字になってしまいます。データベースのテーブルでのフィールドは、原則として何かの意味を持ちます。定義域があるという定義がその根拠になります。

では、姓と名はどうでしょう。データベースの設計として、ここは重要なところです。一般には、姓と名を分ける方が合理的とは言えます。なぜなら、「別々に扱う」ことがよくあるからです。例えば、名前が入った一覧表を作るときに、必ず姓名を表示する場合もありますが、姓だけで構わないというような場合があるとするとします。もし姓名をまとめて1つのフィールドに入れていた場合、そこから姓を分離するのは少し厄介です。日本人は大体姓も名も漢字では2文字だからというと、姓が1文字、名前は3文字という人もいらっしゃるわけで、確実に姓だけを取り出すことはできません。そこで、考えるのは、「姓と名の間は必ず全角のスペース」として、入力時にきちんと検証するという方法です。もちろん、それでうまく行きますが、データ構造からUI設計まで巻き込んで、姓名の分離が可能なデータ入力とその保持を行うというのは、なんだかコストに影響しそうな気がします。最初から分離しておけば、その問題はありません。非常に大雑把に言えば、データベースのフィールドは分割するのは大変というか、データベースの設計を超えた仕様の検討が必要になるのが一般的です。一方、データベースのフィールドを結合するのは、ビューを利用することで簡単に可能です。Webページだと、単にフィールドのデータを並べて配置すれば済みます。分割は大変だけど、結合は簡単ということから、分割可能なものは分割しておくのが良いということになります。

ただ、姓名分割方式だと、ミドルネームの扱いをどうするのか、更には、Family NameとGiven Nameの区別を意図通りしてもらえるのかどうかなど、外国の人の名前を巻き込むともっといろいろ考えないといけないかも知れません。今時はUnicodeでシステムは動くので、「入らない文字がある」という不便さはほぼなくなりつつはありますが、フィールドをどう用意するかは悩ましいところです。「名前」という1フィールドにして、とにかくその方を特定できる名前であればなんでもいいから入れておいてください的な割り切りがあるのなら、姓名の分割は不要です。その場合、姓だけを抜き出すというようなことは要求にないなど、名前フィールドの中身を系統的に分割して利用するということはしないという前提があります。例えば、送り状の宛先に使うだけだとかいった場合は、わざわざ姓名で分離しておく必要はおそらくはないだろうということです。もちろん、ここで、要求がしっかり確定していないといけません。このような場合で、データがすでにたくさん入ってしまった後から、やっぱり姓と名は分けましょうというのは、もはや簡単に移行できないことになってしまっているわけです。

名前と同様、住所も悩ましいですね。郵便番号＋都道府県名＋市区町村名＋町域名＋番地＋ビル名と室名、といった分離をするというアイデアから、逆に全部ひとまとめにして住所フィールドというやり方まで、どうすればいいでしょうか。基本的には名前と同じです。住所を単に封筒などへ印刷するときの宛先としてしか使っていないのなら、まとめてしまってもいいでしょう。分割してしまうと、必須のフィールドとオプションのフィールドがどうしてもできてしまうのですが、番地を必須にすると、北海道の大自然に囲まれた場所だと番地がなかったりして、エラーで先に進みませんとクレームになるかも知れません。ですが、１つの住所フィールドなら、郵便物や宅急便が届くようにとにかく好きに入力して貰えばいいのです。届かないのは入力した人が悪いと言えます。

しかしながら、分割するメリットはないのでしょうか？ それは大いにあって、住所を分割しておくことで、分類が非常にスムーズになり、集計や分析がしやすくなります。例えば、住所以外にアンケート結果なども一緒にあるとしたら、アンケートの回答について、都道府県別に集計するようなことが非常に容易になります。もっとも、そうした分類に使うとしたら、市区町村名までで、残りは一括でもいいような気がしますが、いずれにしても、後から都道府県名だけを取り出すということに力を注ぐよりも最初から分割されている方がスムーズなのはもちろんです。住所を１フィールドで運用してどうぞご自由にとしたら、きっと都道府県名は書いたり、省略したりということで、必ずしも含んでいるとは限らないでしょう。

結果的に、フィールドをその後に何に使うのかということを仕様として織り込んでいるのかで、その細かさあるいは荒さが決まるということです。さらにそれを突き詰めると、どんな定義域を想定しているのかということに他なりません。

ところで、一般には住所は「郵便番号＋都道府県名＋市区町村名＋町域名＋番地＋ビル名と室名」と分離して、それぞれ入力したりします。ただ、それだと不便だということで、郵便番号を入力すると、ルックアップして自動入力なんてUIもよく見ます。郵便番号は全ての住所を網羅しているので、{郵便番号}→{都道府県名, 市区町村名, 町域名}という関数従属性はありそうです。であれば、まさに表を分離して、郵政事業が配布している郵便番号のテキストファイルの内容を保持する表を用意し、住所の記録が必要な表では住所は、{郵便番号, 番地, ビル名と室名}だけでいいのではないかとも言えるかも知れません。もしくは、さらに割り切って{郵便番号, 残りの住所}でもいいかも知れません。郵便番号で照合すると元の住所は再現できるので、理屈ではそうなるでしょう。それでも、都道府県名をフィールドとして用意して入力させるのはどうしてなのでしょう？

もはや住所は文字としては入力していないのかも知れないと思わせるのが、ヤマト運輸のクロネコメンバーズのアドレス帳です。本当にどういうデータベース設計しているのかはわかりませんが、常に郵便番号から自動的に選択させ、番地等も選択入力をさせられます。おそらく、町域名よりも細かい住所データがあって、それを突き合わせながら入力することで、存在しない入力を極力避けることを意図しているのでしょう。それは、配送の効率化という意味では納得のいく対策です。

しかしながら、多くの方は、今でも、念の為に、郵便番号以外に都道府県名から文字列で入力しているでしょう。なぜか？ １つは仮に郵便番号から住所の一部を取ってくるという運用をするには、郵便番号と住所のメンテナンスが必要です。全国のデータが掲載しているファイルは1ヶ月に1回更新されており、最近は少ないですが自治体の合併や、住所の変更などが随時反映されていないければなりません。また、なくなる町名の扱いをどうするなども対処が必要です。要するにで、メンテナンスをきっちりとやらないといけないという点で、まずはコストとのバランスで多くの場合は躊躇してしまいます。

この点を除けば、都道府県などはフィールドとしてははいらないとも言えそうですが、今でも、郵便の表に書く宛先には、郵便番号だけでなく、都道府県はまだ一部省略するとしても、市区町村や町域名は書くわけです。もちろん、そこは差し出す人、受け取る人が見るということで、人間向けのUIであるという見方もできますが、住所は少々冗長であっても省略しないで記述することに慣れ切っている私たちにとっては、いきなりデータベースの理論上これは不要だと言われてもちょっとねーと思ってしまう側面があるのではないかと推測されます。少なくとも、入力時に都道府県を入れない入力ページはインターネットのベテランでも首を傾げそうです。習慣を変えるのは、データベースの設計を変えるよりも大変なのです。

ルックアップの話は３日かかりました。今回で話は終わるはずです。ここまでに、ルックアップの機能を説明し、便利ながらも正規形の議論から外れた設計になっているとも言えるという話をしました。以下はルックアップによって、「商品名」「単価」が別の商品の表から複製された販売明細です。

一方、以下は、上記の表で、{商品ID}→{商品名}、{商品ID,(販売日)}→{単価}という関数従属性があることを見つけたとして、テーブルの分解をおこなった場合です。販売日と、開始日及び終了日で表現される「販売期間」の照合は、大なりや小なりを使って検索条件を組み立てないといけないので、商品IDのような単純な=での照合よりもかなりややこしくなっていますが、理屈としては理解していただけると思います。

このような場合、後者のテーブル分割をした結果の方が、データベースの理論上は「正しい」とも言えるのですが、実はいくつかの点で躊躇してしまいます。どちらの設計で進めるかはかなり考えないといけませんし、発注側と協議も必要です。何を懸念しているかと言えば、まずは、「新しい要求を作ってしまっていないか」という点です。単に販売の記録をしたいだけだったのに、単価の管理という新しい機能が増えています。もちろん、開発の作業量が増えることもありますが、それよりも、利用者のワークフローに変化が出てきて、場合によっては作業量は大きく増えます。要するに、「かえって面倒になる」機能を実装しようとしていないかという懸念があるのです。利用者の要望が「ともかく伝票が作れたらいい、価格はその都度変えて、たとえば、3年前の5月はいくらだったかというのは伝票を見れば良い」というくらいのことであれば、テーブル分解をしていない手法の方が何かとお手軽であることは言うまでもありません。一方、利用者が、「それでは単価テーブルをきちんとメンテナンスしましょう」という気持ちになっているのなら、テーブル分割することによる意義はあるでしょう。もちろん、今回のサンプルであれば、「過去の単価の変遷をきちんと追える」といったメリットは言えるのですが、それがどうしたと言われればメリットにもなりません。色々な立場で、手間との天秤をかけることになります。検討するためには、テーブル分割した時のメリットやデメリットをしっかりと把握しましょう。

仮にテーブルを分割したとしても、前回説明した「前提に合った」正しいデータであるという状態を保持するために、色々な対策を考えないといけません。少なくとも、ユーザーインタフェースにそうした仕組みを入れないといけないでしょう。まず、同一商品で期間が重複していないようにしなければなりません。もし、重複した期間があった場合、その期間内の日に販売日が入っているとしたら、その結果は合成した表では２つのレコードとして登場します。１件の販売が２件の販売に膨れ上がってしまうので、仮に単価が同じであっても事実を曲げていることになり、これは問題です。入力時にチェックすればいいというとそれまでですが、実はこの期間の重複がないという判定は、レコードをまたがって判断が必要になるため、単純な式では求められません。どう実装するかは工夫のしどころでしょう。画面上に同一商品で検索した結果を一覧して、下から舐めて、重複がないかをチェックするなどの方法が考えられますが、いずれにしても、そうしたチェックなしにかかると期間の重複が発生する可能性が高いため、必ず設計に盛り込まないといけません。また、終了日に遠い日を設定するというやり方で説明しましたが、前回にも指摘したように、NULLを入れることで未来永劫を示す方法にしたい場合には、それも対処が必要になります。

では、ルックアップで済ませることでも概ね大丈夫な線引きはどの辺にあるのかと言うことですが、これは一概に言えないものの「紙で運用していたもの」は、感覚的な話になりますが、だいたいルックアップの方が実情に合っていると言えるかもしれません。たとえば、病院のカルテなどは、昔は紙の束だったので、要するに、リンクしていると言う概念はほとんど及びません。その時に記入したら、それが残るのです。ある患者の保険証番号は１つに決まるはずですが、転職などして変わることもあります。その変化を緻密に追うことは病院業務としては考えにくく、診察に来た日の保険証番号が、その日のカルテに記載され、請求書や領収書に使われるとして、要するに、「今現在の保険証番号」が患者マスターにあって、診察日にはそれだけが関心の対象であるので、それを「複製」して使用するレイアウトで見えていればいいわけです。むしろ、保険証番号を参照して表示しているとしたら、番号が変わった場合に過去の紙帳票の内容も変わってしまって、これはむしろまずいでしょう。もちろん、診療録関連でも期間を管理する必要があるようなデータもあるかも知れませんが、概ね紙で運用していた資料にあるようなものは、ルックアップでことが足りる事が多いと言えます。

一方、ルックアップで実装すると、単価や商品名などを「編集可能」にすることもよくあります。間違えた時などに手軽に処理をしたいというような事があります。ですが、商品IDを切り替えずに、手で商品名と単価を入れるのは、参照不整合を起こさせるようなものとなります。編集は気軽にできないようにするのが１つの方策ですが、利便性のために利用者からは編集可能にしてほしいと言われるかも知れません。そうなると、きっと、商品名のところに、前の行の商品の説明として「納期は今現在未定です。」みたいな注釈を書いてしまうかも知れません。それでも、商品IDが未選択なら問題はないとも言えます。多くの人は、表形式の画面を見るとExcelを起想します。下手をしたら、「なんでここで下にコピーできないんだ！」とまで言われることもあります。それは極端な例としても（でも、Webアプリで言われたことありますね）、Excelの時のように入力し、編集したいとどうしても思ってしまいます。この辺りは、運用方法を開発者が想定する適切なものでとお願いしても変えてくれないこともよくあります。つまりは習慣化してしまっているのでしょう。本来、注釈があるなら、別のフィールドを用意するのが正しい対策ですが、編集可能にした結果、意図せずそういう使われ方をすることは十分に考えられます。いずれにしても、フィールドを編集可能にしてしまうのかどうか、よく検討が必要です。編集できないのは不便です。できると統制が取れなくなってしまう可能性があります。これは、落とし所が難しい問題になります。

ここでの単価の表のような事例としては、人事の記録があります。ある期間にどの部署に所属していて、どんな肩書きだったのかと言うような記録が確実に取れます。肩書き等が他のデータに対して影響のあるようなシステムの場合は、やはり、期間を記録する表は必要になります。ある案件で、そう言うものを作った事があるのですが、元々、人事のためのマスター的なものがなかったのか、あるいはシステム適用の範囲内で存在しなかったのか、ともかく、その表のメンテナンスという新しい仕事が発生してしまい、システムが使われないままになった原因の1つを作ってしまったという残念な経験があります。

システムの設計にはトレードオフは至ることで出くわします。データベースの設計だけなら、テーブル分割することでの効率化や、データの多角的な利用といういいことばかりが見えるのですが、システム全体として、そしてそれを利用する人たちのワークフローにどんな影響を与えるのかということを常に俯瞰しながら設計を進め、実装を行い、検証を行うという必要があります。まずは、設計の上でトレードオフが発生し、それぞれのメリットやデメリットを把握することが重要です。その上で、発注側あるいは利用者に丁寧に説明をして、判断ができる状態を作って共同で検討を進めるのが理想的でしょう。

前回はルックアップの機能を説明しました。象徴的な画像を再掲しておきます。この場合、販売明細の商品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}→{単価}のようにややこしいキー構造になるのかもしれません。これは、「それ以外のチャネル」をどううまくデータ構造に埋め込むかが勝負な感じです（そうしないと、全チャネルをリストアップすることになる）が、結構複雑そうですし、実装のやりやすさなども設計に影響しそうです。システムの設計変更も、変更前と変更後でどのような前提条件の変化があるのかを意識すると、見通しが良くなる場合もあります。ともかく、どんな場合にもうまくいく設計というのはおそらくありません。