Category: データベース

ちょっと間が空いてしまいました。タイトルのテーマでサクッと書こうと思って調べ始めると、迷路に入ってしまいました。真面目に、全ての情報を書くのは多分、この話題はかなり辛いでしょう。特に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}→{単価}のようにややこしいキー構造になるのかもしれません。これは、「それ以外のチャネル」をどううまくデータ構造に埋め込むかが勝負な感じです（そうしないと、全チャネルをリストアップすることになる）が、結構複雑そうですし、実装のやりやすさなども設計に影響しそうです。システムの設計変更も、変更前と変更後でどのような前提条件の変化があるのかを意識すると、見通しが良くなる場合もあります。ともかく、どんな場合にもうまくいく設計というのはおそらくありません。

ルックアップの機能はFileMakerではお馴染みでしょう。FileMakerがリレーショナルデータベースである以前のかなり初期の頃からあった機能です。むしろ、大昔はこの機能だけがテーブル間連携だった時代もあります。その後、リレーショナルデータベースの仕組みが組み込まれても、ルックアップの仕組みは残っています。つまり、必要だから残っているとも言えます。そのこともあって、筆者が開発しているフレームワークのINTER-Mediatorでもルックアップはサポートしています。ということで、「さまざまな開発ツールでサポートされているルックアップ」と言いたいところですが、どちらかと言えば、ローコード系でよく実装されているということになるでしょうか。

ルックアップの詳細を説明する前に、まず、テーブル間結合のことをおさらいしましょう。お馴染みの、販売明細と商品の表をまたまた出してきます。サロゲートキーを主キーにしました。そして、それぞれの表の商品IDが照合の手がかりです。この2つの表を結合することで、下のような表が得られるので、ここでの「商品明細」だけではわからなかった商品名や単価が、販売１つ１つのレコードに追加されており、例えば、1行目の商品は800円などとわかるようになります。その結果、納品書の明細に記載ができるデータになったとも言えますし、単価と個数を掛け算して、金額を求めることができるようになったとも言えるわけです。ここで、下の表を見る限りは、商品IDから商品名や単価が決定される、つまり関数従属性があることから、「商品」の表が分離できるということが、正規化の理論でも実証されているので、表を分離して管理するということがデータベースの流儀ということになります。

ここで、商品の価格が変わったとします。例えば、ロボットいか2号が850円になったとします。そこで、商品の中の１レコードの単価フィールドを850に更新しました。更新はこの１箇所だけ行ったとします。その後に、販売明細と商品を結合した下の表を見ると、当然ながら、ロボットいか2号の単価は850円になっていますが、２箇所登場しているいずれも850円になっています。大元が変わったから、それを参照している複数の箇所で結果的に新しい値が得られています。

この「修正」が、「間違っていたから」行ったものであれば、これで、無事に正確なデータを記録できたことになり、それでOKです。納品書を再発行しないと行けないような気はしますが、データベース上の話はこれで終わりかと思います。しかしながら、実は11月10日に、価格改定が行われたとしたらどうでしょうか？つまり、11/9の販売は、800円のまま、11/11の販売は850円にしたかったと言っても商品テーブルを修正した結果、過去の全てのデータに対して更新されてしまった結果になります。

そうならないために、どうすればいいか？ もちろん、ルックアップを使うのですが、まず、なぜルックアップなのかということは傍に置いておき、ルックアップを使った結果を示します。同じようなデータですが、販売明細に「商品名」と「単価」のフィールドを用意します。正規化が崩れるじゃないかと思われるかもしれませんが、その件は、後から（次回）に説明します。この新たに作ったフィールドは、商品テーブルに対応する値を複製した結果を残すために用意します。複製をするというのがポイントになります。販売情報としては、販売日〜個数までの4つのフィールドで事は足りているので、まずは新しい2つのフィールドは空欄とします。そして、テーブル結合しても、やはり空欄のままです。

ここで、ルックアップは一般には次のような動作をします。ここでは、商品との関連を商品IDで照合していますので、外部キーにあたる販売明細の商品IDが更新された場合、結合した先にある「商品」の表の「商品名」「単価」を、販売明細の「商品名」と「単価」に複製するということを行います。外部キーをトリガーにして、リレーションシップがある先のレコードから複製するという動作です。ここでの商品IDは、例えば、ポップアップメニューで選択できるようになっていれば、メニューを選択したときに、101などの値がフィールドに設定されるとともに、システムの背後で商品名と単価のコピーが行われます。これがルックアップの動作原理です。単に「取ってくる」ということではなく、どのフィールドの更新がトリガーになっているか、そしてどのテーブルから取り出し、結果、どのフィールドに値を入れるのかということで、把握すべき事態はたくさんあります。ですが、把握すべきことは全部データベースの設計に絡んでいることであるとも言えます。

なお、こうしたルックアップを複数のレコードでまとめて行う機能もありますが、通常は入力に伴って複製されることになり、その場合は１レコードずつの処理になります。通常、複製先にデータが入力されていれば、つまりNULLか””でない場合は、複製を行わないように設定するのが一般的かと思われますが、この動作はオプションになっているので、設計者が適切に選択しなければなりません。このオプションはどっちがいいのかというよりも、ユーザーインタフェースの動作や、あるいはシステムの要求がどうなっているのかに関わることであり一概には言えませんが、安全策としてはデータがあれば複製しないようにしておき、再度のルックアップ処理をするためには一度フィールドの内容をクリアするというような措置を取るようにするのが安全です。ですが、そのことを警告してルックアップはしなかったことを適切にユーザに示すことなどが必要になり、ユーザインタフェースは込み入ってきます。

さて、ここで、商品のある単価が845円になりました。しかしながら、販売明細の単価は複製した値なので、そのまま800円のままになります。これは、すでに別々のフィールドになっているので連動しないのは当然のことです。

そして、別の日に売上明細にレコードが3行追加されました。ルックアップが稼働する前は、商品名と単価は空です。

商品テーブルと結合行い、その結果から、空欄になっている追加した３つのレコードの「商品名」と「単価」を埋めました。すでに入力されている最初の２行はそのままにします。ルックアップをもちろん、そのように動作させるということです。当然ながら、新たに追加した４行目の販売データでは、単価は更新した845円になります。

ルックアップを適用した結果「販売明細」のテーブルでは、このように商品名と単価の空欄部分が埋まりましたが、販売日によって、ロボットいか2号の単価が違うという状況が「正しく」記録されたことになります。

これが一般的なルックアップの機能の紹介です。リレーションシップの上で正しいかどうかということは議論のネタかもしれませんが、その前に、このルックアップは途中で説明した通り、フィールドの更新によって稼働します。つまり、何らかのトリガーがあって、そのトリガーにより、該当する単一のレコードのフィールドが、別のテーブルから取ってきた値で上書きされるという仕組みです。上記の図では図では２、あるいは３レコードをまとめて更新しましたが、FileMakerでは単一のレコードごとに複製されることが一般的ではないかと思われます。こうした複雑な動作を設定することは結構間違えやすいのですが、見通しを良くする方法としては、データベース設計をきちんと行って、何と何が関連性があるのかということを完全に把握しておくことが挙げられます。

このルックアップが開発ツール等に実装されていれば、手軽に使えるのですが、実装がない場合は自分で実装しないといけなくなります。これは仕方ないことになります。

このルックアップはセットアップの大変さはあるものの、ある意味単純な機能ではあります。そして、意外に業務で発生するさまざまな要求をうまく飲み込んでくれる機能でもあります。なんでもルックアップにすればいいということではない一方で、複雑な仕組みを作らなくてもルックアップで実運用に耐えられるような仕組みが作れるということは、すでにFileMakerの長年の実績で証明されていると言えるでしょう。その辺りの理由は次回に説明します。

一時期、サロゲートキーについての議論が盛り上がったこともありますが、古い本を紐解くと、2006年に初版が出ている「Refactoring Databases: Evolutionary Database Design (Scott W. Ambler and Pramod J. Sadalage)」あたりにそこそこ詳しく書いてあったりもしました。サロゲートキー（surrogate key）とは、人工的に作り出したキーフィールドで、例えば連番の機能を使ってあるフィールドに自動入力するなどして、確実に主キーとなる単独のフィールドをテーブルに用意しておく手法です。最近は、UUIDを文字列型のフィールドに入れるような場合も見られます。連番の場合はバグがや運用ミスがなければ確実にレコードごとに異なる値が振られます。UUIDだと、異なるシステムで生成されたレコードを統合しても、やはり一意な値になっていることが十分に期待できるので、少々のパフォーマンス低下はあるとしても、データベースを分散して運用する場合や、後から統合が必要な複数のデータベースの運用では便利に利用できるでしょう。

しかしながら、サロゲートキーは元々データに存在しない、まさに人工的なキーであって、モデルそのものに存在するのが間違いであるという考え方もできます。人工的に値を追加していいのであれば、なんでもありになってしまい統制が取れません。本来、業務上保存が必要なデータがどんな振る舞いをするのかということを示すのが主キーであり候補キーであるという考え方であり、その主キーの存在を手がかりにしているのが正規化の理論だったりするので、無駄なものを許容しないという見方は分かります。かくして、サロゲートキーは使わないことを「複合キー（Compsite Keys）で運用する」ような呼び方になっているのですが、この場合は複数のキーフィールドであるということではなく、複数のキーフィールドであっても本来のデータにある情報からキーフィールドを選択するべきであるということを言いたい用語です。つまり、英語のCompositeを単に複合と訳すと、複数っぽいニュアンスが垣間見えてしまうというところです。なお、「人工キー（Artificial Keys）」の対比で、「自然キー（Natural Keys）」と呼ぶ場合もあります。

それぞれのメリット、デメリットは順次紹介しますが、概して、複合キーであるべき論は、データモデリングの立場から出ている意見です。これに対して、実装側の意見としては、サロゲートキーあるいは「単独のキーフィールド」というべきかもしれませんが、そういう状態が望ましい場合が多々あるというところでしょうか。どこかで激論があるとしたら、単に立場の違いということだけですが、IT業界の不思議な分業体制がいまだに根付いている感もあるので、やはり「職種による対立」に発展しかねないテーマでもあります。ですが、時代は協調を求めています。相手の立場に立って考えることがなんと言っても必要です。

まず、データモデリングの立場から、サロゲートキーがなくても主キーを決定できる場合は、不要なものがない方がモデルを示す情報としてはより良いという単純な考え方ができます。不要なものは不要だということですね。一方、データモデリングの立場であってもある状況であればサロゲートキーを許すというか、それをモデルとして認識せざるを得ないような場合です。それは、本当にデータに主キーが存在しないような場合です。極端な例では、センサーからの送達データなのか、どこかでエクスポートしたデータなのか、フィールドが {時刻, 場所, 温度} のような場合はどうでしょうか？ 普通、時刻あるいは時刻と場所でキーになりそうな感じではありますが、強引ですが、同一時刻、同一場所で測定されていることもあるような場合はもうお手上げです。その場合は、サロゲートキーを付与するしかありません。インポート時に付与するなどの方法があり、キーとしてはサロゲートキーを使うということをモデルに記述する必要が出てきます。このような事例として、データウェアハウスでのデータ管理でサロゲートキーを使うというのは基本的なテクニックとして古いから認識されています。いずれにしても、こういう「どうしようもない場合」以外はサロゲートキーを使う必要はないという立場です。

一方、サロゲートキーは容認、あるいは必要という立場は、まず、実装上の立場から言えます。システムの内部では、「レコードを特定する」という場面がよく発生します。わかりやすいのは、一覧を表示し、「編集」ボタンをクリックすると、そのレコードの編集画面が出るような場合です。ここで、画面には見えていないかもしれませんが、一覧の各行のに対して「どのレコードか」を特定するデータがどこかに隠されています。そして、ボタンをクリックした時、その特定のためのデータをシステムは次のページに引き渡して、そのページでは、示されたデータを表示します。そして、そのページが編集可能なページであるとしたら、あるフィールドを変更した結果をデータベースに伝えるために、どのレコードなのかを覚えておいてそれを利用します。もちろん、そのどのレコードなのかに使えるデータは主キーです。これが {時刻, 場所} のようなキーを使うとの{計測結果ID}のような単一のサロゲートキーを使っている場合とを比較すると、当然ながら、システム開発は後者の方が容易で確実、つまりソフトウエアの品質保証を確保しやすいと言えるのです。もちろん、「プロの開発者ならそれくらい対処しろや」と言いたいかもしれませんが、テーブルごとにキーが違っていると、その都度微妙に違うプログラムを作ることになりますが、全てのテーブルがサロゲートキーを主キーにしているというルールでデータベースが構築されていれば、どのレコードもプログラム上の扱いは均一化されるので、やはりミスや出戻りが少なくなることが期待できます。さらには、現在はフレームワークを使った開発が一般的になり、フレームワークでは様々な処理が自動化されているのですが、そのような場面ではサロゲートキーの利用が必須だったり、あるいはフレームワークが勝手に作ってしまうような場合もあるかもしれません。仮に複合キーもOKとしても、フレームワークの内側で、文字列データの比較などを適切に行っているのかということも気になる部分であhあって、それなりにデバッグはされているとは思いますが、謎の関連付けが発生しかねないという懸念も持ってしまいます。

すごく荒っぽい言い方をすれば、開発する側はサロゲートキーの方が都合がいいのです。単一のレコードの特定に使う場合もありますが、ロジックを組むような場合で、いくつかのレコードの集合を記録しておいて、ある値を求めて、後からそれらのレコードを全部更新するような場合、つまりは主キーを配列に残して処理を進めます。そのような場合でも、複合キーの場合とサロゲートキーの場合では、当たり前ですが、後者の方が容易であることは言うまでもありません。また、連番で整数になっているフィールドの値だと、気軽に検証ができます。UUIDも文字列ではありますが、長さや正規表現のマッチングで検証は可能です。そういうちょっとした安心を増やすことも、サロゲートキーではやりやすいと言えます。

Refactoring Databasesでは、以上のようなことを「複雑さの回避が可能」と表現しています。そして、この書籍でもう１つ重要なことが書いてあって、「ビジネスドメインの変更を受けにくい」と述べています。要するに、仕様が変わって主キーだったと思っていたものが違いました、あるいはもっとフィールドを増やさないと主キーになりませんと言うことがあったとして、サロゲートキーで運用してれば、「主キーの変更」と言う大ごとは起こらないと言うことです。複合キーによる主キーを変更するとなると、おそらくプログラム側の変更は結構大変ですし、変更しきれずバグがダラダラと残りそうな雰囲気がぷんぷんしてきます。

ところで、このような仕様変更の話が出てくると、必ず言われるのが、「それは最初に仕様をまとめたやつが認識できなかったのが悪い」などといった意見です。後でも議論しますが、確かにデータベース設計は初期段階に決めて変更しないようにするという鉄則のようなものはあるのですが、なぜ仕様変更となるのかということを改めて考えてみる必要があります。もちろん、設計者がボーッとしていて気づいていないことで、後から変更が必要になるというのは困ったものですが、それを避ける方法としてはレビューをきちんと行うということに尽きるかと思います。開発者全員が釈然と対処したとしても、システム発注側が認識していないことについては、どうしても、開発途中にその要求が具体化されるということがあり、それによってデータベース設計の変更が伴うことはよくあります。仕様書に従って作っているだけだと開き直るのは、IT業界内での理想論であって顧客ビジネスをしている限りは完全に開き直るのは悪とされます。ともかく、発注側が全てを把握しているとは限らないのです。この前提は忘れてはいけません。発注側が、業務を全て理解して分析可能な知識があるとは誰も保証していません。なので、ある程度動くシステムができたところで、「あっ！」ってなることは十分あり得る話です。一方で、ビジネス環境の変化も早くなりつつあり、開発している間にビジネス環境の変化はあり得ます。それに追随することを顧客は望んでいます。要するに、設計変更を受け入れる余地がないような開発の段取りでは、現在では受け入れてもらえないのではないでしょうか。もちろん、そのために別の機能の実装は諦めるとか、費用な納期を見直すという交渉を行うなどの、技術的なことを超える対処は必要になりますが、現在ではそういう時にどういう問題があってどうすべきかは知識として蓄積されていると言えるかと思います。仕様変更の必要が生じたとき、前向きに取り組むかどうかの問題です。そのような中、設計変更が生じたとしても、仕様作成の担当者がミスったという見方は自分の責任じゃないのだと言いたいのだと思いますが、なんだかブラックな空気を感じます。

実際、筆者でも、「データ内のフィールドを主キーフィールドにしてやばいことになった」ということを何度も経験しています。製造番号や商品番号が重複しているのは、まだ序の口でした。ある案件で、早いうちに製造番号が、なぜか10年経過すると同じ番号がわり当たると言うルールで運用されていて、それは変えないという決定がなされたので、当然ながら、サロゲートキーを割り当てることになります。その案件では、商品番号も怪しいと思っていたら、やっぱり重複があって、というか、１レコードで管理したいものに商品番号はユニークに振られていなかったので、やはりサロゲートキーを設定しました。このような、よくある設計例であるような「◯◯番号」であっても、実データを読み込むと「あー！重複している」ということは結構ありました。ですが、一番印象に残るのは、「◯◯◯の◯◯番号」です。伏せ字ですみません。これを書くと色々不味そうなので、お許しを。この番号は常識的には重複はないと思われるものです。このシステム、ある程度開発された結果を元に作れというのが至上命令だったので、痕跡を残すためにも途中から手を入れ始めました。そして、この番号がキーになっていたんです。その時の開発スタッフ同士で、かなり悩みました。この番号がキーだけど、ほんとにキーにしていいのか？ いっそのことサロゲートキーに置き換えるか、そうだとしたら、かなり変更しないといけない。などなど議論して、結局、常識的にこの番号はユニークだろうということで、そのまま使いました。この「常識」って疑うべきことの最優先だと思いながらも、トレードオフの理由として光っていたのです。そして、データを統合したら、なんと重複があったんです。しかも、重複回避のための番号の振り替え等はやらないと決まったらしく、これはきつい状況です。サロゲートキーにしていれば、そこで悩むことはなかったのかもしれません。ちなみに、筆者はそれが顕在化した段階では開発スタッフから外れていたので、その後どう対処したのかは聞いていません。ですが、当然最初の段階で、その番号には絶対に重複はないということを発注側から聞いており、常識的にそうなので信じてしまい、データの確認もせず（というか、できなかった）、ということだったので、最初からサロゲートキーにしておくべき案件だったと後から噛み締めるのでした。

一方、サロゲートキーを設定したとしても、候補キーとしてフィールドの組みがあると確実に言えるような場合は、UNIQUEをつけたインデックスを作り、それらのフィールドをキー制約に入れて、少なくとも、「同じデータの組み合わせ」が存在しないようにすることは必要になります。もちろん、必要に応じてNOT NULLによるNULL制約も設定します。サロゲートキーがあるからとそれだけPRIMARY KEY定義するだけで放置せずに、候補キーがあるとしたら、それはデータベースの設計としてケアする必要があることが一般的と考えるべきでしょう。

そのほか、パフォーマンスの議論などもあるのですが、現在のコンピュータ環境と進化したデータベースの実装では、あまり関係ないこともあるかもしれません。昔はパフォーマンス要因として大きかったものも、時代と共に変化します。もちろん、計算量を見積もって考えるということは基本ではありますので、その時代に応じたパフォーマンスの検討は必要にはなります。ちなみに、郵便事業より全国の郵便番号のデータが配布されていて、10万件を超える文字データです。筆者は大昔からこれを使っているのですが、20年前はインデックスなしではそこそこ検索に時間がかかっていましたが、10年前の段階で、インデックスの有無による検索時間の差はもうほぼないくらいになっていました。ですが、もちろん、念のためにインデックスは付けています。検索が重なる場合やパフォーマンスを落とすような検索ワード（と書きつつどんなのかわかりませんが）があったときの対処ということです。

主キーの話をFileMakerと絡めるのはかえって変な方向に行くかもしれませんが、FileMakerはある時期からテーブルのデフォルトフィールドで「主キー」という名前のフィールドを作り、当初は連番を、そして現在はUUIDを設定するようになっています。つまり、サロゲートキーそのものが既定値で作られるのです。FileMakerには、SQLにあるようなPRIMARY KEYに相当する機能はありませんし、複合フィールドのインデックスを作ることもできません。単一フィールドに対する一意性を確保することは可能です。結果的にサロゲートキーが前提としてそこで一意性を確保し、自然キーがあるとしたら、入力時の検証等をうまく働かせるなどの対処が必要になります。そのように、FileMakerでのリレーショナルデータベースの実装はある意味少し変わっているところもあります。ですが、基本、サロゲートキーを既定値にしているあたりで、サロゲートキー自体は否定されるものではないかということを示しているとも言えます。

モデリングをする側としては、「俺の素晴らしいモデルに余計なものを入れるな」と思われるかもしれませんが、実装して、完成に持ち込まないといけない立場からすると、サロゲートキーに結果的にしなければならない場面が出てくることや、複雑さを持ち込まないというあたりで、積極的に使いたいのがサロゲートキーなのです。別に実装チームはモデリングチームの成果を否定したいわけではなく、実装に都合の良いようにして欲しいだけなのです。

ちなみに、現在の複雑なシステムでは、多くは「レイヤーアーキテクチャ」というアーキテクチャパタンを踏襲した構成になっていると思います。一般にはユーザーインタフェースを最上位層、データベースを最下層として、その間を１層あるいは複数層のコントローラなどで構成し、基本的に「上から１層下への利用」にとどめるという実装を進めるのが特徴です。２層を超えることは、複雑さを回避するためやりません。下から上については、一定の制約を設けて実施できるようにしますが、よくある手法はイベント発生により、あるレイヤーのオブジェクトのメソッドがコールされるような枠組みを作っておいて、複雑な要求に応えられるようにします。このレイヤーアーキテクチャは、上から下へのコールが基本なので、高いレイヤーほど一般には変更しやすくなります。低いレイヤーを変更すると、下手をすると、上位レイヤーまで変更しなければならなくなります。そこで、よく変更が行われるユーザインタフェースやそれを受け取る上位コントローラのレイヤーを「上」に配置します。言い換えれば、データベースやそのモデルなどは「なるべく早い段階で完成させて固定化する」という方策とセットになった手法であるとも言えます。つまり、データベース設計は、後からあまり「変わらない」ことを期待しているのです。しかし、それを拡大解釈しているのかどうか、「変えない」と思っている方もいらっしゃるのかもしれません。データベース設計者を神格化しているのか、決定事項が絶対だと思っているのでしょうか？ データベース設計者に特権があるわけではなく、あなたもスタッフの1人です。大体、偉い人、あるいは偉そうな人が担当していることが多いのですが、仕様変更があったらデータベース設計も含めて変更作業に着々と取り組むことが求められています。レイヤーアーキテクチャだとデータベース変更をしてはいけないわけではありません。それよりも、顧客のニーズに応えることが、より高いレベルでのゴールにあるので、変更の必要がある場合には変更すべきなのです。確かに上のレイヤーまで変更が及ぶかもしれませんが、何の整理されていないコード群に比べて。影響の範囲をグラフ（メソッド呼び出しのつながり）として把握できるので、素手で取り組むよりも遥かに効率は高いと言えます。こういったIT業界内の謎のヒエラルキーも崩れがちではありますが、そういう空気感はまだ感じるところでもあります。

正規化の理論は別の記事で詳しく述べてきていますが、意図的にサロゲートキーを使ったサンプルも載せているように、サロゲートキーを使ったからと言って、正規化の理論が崩れることはありません。ただ、主キー以外に候補キーを把握した上でないと議論はできないので、サロゲートキーだけを見ていればいいわけではありません。ですが、サロゲートキー追加しても正規化の理論が崩れないということであれば、それはそれで安心して使える材料でもあると言えるでしょう。

前回はNULL制約、そして一意制約について説明しました。もう1つ重要な制約は、外部キー制約です。サンプルに出てきた販売明細と商品の間では、外部キーフィールドである販売明細の商品IDフィールドには、主キーである商品の商品IDの値を入れることで、販売明細の１つのレコードにおいてどの商品を販売したのかを記録するということができました。この時、外部キーに、主キーにある値以外のものを入力できなくするのが外部キー制約です。例えば、次のようにテーブル定義の時に、FOREIGN KEYで外部キー制約を定義します。CONSTRAINTキーワードを使った書き方もありますが、省略することが多いと思われます。FREIGN KEYでの記述では、まず、商品IDフィールドが外部キー制約をかけると言うことを指定し、REFERENCES以降で、制約をかけたフィールドに入るべき値が用意されているテーブルのフィールドを記述します。ここでは１つのフィールドだけですが、複数のフィールドを指定することもできます。

# DROP TABLE IF EXISTS 販売明細;
CREATE TABLE 販売明細 (
  販売明細ID INT AUTO_INCREMENT,
  納品書ID INT NOT NULL,
  商品ID INT,
  個数 INT NOT NULL DEFAULT 1,
  PRIMARY KEY(販売明細ID),
  FOREIGN KEY(商品ID) REFERENCES 商品(商品ID)
);

# DROP TABLE IF EXISTS 商品;
CREATE TABLE 商品 (
  商品ID INT AUTO_INCREMENT,
  商品名 VARCHAR(50),
  単価 INT,
  PRIMARY KEY(商品ID),
);

INSERT INTO 商品(商品名, 単価) VALUES('A', 500);
INSERT INTO 商品(商品名, 単価) VALUES('B', 600);

最後のINSERTを実行すると、商品IDが、1と2のレコードが作成されます。その状態で、販売明細に新しいレコードを作る時に、商品IDに4などの商品テーブルに存在しない数値を指定すると、エラーとなって挿入処理はキャンセルされます。こうして、「参照先が存在しない」と言う状況を作らないようにガードしてくれる機能がデータベースにあります。

mysql> insert into 販売明細 (納品書ID, 商品ID) values(1,4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test_db`.`販売明細`, CONSTRAINT `販売明細_ibfk_1` FOREIGN KEY (`商品ID`) REFERENCES `商品` (`商品ID`))

ただし、ここでNULLの扱いをきちんと把握しなければなりません。外部キー制約に関してはNULLは対象外となっていて、NULLの入力は可能です。NULLはもちろん、参照先で何かのレコードにマッチするわけはなく、いわば「未参照」の状態を記録する値でもあります。もし、NULLになるのがまずいのであれば、NOT NULLをここでの販売明細の商品IDの定義行に追加しないといけません。NOT NULLするかしないか、これはシステム自体の実装や用途、ワークフローに応じて決められることになるでしょう。納品書の明細行を、商品未選択の状態で用意して、そこからポップアップメニューで選択するような感じのUIだと、NOT NULLでない方が都合が良いでしょう。もっとも、その場合でも、NOT NULLを指定しつつ、「商品未選択」と言う特殊な商品を用意しておくという手法もあり得ます。どちらにしても、データベース以外のところでの決め事があって、それに従って進めると言うことになります。

外部キー制約の定義で、ON DELETE CASCADE、あるいはON UPDATE CASCADEと言う記述を追加する場合もあります。ON DELETE CASCADEがあると、主キーの側のレコードが削除されると、それを外部キーで参照している側のレコードも削除されるという動作です。商品マスターと明細では設定することは多分ないでしょう。商品がなくなると、売上の記録も無くなってしまうからです。一般に、商品マスターは販売しなくなっても残しておくものかと思われます。しかしながら、納品書と販売明細ではどうでしょうか。販売明細の納品書IDは外部キーとして納品書テーブルを参照し、これによって１つの納品書レコードと、複数の販売明細のレコードが関連づけられています。納品書を作ったけど、完全に間違えたなどの場合、納品書そのものを破棄したいかもしれません。その時、関連付けられている販売明細のレコードはどうなるかと言うと、単に納品書のレコードを削除しただけなら、販売明細のレコードは残ります。それでも、納品書IDの値を再利用つまり、後日別の納品書に利用するようなことをしない限りは、単なる参照先のないレコードが残るだけです。適切なユーザインタフェースを作ってあれば、それが目に触れることはありませんが、集計等では無視するような作りが必要になります。と言うことで、納品書を消したときに、販売明細のレコードも消えてくれる方が妥当かつ明快であるとも言えます。

ON UPDATE CASCADEは、主キーの側の値を変更したら、参照している外部キーの値も変更されるという設定です。一般に主キーは変更しないという前提があるのですが、データベースにはこのようなキーがあり、ある意味で、主キーの変更までもサポートしています。もし、この設定がないとして、主キーの値を変更したらどんな問題があるでしょう。例えば、商品ID=2を5に変えたとします。主キーなので、この変更後の値は他のレコードに存在しない値であることは保証されています。しかしながら、このテーブルを参照している外部キーはまだ2のままです。そして、外部キーのあるテーブルの該当するフィールドの値を全て、2から5に変更する必要があります。複数のレコードにわたって変更をする必要があります。また、それを複数のテーブルに渡って行う必要があるかもしれません。しかしながら、ON UPDATE CASCADEにより、それを自動的に行なってくれます。

これなら、主キーは変えてもいいじゃないかと言う話もあるかもしれません。もちろん、システム全体にわたって、ON UPDATE CASCADEがきちんと機能するならいいのですが、懸念されることは、通常システムの実装においては、主キーの値をどこかに一時的に保持することはよくあることです。例えば、Webアプリケーションで、あるレコードの値を変更するようなページがあるとしたら、データベースからデータを読み込み、ページを構築はもちろん行います。そして、画面上のデータを変更したときにサーバーに送り込んで更新をデータベースに反映させないといけません。その時、主キーの値を手がかりに、修正したレコードを特定した上で、更新処理を行います。そうなれば、ページを表示して、修正する前での間に、主キーの値が変化してしまうと、更新処理での元レコードの特定ができない、つまり特定してもその値ではなくなってしまうので、多分存在しないレコードを更新しようとして、エラーになってしまうという問題があります。データベースの都合上、確かにON UPDATE CASCADEは便利なのですが、システム全体のことを考えると原則として、主キーは変更しないというルールは守るのが良いでしょう。そして、主キーを変えるような作業を頻繁に行わないようなワークフローとデータベース設計を行い、どうしても変えないといけないような場合は、誰も利用しない時間帯にメンテナンスとして実施するか、あるいはUI上の処理を禁止するような仕組みを組み込んでおくなど、運用上、あるいは実装上の工夫は必要になります。

ユーザインタフェース側で、あり得ない値を設定するような仕組みを排除し、可能な限り検証をしたデータをサーバに送り込んでいるので、外部キー制約は不要であるといった考え方もあります。もちろん、そのような状況においては、外部キー制約に引っかかることは全くないと言えるかもしれません。それでも、制約は設定すべきという意見の方が合理的です。まず、ユーザインタフェースにバグがあった時、そのバグによって整合性が崩れたデータが入力されてしまうよりも、謎のエラーで（できればそうならないのがいいのですが）止まってしまってデータ入力されない方が、データベースとしては好ましいと言えるからです。また、外部からのデータをCSVファイル等で入力するような場合には、結果的に制約の検証は事前に確実に行われているかどうかは怪しいもので、おかしなデータがやってくるなんて言うのはザラにある話です。その場合、データを検証するプログラムを通してから読み込むと言うこともあるかもしれませんが、外部キーのフィールドについては外部キー制約の制限をデータベースに与えておいて、読み込みながら検証する方がより効率的であるのではないでしょうか。と言うことで、参照制約をサボる理由はあまりなく、設定しておくことでのメリットの方が多いので、早い段階から設定はしておくべきです。

2回に渡って制約の話を行いましたが、これらの制約をうまく利用することで、データベース内のデータが整合が取れた状態をキープできます。もっとも、何を持って整合が取れているのかと言うことは、きちんと把握が必要になります。どうなって欲しいのかということを設計では考慮しますが、どうなって欲しくないのかと言うことにも気を配ると、いろいろな解答が得られます。

この場を利用して、小田嶋隆先生がお亡くなりになられたことに対して、お悔やみ申し上げます。先生との関わりは、新卒で入った日経BPでの原稿の発注先でした。いくつかの会社に発注していたのですが、後楽園のマンションにあるオフィスに緊張しながら初めて伺ったのが懐かしい思い出です。その後、移転、さらにご自宅での執筆になるくらいまでお仕事をお願いしていたのですが、原稿できるまで張り付くと言うことが常になっていて、明け方に奥様の運転で自宅に送っていただいたき、途中で焼き肉を奢ってもらったことも懐かしい思い出です。その後、私は原稿を発注していた別の会社に転職することになり私もライター業を主業務にしていたこともあったのですが、編集者として張り付いていた時に、ライターとしての働きぶりを、かなりリアルに見せていただいたことが自分にとっての大きな糧になっていました。先生ありがとうございます、安らかにお眠りください。（その頃に原稿をお願いしていた人たちが、もう何人も鬼籍に入っているなぁ。）

データベースを稼働させるには、色々な前提があるということはこれまでにも説明してきました。例えば、主キーという存在が必要であるということは、主キーとして認識しているフィールドのセットに対して、それらが重複するレコードが存在しないことが前提となります。これを、「後はよろしく」と言われても、実装が込み入るとか、バグがあったらどうするなど懸念点ばかりが思いつきます。しかしながら、データベースには、前提が必ず満たされているということを保証するための制約の機能があります。この機能を利用することで、前提が崩れるデータはデータベースには存在しないと保証できると思っていただいていいでしょう。データベースにバグはないのと思われるかもしれませんが、今時のデータベースソフトは長年の開発によって、こうした「前提の前提」のような発生しては大いに困ってしまうような箇所のバグはまずないと思ってOKです。

前提を満たさない状況にしようとしたときにエラーを出してデータの書き込みをしないというのが一般的な動作になりますが、システム側はそうしたデータベースのエラーを適切に処理する必要があります。システムが止まってはいけません。そしておそらく書き込もうとしたデータが正しくないわけで、そのことがわかるようなユーザインタフェースを作る必要があることも言うまでもありません。結果的に、そうしたユーザインタフェースをまともに作るとなると、「あらかじめ前提を満たすかどうかをチェックする」と言うことに究極的にはなってしまって、それじゃあ別にデータベースに制約機能なくてもいいじゃないかと思ってしまうかもしれません。このことはむしろ、データベースとUI側の両方に制約をかけることで、より確実にシステムが動作すると言うことで前向きに捉えるべきでしょう。いずれにしても、データベースが制約をかけているから「大丈夫」と放置するのではなく、きちんとシステム側の特にUIにもきちんとした対処は必要であることは認識すべき事柄です。

データベースのフィールドは、NULLと言う特殊な値を取ることができますが、まず、「NULLにはならない」と言う制約が付与できます。CREATE TABLE文のフィールド定義で、NOT NULLと言うキーワードを付与するのが一般的です。NULLの書き込みは意図的なことをしない限りは通常はなく、NOT NULLはフィールドの初期値に関して考えておくことになるのではないでしょうか。一般にはフィールドの初期値がNULLになりますが、この制約をつけても初期値はNULLのままです。つまり、既定値は許されない状態になるので、必ず値を与えるか、フィールドの定義部分で「個数 INT NOT NULL DEFAULT 1,」などとして、DEFAULTに続いて値を指定するか、何らかの対処が必要になります。

そして、主キーフィールド等で利用できる「一意制約」の機能もあります。この機能はインデックスの仕組みを利用するのが一般的です。インデックスは、フィールドのデータがどのレコードにあるのかを特定できる情報をデータベース側で自動的に作ってくれる機能です。その実現方法も興味深いところですが、完全にブラックボックス的に動いてくれるので、要するに「検索結果を早く提供してくれる機能」と割り切って考えれば良いでしょう。例えば、文字列を検索する場合、インデックスがないとしたら、全データを最初から最後までチェックしないといけません。インデックスがあると、その文字列があるレコードは、これとこれと…と特定してくれるので、当然ながら検索は速くなります。現在のコンピュータ環境では、ちょっとしたデータであればあまりインデックスの効果はないと思っている方もいらっしゃると思いますが、長いデータや大量のデータでは十分にインデックスによるスピードアップは期待できます。少量のデータだからといってインデックスがあれば遅くなるようなことはないので、検索に使うフィールドにはインデックスを作っておくのは基本中の基本になります。仮にインデックスを作らなくてもサクサク動いているとしても、インデックスのない検索はデータに比例して時間がかかるようになるため、いつか耐えられないくらいになってしまう可能性を秘めています。問題がないと思っても将来に備えてインデックスを作っておくべきです。

このインデックスは、値の重複を許す場合と、許さない場合があり、インデックス作成時に指定をします。もちろん、前者は検索の高速化が主な用途ですが、後者は主キーに設定するために利用できます。そして、フィールドは、１つだけではなく、フィールドの組み合わせに対してもインデックスは作成できます。例えば、{商品名, 顧客名, 個数}に対して重複を許さないインデックスを作成すると、この3つのフィールドが全部同じレコードは作成できなくなります。商品名が同じものがいくつもあるのは構いません。ちなみに、レコードを追加したりフィールドを変更した時に、インデックスは更新されるのですが、インデックスが正しく更新されないと、追加や変更の処理もキャンセルされてしまいます。インデックスはテーブルのデータと連携をしていて、「制約」として動作すると言うわけです。もちろん、データベースからはエラーがレポートされるので、適切に処理をしなければなりません。

MySQLを例にとって説明しますが、例えば、次のようなテーブル定義があったとします。とりあえず、販売明細IDを主キーとし、連番を入力することにします。記述方法の1つとして、( ) 内の最後にPRIMARY KEYとしてどのフィールドが主キーかを指定しています。これにより、自動的に重複を許さないインデックスが作成され、フィールド「販売明細ID」の値と連携することになります。もちろん、ある数値を与えれば、どのレコードなのかと言うことを瞬時に返すインデックスの意味も持ちますが、販売明細IDに既に入力してある値を新たに挿入しようとしてもエラーを返す返す一意制約のためのインデックとしても機能します。ちなみに、これによりPRIMARYと言う名前のインデックスが作成されます。インデックスは、テーブル名とフィールド名の情報を持ち、テーブルごとに管理されるので、結果的にデータベース内にPRIMARYと言う名前のインデックスがいくつも作られることになるでしょう。

# DROP TABLE IF EXISTS 販売明細;
CREATE TABLE 販売明細 (
  販売明細ID INT AUTO_INCREMENT,
  納品書ID INT NOT NULL,
  商品ID INT,
  個数 INT NOT NULL DEFAULT 1,
  PRIMARY KEY(販売明細ID)
);

MySQLでは主キーを設定すると自動的にインデックスが作られるので、例えば、「CREATE UNIQUE INDEX 販売明細_販売明細ID ON 販売明細 (販売明細ID);」のようなSQL文で新たにインデックスを作成する必要はありません。ちなみに、重複を許さないという特性は、UNIQUEと言うキーワードで指定をします。INDEXの後はインデックス名、そして、ONの後に「テーブル名（フィールド名）」と言う記述を行います。

なお、ここで、モデルの上で、同一の納品書の明細には、1つの商品は1行だけ登場するか、全く登場しないかどのどちらかであるという制約があったとします。そうすると、次のような重複を許さないインデックスを作成しておくことで、主キーの制約をかけることができます。おそらく、納品書のUIで、明細行を追加し（その時には既に納品書IDは確定しているので入力済みになる）、商品を選択したときに既に同じ商品がその納品書の明細に存在していれば、制約違反のエラーが出てきます。それを避けるために、商品の選択肢に既に入力してある商品が出てこないようにするのが1つの方法ですが、そこまでの対処をUIで行うと、UI側とデータベース側の２重対処をしたことになります。そのことが冗長なのか、それとも確実な動作を支える実装なのかは意見が別れるところですが、設計とはそういう場合にどうするのかと言うこことを決める作業に他なりません。そのようなことを早い段階から検討できるようになりましょう。

CREATE UNIQUE INDEX 販売明細_主キー ON 販売明細 (納品書ID, 商品ID);

なお、UNIQUEなインデックスを設定するだけだと、NULLは許可されている状態になります。前述のようなユーザインタフェースだと、商品IDがNULLの期間が発生するので、NULL許可はワークフロー上は必要であるとも言えます。ですが、いずれにしても、NULLにならないようにしたいということであれば、テーブル定義にNOT NULLを指定します。MySQLのUNIQUE INDEXは、例えばこの場合だと、納品書=1、商品ID=NULLというレコードは複数存在できるようになっています。つまり、NULLの値がある場合には、一意性の評価はしないと見ることができます。これは、NULL = NULLはFALSEであると評価できるところから来ており、NULLは「どのレコードも異なる」と判定されてしまいます。つまり、主キーフィールドの１つでもNULLがあれば、見かけは同一のレコードも作成できてしまいます。もちろん、主キーフィールドはNULLにならないと言うのが「原則」という考え方もできるのですが、ワークフロー上NULLである期間を許すとなると、落とし所は難しくなります。もちろん、現実の案件では、色々な要求に対する実現手法や実現度合いを見計らいトレードオフを行なって最終決定するしかないでしょう。

さらに「外部キー制約」のこともあるのですが、ちょっと長くなったので、これは次回に説明しましょう。

ER図やクラス図で、テーブル設計を記述することはよく行われています。その結果をもとに、CREATE TABLE文を駆使して、テーブル定義のためのSQL文を記述し、データベースに読み込ませて使えるようにします。文法的な間違いは随時直すとして、設計の変更などが発生してフィールドを追加するなど、データベースの変更は開発中には付き物です。ということで、モデルとテーブル定義のSQL文が分離した瞬間、両方をメンテナンスするということになり、変更を適用するための記述はSQL側になるので、やはりモデルの修正はおろそかになってしまいます。常に、モデルから生成できればいいのですが、データベース特有の記述を毎回手で治すような事態だと、別々にしているのと手間は変わりません。ただ、最近のWebアプリケーションフレームワークは、モデルをコードで記述することで、自動的にデータベースの生成や、あるいは設計が変わった時のデータの移行なども含めてやってくれるので、その意味ではモデルベースではありますが、ERやクラス図と、モデルのコードの変換や対応付というまた同じ問題に直面します。この辺り、なかなか良い方法はないというか、労力をかけないで、ドキュメントをメンテナンスするということへの工夫をとにかく頑張って凝らさないといけないところではあります。ERやクラス図を最初に作り、その後、SQLやコードなど実装が進むと、ER/クラス図が放置されるのが常かもしれませんが、開発が落ち着いたら、実情と合うようにモデルを修正しておくようにしたいところです。

実際に開発を始めると、テーブルだけでなく、ビューも使います。テーブルのやりとりだけだと、何度もアクセスが必要になります。そこで、SELECT文でいくつかのテーブルを結合した結果が欲しくなるのですが、ソースコードにSELECT文を展開する場合もあるかもしれませんが、ビューを定義して、あたかも「1つのテーブル」のように扱える仕組みは便利です。1つのビューをいくつかの目的に使い回しもできます。ビューを最初から用意するというのが１つの理想的な状況ですが、現実には開発をしながらビューを追加することが多いと思われます。つまり、実際にユーザインタフェースのコードを記述するときに、「このページにはこのフィールドがいるけど…」と検討を始めて、「このテーブルと、あのテーブルと、そちらのテーブルを結合しないといけないな」などを考えて、CREATE VIEW AS SELECT …などと書いていることが多いと思われます。実際にUIのコードと対峙しないと、必要なビューというのが見えてこないというか、気合を入れて考える気がしないのではないでしょうか。そして、フィールドが足りないとビューの定義を書き直して、DROP VIEWしてまたCREATE VIEWをするという手順でメンテナンスを繰り返します。テーブルを変更するときはALTER TABLEコマンドを使わないと、DROP TABLEしたらテーブルが消えてしまいます。ですが、ビューは、一時的にはどこかに結果はあるとしても、設計情報だけが必須のものなので、DROPしてCREATEできる点では気軽に変更できます。

気軽に作成した結果、何が起こるかというと、ありがちなのは似たようなビューをいくつも作っていたり、名前の付け方が統一されていなかったりといったことが起こりがちです。動いていればいいという考え方もありますが、できれば、後からのメンテナンスをやりやすいように構成されていたいところです。ということで、ビューも、テーブル設計に続いて設計内容として記述できればと思ってしまいます。ER図作成ソフトの中にはER図にビューという要素をエンティティの1つとして配置できるものもありますが、結果的に構造がテーブルと同じということで、テーブルの代わりに置けなくもないという感じの機能です。むしろ、ER図ではビューは記述しないのが一般的と言えるかと思われますが、実のところ、この理由を記載した記事などはみたことがありません。一番考えられるのは、ビューの設計は、SQL文でテキストで記述してしまうので、それを確認するのがとにかく確実で早いということがあるからではないかということです。当然、そこにはビューの設計情報が全部あり、テキストなので検索も確実に引っ掛かります。しかしながら、テキストファイルに大量にビューの定義があると、前に説明したように、同じような別のビューを定義してしまうということにもつながります。開発で頭が爆発しそうな時でも、冷静に既存のビューを参照できるようにするために、クラス図にビューも記述するのが良いのではないかと考えて若干、実践をしてみましたが、ただ、やり始めてあまり時間が経過していないので、効果の程はまだ未知数です。前回に出てきたテーブル設計のクラス図が上半分の6つのクラスにあるものと同様です。新たに、ビューとして2つ定義したことを書き加えました。ビューは下の2つです。まず、ビューの名前には、<<view>>というステレオタイプを記述し、通常のテーブルと区別できるようにしました。ついでに色分けもしてあります。

ビューが定義されている状態の何を知りたいのかということですが、まずは、どのテーブルあるいはビューをもとにしているのかという情報かと思います。つまり、FROM句に並ぶテーブルです。販売明細テーブルの内容に、商品の情報をくっつけた「販売情報_商品付」というビューが定義されています。このビューは、販売情報と商品の2つのテーブルをもとに作られていることが分かります。左側のビューとも繋がっていますが、この線は、左側のビューが「販売情報_商品付」をもとに作られているものですので、ビューとビューを結ぶ線はどちらが大元なのかをチェックする必要があります。「販売情報_商品付」は、基本的に「販売明細」にあるレコードが、そのままビューの側にも存在するということを期待しています。つまり販売明細テーブルの拡張版なのです。そこで、黒いダイア付きの線、コンポジションを利用して、「販売情報_商品付」が販売明細と同一のレコード構造、つまり、販売明細の１レコードが、「販売情報_商品付」の1レコードになることを示唆しています。コンポジションの意味から若干外れるのですが、販売明細の１つ１つのレコードをパーツとして、それをそのまま組み立てているというイメージで、当たらずとも遠からずかと思われます。そして、商品からの線は、「販売情報_商品付」ビューが商品とも結合されていることを意味してますが、通常の結合でない場合、ここでは左結合の場合は、<<left-join>>というステレオタイプをつけておきます。ここで、コンポジションに当たる販売管理に対して、商品が左結合するということになります。そして、属性のところには存在するフィールド名を記述します。主キーを明示したい場合は、ステレオタイプの<<PK>>などを記述しても良いでしょう。そして、最後の「金額」フィールドは計算フィールドです。計算フィールドの名前の前に / が付けられていますが、派生と呼ばれる記号で、他のフィールドの値から導出可能であることを示しています。もちろん、この場合はさらに同一レコードの値から導出可能であるので、派生の記号のまさに使い所です。式はどうするか、ここではメモに記述していますが、左側のビューのように、制約として記述する方法もあります。長くなると、メモの方がみやすくなるかもしれません。最も、ここに式をあまりに細々と記述してみづらくなるのであれば、式はいっそのこと省略して、CREATE VIEWを参照するということでも良いでしょう。

「納品書_合計付」ビューは納品書の１レコードが、ビューの１レコードになることを期待しているので、納品書テーブルからの線はコンポジションとなります。そして、顧客名などを取り出したいので、顧客テーブルを左結合します。しかしながら、ここで、明細の合計を取りたいのですが、「販売情報_商品付」ビューから値を取ってきて合計を取ります。まさに、「販売情報_商品付」ビューの結果を集約するので、白いダイアマークのアグリゲーションが適切です。ただ、この時、どのフィールドでグループ化するのかもステレオタイプ<<group-by>>で示しておきます。そして、合計金額の派生フィールドが、SUM関数を使った式を持っているので、そこに関連した明細の「金額」フィールドの合計の値が求められることになります。

以上のビューを実際のSQL文で記述するとこんな感じです。なお、このままのSQL文は通らないかもしれません。アグリゲーションがある場合、アグリゲーションに関係ないフィールドをSELECT句に入れてはいけないというルールで運用されている場合が最近は増えています。

CREATE VIEW 販売情報_商品付 AS
SELECT 販売明細ID, 納品書ID, 商品ID, 商品名, 単価, 個数,
  単価 * 個数 AS 個数
FROM 販売明細
  LEFT JOIN 商品 ON 販売明細.商品ID = 商品.商品ID

CREATE VIEW 納品書_合計付 AS
SELECT 納品書ID, 顧客ID, 顧客名, 販売日,
  SUM(金額) AS 合計金額,
  SUM(金額) * 0.1 AS 消費税額,
  SUM(金額) * 1.1 AS 総計
FROM 納品書
  LEFT JOIN 顧客 ON 納品書.顧客ID = 顧客.顧客ID
  INNER JOIN 販売情報_商品付 ON 納品書.納品書ID = 販売情報_商品付.納品書ID
GROUP-BY 納品書.納品書ID

ビューとビューの間の線が、コンポジションでもアグリゲーションでもない場合は、どちらが元になっているのか分かりにくいので、この例では記述がありませんが、もとになっている側に矢印の矢尻、つまり、導出可能の記号を付けておくのが良いでしょう。最も、矢尻はコンポジションやアグリゲーションの記号の反対側なので、常につけるでもいいような気がします。

図に、テーブル間結合の式は必要かというと、その式は、大まかにはすでにテーブル間結合の図で記述されています。どのテーブルを持ってくるのかということから、関連のための条件式はほぼ決まります。最も、リレーションシップを検索のように使う場合もあるので、そのようなテーブル間の関係性として定義していないリレーションシップでの結合を行う場合は、結合の線に対してメモで条件を記述すると良いでしょう。

実際案件で、ビューも一緒に記述してみました。やはりというか、線だらけになってしまいました。なので、ビューへの結合線は薄い色にするなど、本来のテーブル間の関連付けの線を目立つようにしています。ですが、ほとんど同じビューが２つあって（これは意図的なんですが）、そういった定義でちょっとだけ違うのがどこなのかは一目瞭然になります。ただ、やはりというか、１つ１つのビューの用途については、箱の存在だけではピンとは来ません。そこで、メモでビューの用途や意図を記述をする必要性はあります。最も、これはテーブルについても同様かもしれません。

クラス図にビューを記述することは、前回紹介したAmbler先生のページにもありますが、今回紹介した記述方法はいくつかルールを追加しています。ビューも設計のうちですし、管理したいということもあるかと思いますが、テーブルの設計と併せて記述するのはあまり支持してもらえないかもしれません。みづらくなるだけだという感想を持たれるかもしれません。一方で、離れていた開発から戻った時や、メンテナンス開発の時などは状況の把握には有効でしょう。結局、その案件の中では、テーブルとビューを両方記述しましたが、両方記述した図と、テーブルだけを記述した図の2通りの出力を出すことにしました。