Category: データベース

前回は第二正規化を説明しました。定義を抽象的に言えば難しそうで敬遠しがちかもしれませんが、本質は、関数従属性が1つのテーブルに複数存在しているということにより、何かが問題であるということがあります。今日はその問題とされる「何か」を説明したいのですが、その問題の総称は「更新不整合」あるいは「更新時異状」と呼ばれます。英語ではupdate anomalyですが、日本語の記述は書籍によって異なります。更新、つまり、修正、追加、削除の時に問題があるということになります。

改めて、第二正規化前の表を示すと次のとおりです。この表の名前とともに、どんなフィールドがあるかということを記述する方法として、「販売明細(販売日, 販売先顧客, 商品名, 単価, 個数)」といった表記もされます。一連の記事ではわかりやすく表にしているのですが、より簡潔にはこういう書き方もあります。販売明細は、結果的に、いつ誰に何をどれだけ売ったのかの記録なので、単価を除く {販売日, 販売先顧客, 商品名, 個数} という4つのフィールドが候補キーであり、主キーになるというのが#27の時に説明した内容です。なお、スーパーキーとしては他に、全フィールドを示す{販売日, 販売先顧客, 商品名, 単価, 個数}もあります。

ここで、前の表のレコードに対する主キーは、関数従属の1つの表現であります。この表には、主キーに対するレコードへの関数従属とは少し異なる FD: {商品名} → {単価}、つまり、商品名が決まれば単価は自動的に決まるという関数従属がありました。こちらの関数従属は、主キーによる関数従属とは別の関数従属であり、このデータ持つ性質です。

第二正規化した結果は次のとおりでした。新しくできた「商品」の表は、候補キーとしては{商品名}の1つになり、これを主キーとします。そうすると、元は販売明細に存在した FD: {商品名} → {単価} という関数従属性は無くなっています。当然ながら、単価フィールドを無くしたからです。この関数従属性は「商品」の表に移動しており（あるいは分離した）、それぞれの表は1つの関数従属性を保持するデータとなっています。以下の分割した表から元の表は、「商品名」で照合して、商品の「単価」をくっ付ければOKということは何度も説明しています。このようなことができる特徴を「無損失結合分解」や「情報無損失分解」と呼ばれ、これも数学的に定義されています。ここでは、実体を表で見ながら考えてみて納得感は得られるのではないかと思うので、詳細な数学的な議論は説明しませんが、興味ある方はその辺りもガッツリ勉強しましょう。

正規化前の表で、データの変更を行いたいということが発生したとします。いくつかパターンはあると思いますが、まず、単価が間違っていたとかあるいは発売前に他社製品よりも安くしたいので値下げといけないということがあったとします。例としては「ロボットねずみ3号」を900から850にするといったような事情です。もちろん、正規化前の表の単価のフィールドを変えればいいのですが、おそらくは複数のたくさんのレコードにわたってフィールド修正を行うことになります。これが、正規化後であればどうでしょうか？ いずれの変更処理も、商品の表の1レコードを変更するだけでOKです。

同様な考えで、2行目の販売が、実は「ロボットねずみ3号」ではなく、「ロボットさんま1号」だったとします。この場合、正規化していないと2つのフィールドの変更が発生しますが、正規化後は商品名だけを変えればよく、１つのフィールドの更新だけで済みます。このような性質を「修正不整合」などと呼ばれます。そして、第二正規化により、修正不整合は解消されたと言えます。

ただ、ここでツッコミどころはあると思います。「商品名」が変わったら、正規化後の表においても複数のレコードに渡って変更しなければならなくなります。これは後で説明します。また、もう一つツッコミどころがあって、「販売先顧客名」は変わってもいいのかということもあると思います。これも商品名と関連性がありますが、やはり後で説明をします。

次に正規化前の表に、レコードを追加したいと思います。もちろん、新たに販売明細が発生すると、それは追加できます。ここで、新しい商品「ロボットうさぎ4号」が、990円で発売されたとします。正規化前の表だと、発売日、販売先顧客名、個数が決まるまで、つまりは売れるまでその情報が登場できません。これら、主キーフィールドはnullではいけないというルールがあるからです。つまり、商品名と単価以外のフィールドが空欄のレコードは、登場しないという前提で話をしているのです。ですが、正規化後は、販売明細はそのままで、商品の方に「ロボットうさぎ4号」「990」というレコードを追加することができます。「販売明細」の方には「ロボットうさぎ4号」はまだ登場していないので、元の合成した表に戻した時には、商品にある「ロボットうさぎ4号」は消えてくれます。正規化前にレコード追加の問題があることを、「挿入不整合」などと呼びます。「新しい顧客が発生したら？」というツッコミは、やはり後で説明します。

さらに、正規化前の表からレコードを削除することを考えてみます。たまたま「ロボットさんま1号」は3行目に1つしかありませんが、これを削除したとします。すると、「ロボットさんま1号」が700円であるという情報が、表から欠け落ちます。しかしながら、正規化後の表で、販売明細の3行目を削除しても、商品の方に「ロボットさんま1号」が700円であるという情報が残ります。この問題は、「削除不整合」と呼ばれます。これも、「顧客だって削除したら残っていない」というツッコミがあると思いますが、やはりすぐ後に説明します。

次回から第三正規形以降に移るのですが、こうした正規形を求めるモチベーションは、修正不整合、挿入不整合、削除不整合を合わせた更新不整合の発生を抑えるというところにあります。

ここで、不整合の発生が、単独のフィールドではなく、ここでは商品と単価のように、フィールドの組み合わせに対して発生している点にまず注意をしてください。正規化理論は、あらゆる不整合に対処しようとしているのではありません。フィールドとフィールドの関係がある部分、ここでは理論の上では「リレーション」に対してと言えますし、ここまでの議論では関数従属性を持つフィールドに対してという言い方もできます。よって、ここでの「販売先顧客名」のフィールドに対しては、このフィールドは主キーフィールドの１つですが、このフィールド単独でキーとなるような関数従属はとりあえず見つかっていないので、正規化の理論の枠外と言ってしまっていいでしょう。商品については{商品名} → {単価}という関数従属性が一連のデータにはあるという見方をしているので、そこに対しての更新不整合を問いたいというのが基本にあります。では、単独のフィールドの整合性は関係ないのかというと、もちろん、設計上は大いに関係があります。ですが、理屈の上では、フィールドにあるデータは元々は取り得る値の集合があって、そのどれかの要素がフィールドに来るという考え方がありました。記述しているかどうかはさておいて、少なくとも概念的には元になる集合を考えている「はずである」というこれも前提になります。現実のデータベースにその値を外さないようにする手法はいろいろ組み込まれているので、それを使って不正な値を入らないようにするなどは可能なのです。そこは正規化の理論とは関連は大いにありますが、前提部分の話であったりします。単独の値の保持ではなく、関係性の保持が、更新不整合での注目点となっています。

一方、もう１つ検討すべきこととして「主キーに使われている値は変更しない」という前提もあります。いや、前提かどうかは若干微妙ですが、まず、そう考えるとまさに整合するということがあります。ここでの「販売先顧客名」のフィールドにある「Amazen」などの名前は、そんな前提は無理ですが、ここで間違えることはないという前提がある、つまり、そのフィールドに入るべき集合から取り出すなどするので、間違えるはずはないという前提があるとすればどうでしょう？ 「販売先顧客名」のフィールドの変更は、要するにこの世界にはないと言い切ってしまうわけです。もちろん、『そのフィールドに入るべき集合から取り出す』というあたりで、すでに別のテーブルへの分割が示唆されているものの、更新不整合の問題ではどうやら目を瞑るようです。

ということで、更新不整合はリレーションシップつまり、関数従属の枠内での主キーフィールドでないフィールドの更新に限られるとしたら、修正不整合のところでのツッコミの1つ「商品名の変更はどうよ」ということに答えられていると思います（単に封じているだけと言いたいかもしれません）。また、「顧客」については、フィールドが1つだけなので、関係性は論じられていないので、もう1つのツッコミにも答えていると思います。そして、後の理由は、挿入不整合、削除不整合のツッコミにも答えているでしょう。つまり、削除をおこなって「Amazen」がなくなったとしてAmazenが顧客である情報がなくなったということを問題視するのか、いや、売上無くなったのだし顧客じゃないよとドライにバイバイするのかということになると、後者の考え方を取ります。もし、そのうちAmazenにまた販売があれば、また、「Amazen」と1つのフィールドに1つの書き込み、つまり通常の（不整合とは言えない）編集処理の範囲内で付け加えることができるので、顧客については挿入不整合や削除不整合とは考えないということになります。

現実のデータベース設計ではどうでしょうか？ 「顧客」の名前だけを管理するなんてことは皆無だと思われます。顧客の住所や担当者、電話番号などなど顧客に対する様々な属性をフィールドとして追加しないと、実務は回りません。そもそも、同一名義の会社は存在するので、会社名自体を主キーにするのは実用上はあり得ません。同一名や、同一会社で異なる販売先などといろんなバリエーションがあり、結果的に「顧客番号」みたいなIDとなる値を振ることになります。ということで、顧客も商品も、たくさんの主キーでないフィールドを持つことになるので、そこで顧客に関わる関数従属が発生して、「顧客」が独立した表になるかと思われます。

フィールド１つのリレーションというのは全くないわけではないものの、どちらかといえば滅多に出ません。これは１つの話題としては面白いので、覚えていたらまた書いてみたいと思います。ただ、ここで、無理やり、顧客マスターが登場する理屈を考えるとしたら、自分自身への関数従属があるでしょう。つまり、FD: {販売先顧客名} → {販売先顧客名} という関数従属です。当然、1対1に対応するので関数従属性はあると言えばあるのですが、これを言い出すと、実は全てのフィールドについて言えてしまうのですが、ともかく販売先顧客名だけを考えて、自分自身に関数従属しているとしたら、顧客(販売先顧客名, 販売先顧客名) という表を作ることができます。しかし、2つのフィールドの内容は同一なので、表としては顧客(販売先顧客名)であれば十分である、すなわち表として持ち得る情報は等価であると言えるので、これにより顧客マスターの分離は可能です。明細の最初の2行を削除しても、顧客に「トイザラシ」が存在することは残り、挿入不整合、削除不整合は防げそうです。ただし、この場合だと、販売先顧客名は主キーフィールドなので、修正不整合の問題は残ります。この考えを一般的に適用すると、「個数」には「個数マスター」に登録しないと明細に登録できないというようなことになります。もっとも「個数」の場合は、整数というプリミティブな型なので、他に日付も加えてそれを別の表で一旦定義して使うというのは、結果的にかえって処理手間が増えるだけになるので、もちろん現実的ではありません。数値や付随情報があるということは一般には考えにくいです。日付は隠れた関係性を持つことがあって要注意なのですが、日付もやはり単独の値です。いずれにいしても、このように、一般化しすぎると明白におかしな方向に行くので、元々関数従属性の定義では、R(X, Y, Z)において、XによってYが決定するということで、XとYが最初から分離しているということを示唆しているあたりで、自身への関数従属については除外されているとも考えられます。ただし、Y⊆Xの場合は「自明な関数従属性」と呼ばれ、Y = Xの場合も含まれるので、関数従属という考え方は可能です。つまり、選択的に自分自身への関数従属性を考えれば、現状での「販売明細」でも「顧客」テーブルの分離はできるとも言えるかもしれません。そうなると、今度は「選択基準」が欲しくなりますが、現状では、要求があるから、あるいは問題が出る可能性があるから、ということでしか言えないでしょう。以前にフィールドの値はラベルか測定値だと結論づけていますが、ラベルには付帯する属性はあることが多いのですが、測定値はその値が単独で意味を持つとも言えます。であれば、「ラベルを値に持つフィールドは自身への関数従属とみなして良い」みたいなルールを与えれば、第二正規形の段階で、顧客は別の表に分離できそうですが、もちろん、直感で言っていて、それが体系の中で問題ないかは全然検証していませんのでご理解ください。

理論には必ず前提があります。データベースの理論は一度数学の世界に入っているところで完全な理論体系が確立されているのですが、その考え方を現実のシステムに適用するあたりで、前提は忘れがちになります。しかしながら、その前提が重要であることは言うまでもありません。

このシリーズの記事は、最初はわかりやすい話が多いのですが、だんだん難しくなってきています。Facebookのいいねが次第に減り、Twitterのリツイートする顔ぶれが変わってきていますが、頑張って続けたいと思います。

さて、前回のキーフィールドの話をしましたが、キーフィールドはnullを取らない前提があるという話を書いておくべきでした。nullは何と比較してもfalseになるので、照合できないから、キーフィールドの値に使えないのです。nullを許すと、照合出来ない、すなわち検索が出来ないレコードが発生するので、キーフィールドによってレコードを特定するという前提が崩れるのです。ちなみに、MySQLだと、論理式の値にNULLがあれば、結果は必ずNULLになります。「SELECT NULL=NULL;」は、NULLを返し、結果的にfalseとみなします。PHPだと通常はnullかどうかを確認するのはis_null関数を使いますが、null == nullの結果は1つまりtrueになります。SQLと言語ではnullの考え方に違いがあります。

前回に関数従属性という考え方を紹介しましたが、ここで、さらに厳密な「完全関数従属」という考え方をまず説明します。関数従属は、あるフィールドの値が、別のフィールドの値により決まるという考え方でした。この時、元になるフィールドの集合の真部分集合をキーフィールドとして関数従属性が成り立っていないような場合を、完全関数従属していると表現します。ざっくり言えば、フィールド同士の関連の中に、異質な関連が混ざっているような感じでしょうか？ そして、第二正規形は、第一正規形が成り立つと同時に、キーフィールドに含まれていないフィールドが、それぞれの候補キーに対して完全関数従属している場合のことです。

ややこしいですね。具体例で細かく見ていきましょう。以下は前回の登場した販売明細の表です。赤いボックスは主キーフィールドを意味しています。

これだけのフィールドしかない場合は、候補キー、主キーとも、{販売日, 販売先顧客名, 商品名, 個数} の4つのフィールドを含むものしかありません。よってこの1つの集合に関して完全関数従属しているかどうかを調べるのですが、ここで、FD: {商品名} → {単価} という関数従属があるとします。つまり、商品が決まると、単価が確定すると考えることにします。この辺り、前提条件次第で結果が変わりますので、前提条件をきちんと把握してください。

新たに認識した関数従属のキーフィールドである{商品名}は、候補キーとなっている{販売日, 販売先顧客名, 商品名, 個数}の真部分集合です。完全従属性の定義を検討すると、『元になるフィールドの集合の真部分集合をキーフィールドとして関数従属性が成り立って』います。完全関数従属はそれが否定されないといけないので、完全関数従属していません。また、候補キーに含まれていない{単価}が決定されるという関数従属なので、第二正規形の定義が崩れます。よって、この表は第二正規形を満たしていません。

まず、第二正規形を満たすにはどうすれば良いかということを考えますが、結論は表を分離します。キーフィールドの真部分集合の関数従属が表の中に登場しないように表を分離します。つまり、単価を切り離せば良いということになります。ここで、表は分離して、後から結合できるという事実があり、この辺りは数学で記述するのはかなり難しい話になりますが、このシリーズのだいぶん前に実例で感覚的に説明をしているので、これは既知の事実だとします。つまり、以下のようになります。新たに出てきた商品の表の候補キー、主キーはいずれも{商品名}であるとします。

販売明細は、候補キー、主キーとも、{販売日, 販売先顧客名, 商品名, 個数} の4つのフィールドの集合になります。ただし、そのどの真部分集合を考えても、関数従属性は存在しません。よって、単価を分離し、商品名で照合して元のデータが得られるという状況にすることで完全関数従属性が成り立ち、第二正規形を満たすことになります。

要するにマスターを分離することか？と言われればそうです。ただ、この段階で、全てのマスターが分離できるわけではありません。さらに別の正規化のルールも必要になります。

前回、同じような表で以下のようなものも提示しました。こちらは「販売明細ID」フィールドがあります。ここで、この表の前提を少し拡張します。前回のキーフィールドの議論では、販売日〜個数のフィールドには重複するレコードがないという前提を引き継ぎましたが、それをやめてみましょう。良し悪しの問題はちょっと目を瞑って、番号を振ったのだから、区別できるでしょうという大雑把な設計方針が導入されたと思ってください。{販売明細ID}を候補キー、主キーとして、{販売日, 販売先顧客名, 商品名, 個数}は、もはや候補キーではないと前提を変えてみます。

単一のフィールドが候補キーであり主キーであるような状況は意外にシンプルです。ここで、関数従属性はどうなるかというと、例えば、販売明細IDが1003だと、販売先はAmazen、商品名はロボットさんま1号など、残りのフィールド全て、1つのフィールド販売明細IDが決まれば決まっているということになります。つまり、関数従属としては、FD: {販売明細ID} → {販売日}、FD: {販売明細ID} → {販売先顧客名}、FD: {販売明細ID} → {商品名}、FD: {販売明細ID} → {単価}、FD: {販売明細ID} → {個数}、といった関係があるとみなされます。これも厳密に記述すると、FD: {販売明細ID} → {販売日, 販売先顧客名, 商品名, 単価, 個数} ということになり、矢印の右側の集合の部分集合を持つ関数従属性が大量に存在するということになります。つまり、販売明細ID単独で、レコードの特定が可能となっているということです。

であれば、候補キーの真部分集合はありますか？ 単一の集合の真部分集合は空集合を除いてあり得ません。空集合は実はnullなので、これはキーとしては排除するのが定義でした。ということで、真部分集合の関数従属は存在しません。もう一度、完全関数従属の定義に立ち返ると『元になるフィールドの集合の真部分集合をキーフィールドとして関数従属性が成り立っていない』であり、まさにその通りなので、実はこの販売明細IDを主キーにした表は、すでに第二正規形が成り立っています。なお、商品が決まれば単価が決まるという事実は存在していて、FD: {商品名} → {単価} という関数従属性はあるとしても、こちらの表では{商品名}という集合は、候補キーの集合{販売明細ID}の新部分集合ではないので、完全従属性は保持されています。不思議ですが、ここで、{販売日, 販売先顧客名, 商品名, 個数}は主キーではなく、それらが重複するようなレコードの存在を肯定したという状況の変化があったので、実は表としての性質が変わってしまっているということです。

ふと思うのは、Excelの表って、最初から行番号が振られているので、第一正規形をクリアすれば、行番号を主キーと見做せば自動的に第二正規形も成り立つってことになりますね。

あれ？じゃあ、マスターって分離できないのか？と思われるかと思いますが、後半に示した表から商品マスターが取り出されるのは、実は第三正規形を満たすためのテーブル分離で実現されます。次回は、なぜ正規化が必要なのかという議論に必要な更新時異常という考え方を紹介し、さらにその後に第三正規形の説明をしようと思います。

前回は、データはラベルか測定値だろうという話をしましたが、これはあるセミナーでここで説明しているような設計の話をしたとき、受講された方の一番の疑問だったらしく、細かく説明できなかったところなのです。こうしてブログに書く機会ができて個人的にはなんとなく一段落です。今回から、データの集まりである表についての話です。フィールドには名前が付いているので、その名前を手がかりにデータを取り出せますが、レコードにはそうした識別のための材料が、あるのかないのか？そこがポイントです。言い換えれば、レコードを特定する、あるいは一定の条件のレコードに絞り込むなどのいわゆる検索ができる状態の表でなければなりません。今回はとりあえず、架空の会社の例で出てきたこの表からスタートしましょう。営業部門が販売した明細を記録しているという想定です。

もちろん、いろんな意味で省略は多いのですが、この表では5つのフィールドがあってそれでこの会社の販売データが記録されているとします。今、たまたま、５つのフィールドが全て同一のレコードは存在しませんが、さらに3行目と同じレコードが6行目に追加されるという状況はあるでしょうか？ あるいかないかは、結果的にその会社の状況によると思います。つまり、前提条件次第で変わります。ロボットさんま1号が非常によく売れて、Amazonは11月11日に何回も注文したとしましょう。個数は毎回違うかもしれませんが、同じかもしれません。個数まで同じなら、行の区別がつかなくなります。ちなみに、そういう状況にしますか？ しないでしょう。それを手入力しているとしたら、完全に同じレコードが発生する状況で、「さっきの注文入れたっけ？ まだだっけ？」という確認ができません。もちろん、その日の発注を頭からチェックするということもあるかもしれませんが、かえって効率を落とします。現実にはどうしましょう？ 多分、販売日と同じかあるいは「入力日時」などのタイムスタンプのフィールドを増やすなど、同じ内容のレコードが発生しないような工夫をすると思います。ID番号を登場させるのは反則かもしれませんが、例えば、納品書IDがあれば、おそらくは区別できるでしょう。ただ、これも、同一の納品書で同じ商品に対する明細は２つ以上存在する事はない、つまり、単一の納品書では、明細には１つの商品は１回登場するか、全く登場しないかのいずれかになるという前提もあります。

まず、こうした表では完全に重複したレコードがないことを前提にします。その結果、表はレコードの集合であると考えることができるわけです。集合は重複した要素を持たないというのは定義です。ただ、センサー記録などでは、タイムスタンプを落としたとしたらそういうこともあるかもしれませんが、これは、センサー入力が重複しないことを前提としているからです。つまり、データ収集が重複がないということがほぼ保証できるような自動化プログラムで行われているような場合には、後から集計した結果が重要であり、元データに重複はあるかもしれませんが、現実は正しく記録されているとも言えます。今回はそのような状況は特殊事例として除外してください。もっとも、そのような場合でもタイムスタンプなどを記録することで完全な重複がないようにするのはおそらく一般的であると思います。

ここで「関数従属性」という考え方を導入します。あるフィールドXの値が決まると、別のフィールドYの値が決まるという考え方です。Wikipediaに倣って、「FD: X → Y 」と記述しましょう。X、Yはいずれも属性の集合です。前の販売明細の表で言えば、商品名に対する単価は、商品名が決まれば単価がどうやら確定するようなので、「FD1: {商品名} → {単価}」とみなします。あるフィールドの値が別のフィールドの値で決まってしまうという特徴が、表全体に渡って成り立つかどうかを検討します。では、販売先顧客名と単価はどうでしょう？ たまたまある顧客が同一の商品しか注文しないと表の中では関数従属性があるように見えるかもしれませんが、顧客は複数の商品から選択するという前提があれば、単価は顧客では決まらず商品によってのみ決まるので、一般には関数従属性はないとみなすのが自然です。「関数従属性」と言うと名前が難しそうで、かつ数学的に記述するとさらに難しそうですが、「現実はどうなっている」ということが前提にある概念です。関数だから、その内部の計算方法は？などと疑問に持つかもしれませんが、ここでの関数は「入力を与えれば値が返る」という意味の抽象的な関数で、一般にはプログラムや数式では書けなくはないものの完全には書きにくいものです。データの集合がそういう性質を持つという議論です。

この関数従属性を手がかりにして、「レコードを一意に特定できるフィールドの集合」を考えます。この「レコードを」というところがまずポイントで、この性質を見極めておかないと、実用上支障が出そうです。そして、「一意に」というのも重要です。何か特定のデータに特定できるということです。販売明細は5つのフィールドがあるので、つまりはそれら全てのフィールドを決定可能なフィールドの集合Xとして、「FD2: X → {販売日, 販売先顧客, 商品名, 単価, 個数}」を満たすXを認識する必要があります。この概念を一般に「キー」と呼びます。例えば、Xには矢印の先そのもの、つまり全部のフィールドは多分含まれるでしょう。つまり、Xの1つは「A1 = {販売日, 販売先顧客, 商品名, 単価, 個数}」になります。

販売日がこれ、販売先顧客はこれ、商品名はこれ、単価はこれ、個数はこれ、と列挙すれば、その値を持つレコードを探してきて１つのレコードが特定できると言うことです。販売日がなかったらどうでしょう。同一顧客に同一商品を同一個数、別の日に販売した場合、どっちのレコードなのかが区別がつかなくなり、キーの役割を果たしません。ここですでにFD1が見つかっているとすれば、単価は商品名から求められるため、キーの構成要素としてなくてもいいということになります。つまり、単価を知らなくても、どのレコードかは特定可能なので、「FD2: {販売日, 販売先顧客, 商品名, 個数} → {販売日, 販売先顧客, 商品名, 単価, 個数}」となり、「A2 = {販売日, 販売先顧客, 商品名, 個数}」もFD2のXとして成り立ちます。

このように、キーは複数のフィールドの組み合わせも取り得ます。考えられるありとあらゆるキーを集めたものは「スーパーキー」と呼ばれます。ここまでの議論だと、{A1, A2}がスーパーキーです。その中で、余分なものがないもの、つまりなるべく要素数を少なくとしたものが「候補キー」と呼ばれます。つまり、候補キーとしては「{A2}」となります。さらに、候補キーの中で、実際にシステムの中でレコードの特定に使うものが「主キー」と呼ばれます。ここではA2しかないので、A2を主キーとすることになりますが、現実にはフィールドが増えた時などにもう少し候補キーを探すことになるでしょう。4つのフィールドで常に検索するというのはちょっと面倒ですね。

ここで、IDフィールドを販売明細に加えてみます。現実にやっていることの説明が必要ですね。「販売明細ID」フィールドを追加して、一意な数値をつけてみました。

ここで新たに、A3 = {販売明細ID}が、スーパーキー、候補キーの仲間に入りました。スーパーキーを全部挙げるのは実は大変です。A4 = {販売明細ID, 販売日}、A5 = {販売明細ID, 販売先顧客名} … などと、結果的には販売明細IDとの他のフィールドとのあらゆる組み合わせが、レコードの特定が可能です。全部列挙大会をやってもいいのですが、疲れるのでやめます。販売明細ID単体でレコードの特定が可能なので、冗長性を無視して「可能である」と言う点だけに注目すれば、そうなります。候補キーは冗長なものが全部排除されるとしたら、{A2, A3}となるでしょう。そして主キーはより単純なA3になると言うのが一般的な考え方の1つになります。それがいいかどうかは、結果的にどんなシステムを作るかと言うことと擦り合わせは必要ですが、キーの定義はこのようになります。スーパーキーに関して、一度は手で全部書くのが学習になるかもしれませんが、実用的にはスーパーキーを全部列挙するような仕様書は誰も読みません。場合によっては候補キーを挙げて、２つのうちどちらを選んだかと言う理由を記述する場合もあるかもしれませんが、設計者は候補キーが頭の中でサッと浮かび、サクッと主キーをどうするかという議論に入ります。今回の場合A3のように決まったことしか仕様書に書いてないとしても、A2のような実データフィールドの主キーはあるものの、番号振ってあって一意だからこっちでいいよね的なフィーリングは行間にしか書かれてないことが普通です。

キーの中でも３種類もあってややこしいと思われるかもしれませんが、設計上は主キーを特定できればそれでOKです。しかしながら、なぜ、主キーが存在する意味があるのかと言うことを定義するために、関数従属性という考え方、あらゆるキーから意味のあるキーの選択といったことを経て、主キーの考え方に至ります。スーパーキーのように一度「全て」を考えて絞る、ここでは冗長性のないもの、そして実用性を考えるということに分解して仕組みが成り立っているということは、結構重要なのではないでしょうか。

前回は、データベースの基本的な考え方として、もとになる集合があって、そのどれかの要素が１つだけフィールドになるという「ドメイン」の考え方を紹介しました。そのフィールドが集まって表を形成しますが、それを「リレーション」と呼ぶというのが定義です。表そのものや、フィールドという構造がある、レコードという繰り返しがあるという諸々を含めた意味が込められている用語になります。ただ、現場的には「関係性」のこともリレーションと言ってしまうことがあり、ちょっと混乱します。本来は「リレーションシップ」なのでしょうけど、リレーションと言ってしまって通じるところもあって、自分でも時々混乱している時があり、反省する次第です。

さて、フィールドに入れるべきデータの集合そのものがどういう成り立ちをしているのかをさらに探りましょう。都道府県のように、ほぼ誰が見ても明確なドメインはありますが、実際の業務で出てくるデータには、元の集合が書ききれないもの、定義が揺らぐもの、同じものなのに同一名称など、かなり多種多様なものがあって捉えどころがないという考えになるかもしれません。あるいは、「数字」と「文字列」が一般的な言い方でしょう？ という言い方もできるのですが、そうすると、なんでもありということになり、ドメインの考え方が揺らぎます。

実際にデータベースに入っているデータを仔細に見ると、結局のところ、「つけた名前」であるものと「測定値」なのではないかということを考えました。「つけた名前」はここでは「ラベル」と呼びます。英語のlabelに「名前を付ける」という意味があるからですが、「ラベル」というより発音に近いのは「レーベル」でしょうけど、日本語として通用している「ラベル」で以下は表現します。

例えば、「埼玉県」というのは、３文字の文字列ですが、この文字列そのものが、都道府県単位の１つの行政区域を示しており（法律的にはもっと細かくあるのでしょうけど）、ある意味、広い土地全体に対して名前をつけたものです。そして、日本国内は、都道府県で地域を区別できるとか、ある一地点はどれか１つの都道府県であるのかということが通常は結論づけられるなど、素なデータであることが誰が見ても意見の相違がないようなラベルなのです。47都道府県はいずれも文字列という見方もできるのですが、47種類のラベルがあるというのがさらに抽象的な見方になります。そのラベルに書いてある名前があるドメインでは「埼玉県」であるとなっているものの、ドメインが微妙に異なる、つまり状況が変われば「Saitama Pref.」となっているのかもしれません。

このように、データはラベルではないかと考えると、現実にデータベースに保存しているデータのほとんどはラベルです。文字列で記述すると、人間が普段のコミュニケーションで利用している言語に登場する形態と同一と見做せる記述が可能なので、文字列で記述することが多いでしょう。しかも、厳密にはなんらかの定義がきちんとされた上で、ラベルの集合があって、その中の１要素がフィールドに収められています。名前も、姓や名に記述される文字列は、いずれもラベルです。そう考えると、ほぼ全てのデータは、誰かがそれに対してつけた名前や、あるいは番号であって、それらのデータの成立過程を考えれば、フィールドの元になる集合というものの存在が明らかになります。商品の表にある商品名は、やはりラベルです。会社で扱っているさまざまな商品には、通常は製造した会社が付けた名前がありますが、基本的にはそれは他の商品と別の商品であって区別ができるように付けた名前、すなわちラベルなのです。「SK-110B」のような実利、すなわち商品名から商品の属性や成り立ちを示唆するような情報を込めたような実利を追求したような商品名もあれば、「ラッキースタンプ」のようなマーケティングの効果を高める意図が込められた商品名もあるでしょうけど、いずれも、ラベルであることには変わりありません。そうしたラベルを記録するのに文字列という表現形態は非常に便利であり、ほぼあらゆる形式のラベルに対応できるので、データベースとして文字列記録機能が実装されているということになります。ですが、フィールドには文字列を記録するのではなく、設計の上では、ラベルが記録されるという考え方が重要なのです。

ただ、データベースの中身には、ラベルでないものも存在します。例えば、体重や温度といった「測定値」です。測定値とは、なんらかの単位をもとにして測定した値です。これはラベルとは言い難いものです。もっとも、群論の考え方からすると、整数や有理数などは要素が無限の集合の要素ということにもなるので、結局はラベルではないかと考えられるのですが、データの成り立ちが違うと考えています。ラベルと測定値は、照合に意味があるかどうかの違いがあると考えられます。ラベルはなんらかの方法で、同一と見做せるものは同一です。つまり、ラベルとしての文字列が同一であれば、それらデータが示す現実のものは、そのラベルの定義範囲（ドメインの定義と同義）において一定であるということになります。ある会社に売った商品Aと、別の会社に売った商品Aは、同一の商品名なので、商品という区分においては同一です。ここでの同一は、以前に記載した「ある会社の営業部における商品の考え方」に基づく判定になりなす。箱が異なり、製造番号が違うなどの物理的な違いはあるかもしれませんが、「商品」というドメインでは同一と見做すということが営業部においては行っていることです。これが照合の意味するところです。ところが、測定値は同一の測定値は、それは偶然そうなっているかもしれません。正しく定義されたドメインではラベルは同一であることを保証できます。いや、むしろ、それがドメインの集合で検討した場合の定義そのものでもあります。「正しい定義」に「ラベルは重複がない」などのルールを入れれば良いのですが、これは第二、第三正規形に繋がりそうなルールです。一方、2つの測定値は等しい値であることはあっても、それらは同一であるかどうかはわからないのです。極端かもしれませんが、測定値に対象と時間軸がぶら下がるとしたら、唯一無二の存在のように思えてくるのではないでしょうか。

ラベルと言えば文字列ということになりそうですが、一方、数値であってもラベル的な存在も扱うことがあります。ある出荷伝票があって、出荷済かどうかを0と1のどちらかで示す場合があります。良し悪しはともかくそういう実装は時々行われています。このとき0が未出荷で、1が出荷済みということはどこかにドキュメント化しないと、他の人には理解できないのですが、これもドメインの定義に当然ながら含まれていると考えます（実際、ドキュメント化されてないことがほとんどだったりしますけどね）。この場合、「0」と「1」は、測定値ではなくラベルです。便宜的に数値を割り振っています。ですが、これを「133」と「59887」の2つの整数を利用しても、ドメインとしては成立します。プログラムなどの記述が気持ち悪くはなりますが、割り当てるという意味ではなんでもいいとも言えます。ちなみに、0と1にしておくことで、プログラミング言語の論理型との変換がスムーズであることを利用して、コードの単純化をするのは基本テクニックですので、0/1の数値置き換えはエンジニアサイドでは当たり前のことでもあります。数値、特に整数は、同一かどうかの判定が非常に安定しています。文字列だと、全角と半角の問題など、ドメイン内では同一と見做せる文字列をコード体系の問題で異なる文字列で示すことができるので、もちろん正しく配慮すれば問題は発生しないのですが、そうした配慮が必要という点では安定性が整数に比べて減少するという考え方ができます。途中に出てきたIDの値を番号にすることが一般的ですが（最近はUUIDも使われますが）、その理由の１つはこうした照合判定の安定性があります。ちなみに、もう１つの大きな理由としては、照合のスピードが文字列よりも一般には早いということがあります。

いずれにしても、ラベルとなる数値は、一般には計算できません。１が出荷済みということで、1+1=2に何か意味があるかというと、２回出荷した？ それはちょっと違いますね。出荷はするかしないかのどちらかです。「出荷済み＋出荷済み」という演算はこのドメインでは定義されていません。よって、1+1という計算はできるのですが、その計算自体は意味がないのです。こうした数値を「名義尺度」と呼ばれます。

なお、数値には名義尺度以外に、「順序尺度」「間隔尺度」「比例尺度」といった区分があります。「順序尺度」はその大小が順序を決定することが可能なもので、例としては、アンケートでの５段階評価つまり、大変悪い・悪い・どちらでもない・良い・大変良い、に対して、１〜５の整数を割り振ったものです。ちなみに、こういうアンケートの数値の平均を出すのを平気でやる方も結構いらっしゃるようですが、基本は、それぞれの選択肢のヒストグラムを比較検討するのが基本です。どちらでもないと良いの差は1で、良いと大変良いの差も1ですが、本当にこれが等距離かどうかというのは通常は分かりません。なぜなら人間の感覚だからで、厳密で測ったものではないことが一般的で、個人差もあるからです。ただ、平均値同士の違いをざっくりと見るという点では若干意味があるのかもしれませんが、いずれにしても順序尺度であるということは忘れてはいけません。一方「間隔尺度」は数値の差が定量的に意味のある場合です。さらに数値としての0に意味がるような場合は「比例尺度」となります。名義尺度と順序尺度が「ラベル」で、間隔尺度と比例尺度は「測定値」であると言えそうですが、これについては反論が出そうな気もします。

ちなみに、商品IDなどのID番号は基本的には名義尺度です。ただ、システム自体が連番を振るような場合、順序尺度として利用可能ではありますが、これは副次的な作用であって、考え方は「区別する」ということが基本にあるので名義尺度になります。もちろん、ID番号は加算や減産はできたとしても結果に意味はないので、間隔尺度や比例尺度ではありません。ただ、順序尺度として使うには、番号の割り振り自体の管理が必要です。また、順序を変更することがIDの変更ということになり、これは別の回で説明する予定のキーフィールドは変更しない原則にも外れることになります。なので、通常は順序尺度とは考えません。順序をなんらかの方法で記録したい場合は、独立したフィルールドに数値を入れて管理をします。通常はUIとの連携にロジックが絡むことが多いでしょう。

いずれにしても、データベースにあるデータは、ラベルか測定値であり、業務システムではラベルの割合がかなり多くなります。そのような中でも確実に測定値なのは、単価、個数、金額といったあたりのフィールドです。いずれも0の基準は明確なので比例尺度と言えます。そして、金額が同じであれば、同一の販売であるかと考えるかというと、たまたま金額が同じだっただけかもしれないので、同一かどうかは分かりません。これが、測定値を照合には通常は使わないという実例です。

日付や時刻は実はラベルなのか、測定値なのはか難しいですが、差の演算が可能なことを考えれば間隔尺度なので、測定値であると言えます。しかし、年月日時分秒といったコンポーネントの集合のような考え方だと、ラベルのようにも見えます。例えば、年月だけを記録するような場合は、ラベル的な動きをするようなデータであることもあります。このように日付時刻は、単に記録することを超えるとかなり扱いが難しくなります。これも回を改めてですね。

データベースの中身に圧倒的に多い文字列は、ラベルです。つまり、誰かがつけた名前を使わせてもらっていて、それらが共通概念になっているということで、情報が伝達されているということです。ただ、文字列を記録しているだけではなく、ラベルを文字列として記録しているということに思いを巡らせれば、データベースの設計に必要な情報も浮かび上がるのではないかと思われます。

今回も図がないので、何か貼っておきます（笑）

前回は多対多の概念の学習ということで、元々IDを割り振った、つまりは第二、第三正規形を満たしているような状況で説明をしました。残るは第一正規形を満たすようにするということを考えれば、「中間テーブル」を持つことがあたかも自然に発生するように説明しましたが、ちょっとレールに乗り過ぎたようです。改めて、前回のような、学生の管理、履修科目の管理という視点から、「表にする」ことによって分解を進めるということがどういうふうに進むのかを考えてみましょう。

まず、「学生」という表があるとします。学校としては、入学した学生の一覧は必ず作るでしょうから、学生一覧がExcelで作られていると想定しましょう。以下の図では、氏名だけがフィールドとして存在していますが、実際には読み仮名や学科、入学年度、住所など多数のフィールドがあると思われます。今回はそれを書きませんが、氏名以外にもフィールドがあるという前提で考えてください。そして、学生課では、入学した2人の学生が（そんなに少ないわけではないのですが）、科目履修をすることになりました。申込用紙を作るなどして希望を集めた結果、「履修科目」フィールドに履修科目をカンマ区切りで描きました。これで、誰が、どの科目を、履修しているのか、という点では記録できたわけです。使いやすいかどうかは別にしてともかく記録はできていると言えるでしょう。一方、教務課では若干興味が違います。履修科目はおそらく入学前から決まっていてこの場合は3科目あり、「履修科目」表に記載しています。この表も同様に名前以外に年度、教室、担当教員などいろいろありそうですが、それがともかくぶら下がっていると考えてください。そして、学生課から教務課へ、学生が書いた履修希望の記入用紙が「そちらも必要でしょう」ということで回ってきました。こちらは科目が興味の中心なので、「履修者」というフィールドを設けて、履修する学生をカンマで区切って記載しました。ここで、２つの部署で、「履修登録用紙」を元にしたデータをそれぞれ作ってしまっています。ということで、まずは表を増やさないで、つまりは気持ちの上での「データの一元化」を果たすため、フィールドを追加してやや強引にデータを追加したとしましょう。現実の学校では、学生数や科目数を考えれば、即座に破綻しそうですが、ここでは架空の状況としてそのように考えましょう。

ここで、教務課ではふと思いつきました。「担当の先生に履修者名簿を渡さないと行けない」ので、さてどうしましょう。履修者フィールドの句点を改行に変えるとか、それをRPAでやればいいではないか！DX〜!!!とか言い出しそうですが、もう少し伝統的なExcelライクな方法もあります。以下のように、履修科目ごとにシートを作って、そこに履修者名簿を作るのです。これだと、最近までやっていましたという学校もあるかもしれません。

ここで、中央に３つある履修科目ごとの履修登録の表は、どうみても、同じフィールド構成になります。つまり、縦に繋げられるじゃないかと。だけど、それじゃあ解析学の履修登録名簿はどうやって得られるのだと突っ込まれます。ここで、ありがちな鋭い登場人物が出てきて、「それは検索すればいいのです」ということで、以下のような表にまとめることができるということに気づきます。これは、実は単独の履修科目で発生していることを、どの履修科目でも発生しているということに気づき、前の図を得るには、履修登録表で、指定した科目名のレコードだけを残せば良い、つまりは検索すれば前の表の情報が得られるように、表の構成を工夫したということです。３つある表を単に合体しただけでは元の３つの表は得られませんが、その時、各レコードに、どの表からそのレコードを持ってきたのかを記録すればいいわけです。そのことを単純に実現するためには、科目名フィールドを追加するということで、それを検索の手がかりにすれば、元の表が得られるということになります。これを素で思いつくのは本当に優秀な人なんでしょうけど、少なくとも普通の私たちは、事例を通じてこうした抽象化の１つの流れを学習し、学生と科目以外の他の関係にも適用できるようになっておきましょう。そして、その知見を元にすれば、その時々の状況に応じてデータベースの設計ができるようになります。

再掲になりますが、前回に出てきた以下の図は、上記の図を、学生、履修科目に対して、ID番号に置き換えて、それぞれIDで参照するという前提で書き直したものです。IDがあること以外は、表現している内容は同一のはずです。

改めて、学生課、教務課に立ち戻って考えてみたいのですが、このまま学年が進み、最初の2人の学生が卒業する時になって、「あ！成績表を作らないといけない」となったとします（普通はそんなことはない！）。その場合、今日の2つ目の表のように、「成績表-学生A」「成績表-学生B」というシートを作ればいいじゃないかということになりますが、3つ目の図のように、学生に関わらず構成が同じということを発見すれば、結果的に「成績表」という1つにまとめられます。そして、その「成績表」は細かいフィールド構成を考えなければ、つまり、学生と科目を結びつける存在というより抽象度の高い存在意義を考えれば、実は「履修登録」と同じになります。よって、履修登録に成績を記述することは合理的と言えるでしょう。

履修名簿も、成績表も、どちらも同じ表から得られます。それらは何を基準にして検索するかの違いしかありません。もちろん、実際にレイアウトを作る時にはいろいろ違いがあるでしょうが、データの在り方、すなわちデータベース設計においては、どちらも同じここでの「履修登録」表からの得られるということで、この表はデータベース内において存在すべき表である、そして必要十分な表と言えるのです。厳密には、要求を満たしているかを常に検討はします。元はと言えば、学生課と教務課で同じデータを作ったため、どちらから考えても、論理的には同じ「履修登録」表ができているとも言えます。

前回は、今回の最初の図のような状況において、学生の表にある履修科目フィールドを見ると、あっさり第一正規形を満たしていないからテーブル分けましょうと言いましたが、ここで第一正規形について、改めて詳しく説明しましょう。ただ、本来は集合論の話から入らないといけないのですが、そこはうまく飛ばして説明を試みます。

以下の図の左上は、最初の図にも出てきていましたが、これをもう少し拡張高く（笑）書くと、要素に集合があるという左下の書き方や、表の要素が表であるという右側のような書き方になります。いずれも履修科目フィールドには、合成あるいは繰り返したデータがあるという見方をします。第一正規形はこうした合成や繰り返しが存在しない状態になっているということです。ちなみに、集合と表は、データベースの世界ではほぼ同じと考えてよく、表を抽象的に捉えると集合であるということに他なりません。この時、何を持って合成や繰り返しとみなすのか？つまり、何回も出てきている「素なデータ」、つまりこれ以上分割できないデータの解釈が問題になります。「素」は意味的には数学の素数のことになります。これは次回からのテーマにしたいのですが、ここでは定義として「単一の科目」が素なデータであるとみなしているとします。集合も表も、素なデータではなく、少なくとも「複数の素なデータ」です。もう少し詳細に言えば、「素ではないデータがどこかにあるかもしれない、あるいは素でないデータが存在する可能性がある」ということで、たまたま現状の表で素なデータばかりだとしても今後素ではなくなる可能性があるのなら、そこは第一正規形を満たしていないポイントとして見る必要があります。

これらの表を第一正規形を満たすように変換した結果は、事実上、今回の3つ目の図にある「履修登録」表になります。図では、科目名が先にありますが、学生フィールドが先にある方が考えやすいかもしれません。ここで、上の図だと、同一の「履修科目」が複数登場しますが、学生は１回しか登場しません。これに対して、「履修登録」表は、学生も履修科目も複数登場します。同じデータがあちこちに登場してしまって、効率が悪く変更も大変ということになるのですが、それを解決するのが第二、第三正規形の考え方です。要するに、フィールド内で繰り返すのをやめよう、レコードとして繰り返しているものはなんとかなります、というのがリレーショナルデータベースの動作の基本ということなのです。

ちなみに軽く宣伝ですが、表の中に表があるというと、そうですね、INTER-MediatorではそういうUIを構築できます。さらに自慢をしておくと、FileMakerはポータルの中にポータルは配置できませんが、INTER-Mediatorは、表の中の表を何階層にも定義できます。実際には3つ程度でやめておくのがパフォーマンス的には有利ですが、機能は持っています。それじゃあINTER-Mediatorは第一正規形を満たしていないのかというと、それとこれとは意味が違います。INTER-Mediatorは第一正規形を満たしているデータベースから、表の中に表があるようなUIを構築できるのです。データの整合性は設計の上で取れているものから、人間が見てわかりやすい形式に表示できるUIの構築機能があるということで、第一正規形を崩しているわけではありません。データベースのレイヤーで、第一正規形を満たしておき、保存されているデータに整合性が確保されているということが重要なポイントになります。

ちなみに、第一正規形は繰り返しデータだけでなく、合成データにも当てはまります。合成データの代表と言えば、住所ですね。住所として１つに記録するか、都道府県や市区町村を分離するのかなど、議論の的になるのですが、実はこの話は第一正規形に至る部分でデータベースの理論の世界では整理されています。

ということで、次回からは、データベース設計における「１つの塊」をどのように考えるのかということのシリーズに入りましょう。

１対多、１対１と話が進んできました。関係としては、他には多対多があります。ちなみに、多対１もあるという話もあるのですが、ほとんどの場合、双方向で検討が可能なので、１対多と多対１は、単に説明の時に適切に参照するためのものになります。例えば、「納品書と販売明細は１対多の関係にある」と「販売明細と納品書は多対１の関係にある」と説明していることは基本的には同一と考えられるからです。

多対多をこれまでの実例の中で探そうとしても、実は存在していないので、全く異なる事例を出します。この多対多が発生する代表的な例題は、学校の中のデータ管理です。学生1人1人を１レコードとする「学生」表と、履修科目1つ1つを１レコードとする「履修科目」表があったとします。それぞれ、主キーフィールドの〜IDをあらかじめ設けてあります。

これがどう多対多なのかというと、オブジェクト図と表へのデータ追加をしてみて検討することができます。例えば、学生Aは、解析学、線形代数、集合論を履修するということで、学生から見た履修科目は１対多の関係にあります。一方、線形代数学を履修する学生は学生Aと学生Bで、これもやはり１対多の関係にあります。双方とも、ボックスから複数の線が出ているオブジェクトが１つ以上は存在します。実際の大学のデータだと、このボックスが大量に発生して手で描きにくいですが、2人3科目くらいならまあ大丈夫かなと。このように、どちらから見ても多の関係が発生する可能性がある場合、多対多であるとみなします。ここで、それをどうデータ化するという話が実はちょっと込み入るのですが、以下の図の下半分の表のように表現したとしましょう。すでにIDが振られているので、それを利用しています。学生、履修科目、いずれも対応する表との複数の関連性があるので、複数のID値を覚えておかないといけませんが、表の上で見やすい便宜上の記録として、ID値をカンマで区切るということをおこなっています。

学生Aの履修科目は、101 → {601, 602,603} となっています。つまり、右の表の３つのレコードいずれも参照しているということになります。集合論の履修学生は、603 → {101} であり、一人だけが履修していることも表現されています。ちょっと見づらいとは言え、ともかく、学生と履修科目の関連はなんとなくうまく表現できていると思えます。ただし、問題はあって、ある学生がある科目を履修する場合、２つの表の〜IDをそれぞれ矛盾なく更新しないといけないなど、データの処理は面倒になります。これも、CRUDをそれぞれ考えれば、ちょっと大変そうですが、ともかくロジックが加わります。

ただ、この表を見ていて、何が問題かというと、データベース設計を少しでもかじった方は明白なように、〜IDのフィールドに複数のデータがあるため、第一正規形を満たしていないということです。第一正規形は、フィールドの値が１つの素な値、言い換えれば表の中に表があるような状態を認めないという考え方です。「素な値」というのはちょっと唐突で、これもいずれ説明したいとは思っていますが、ここでは表の中に、また表があるという状況になっているとも言えます。この内在する表をうまく外部に出すというのが第一正規形の１つの変換方法になります。一般には、その結果、表のレコードが増えることになるのですが、そこから表の分割は第二、第三正規形の適用になります。ちょっとここでは詭弁っぽいかもしれませんが、すでにIDを振っているあたり、第三正規形まで満たしている状態でもあるので、その状態を有効に活用したいと思います。

改めて学生の方を見ると、学生IDと履修科目IDの１つのデータは、101 → {601, 602,603} となっています。これは、101 → 601、101 → 602、101 → 603 といった3つの関係に分離できます。この関係だけを「履修登録」表にまとめてみると次のようになります。学生の「履修科目ID」からこの表は作ることができますが、一方、履修科目の「学生ID」からも実質的に同一の表を作ることができます。つまり、双方にIDのカンマ区切りリストを持っているということは、データが重複していたのです。重複していたので、更新時には両方を変更しないといけなかったということもここでわかります。また、同一の表を２つ持っている必要は全くありません。なので、一方の表を残すと、結果的に１つ表が増えると同時に、元からあった２つの表から双方を参照する〜IDフィールドを削除することができます。

学生の履修科目IDや、履修科目の学生IDを消しても問題はありません。この3つの表をあらためてよく見てください。学生Aは、学生IDが101です。履修登録で、学生IDが101のレコードは3つあります。その3つのレコードの履修科目IDを順番に見ると、601、602、603です。つまり、履修登録レコードに３つのレコードがある学生は、３科目を履修しているということが、データとして表現されています。そして、どの科目かということも、履修科目IDの値から求めることができます。これもやはり表と表との結合で求めることができるので、「まとめた表」を生成することが可能です。さらに、履修登録にレコードが存在することが「履修登録されている」という事実を表しています。学生Bが集合論を履修していないことは、{201, 603}というレコードが存在しないことから決定づけられます。

ここで、この3つの表の関係をあらためて見てみると、学生からみた履修登録は、１対多になります。逆に履修登録の１レコードは、１つの学生だけを記録するので、１対１です。１対多と１対１なので、学生と履修登録の関係は、１対多とするという話は２回前に説明した通りです。同様に、履修登録と履修科目も同様に多対１の関係になります。ER図的に表すとこのようになります。

このように、「関係を構築する」ための表を用いる必要が出る場合も、設計をしていれば登場します。１対多や１対１に比べて複雑になるのですが、この関係における表の抽出が設計段階にできていないと、実装が大変複雑になるか、あるいは破綻するかのどちらかです。ただ、要求だけを検討してもなかなかわかりにくいです。その場合は、ともかく表にしてみる、あるいはオブジェクト図を書いて実データとしてうまく記録できているのかなどを確認します。

ちなみに、こうした関係を「作文できる」という見方もできます。つまり、ここでは「学生は、履修科目を、履修登録する」という感じです。日本語だと最後に述語が来ますが、英語だとA student resisters a subject. つまり、主語、述語、目的語の順番になって、ちょうどER図の並びの通りになったりします。そして、両側が名詞、中央の関係を記述した表に対する表現は動詞で可能です。作文が意味があれば、おそらくはこの設計は何か正しいものを表現していることになります。ただ、作文による考え方はあまり包括的ではありません。納品書と販売明細のような場合はどうなるでしょう。「納品書は、販売明細を、持つ」とかになって、動詞というか、関係性を表現する単語がなんとでも意味を考えられるようなものになってしまうかもしれません。それでも関係が明白なら、いいのですが、動詞部分が「管理する」とか「記録する」のような、表にする限りは当たり前だろう的な動詞を割り当ててもあまり意味はないのかもしれません。なので、作文可能性は参考程度のものです。ちなみに、たくさんの表が関連しているような場合、直接関連していないけど、線を辿ると関連しているような２つの表についても、多くの場合作文は可能だったりします。

ここで、学生の科目履修だから、やはり得点の記録は必要だとなりました。さて、どこに記録しますか？ 科目の得点だから「履修科目」と思った方はアウトです。もちろん、解析学が80点などと記録されますが、得点は学生ごとに異なります。履修科目に記録するには、また、最初のような第一正規形を満たさないフィールドを作るしかなくなります。同様な理由で「学生」の表に追加するのもだめです。もう結論は見えていますが、もう少し得点というデータの性質を考えてみましょう。得点は、学生ごと、科目ごとに割り当てられます。となると、ここまでの検討した結果で言えば、「学生ごと、科目ごと」に履修登録がされるという状況が作られているので、履修登録の表に得点があるというのが１つのアイデアです。つまり、表で書くとこうなります。

得点という情報は、履修が前提であるという考え方とも一致します。つまり、履修登録と得点は１対１であるともみることが出来て、つまりはフィールドでいいというのは前回に説明した通りです。

この多対多の関係は２つの１対多の関係にするという手法は「中間テーブル」などと呼ばれて、SQLでの設計手法では必ず登場するテクニックです。ですが、この中間にあるテーブルは単に２つの表の関連付けだけを行うだけでなく、ここに示した「得点」のように何らかの実データを持たせる必要が出る場合もあります。したがって、単なる中間にある存在ではなく、意味があって存在しているのです。この意味をうまく汲み取って表の名前を付ける必要があります。また、いろんな開発手法がありますが、この中間テーブル手法は、名前や適用方法、適用範囲を変えて、設計手法には必ず存在していると言っていいでしょう。

ということで、まずは多対多の中間テーブルによる展開を説明しましたが、何だかスムーズすぎませんか？ 途中にも言いましたが、最初からID番号が振られているのは、ちょっと恣意的ではあります。ですが、まずは理解するためにそういう状況から初めてみました。中間テーブルというテクニックがあるものの、実は、第一から第三までの正規形を適用するということで、自動的に中間テーブルが登場するというのが本来の説明になるのでしょうけど、次回はそういう意味での説明をあらためて試みます。

昨日は１対多について説明をしました。１対多の関係を見つけることが非常に重要で、リレーショナルデータベースはそうした複数の表に分割してデータを記録することで、多彩なデータ構造を実現しているのがポイントです。では、１対１の関係というのはどういうものでしょうか？

ここで、次のように、「商品」と、「商品在庫数」というエンティティを考えてみますが、ここで１つの商品について、１つの数値で在庫数が管理されているとします。倉庫や拠点が複数あるような会社ではこれも１対多になりそうですが、ここではこの関係が１対１であるとします。そうすると、２つ目の図にあるように、ある商品に対して、その在庫数が１本の線で結ばれます。線は必ず１本であって、商品あるいは商品在庫数の側から複数の線が引かれることはないと考えます。１対多は、「多になる可能性があれば、関係は多とみなす」と説明しましたが、１対１の場合は「常に１つのものと関係する」という意味で使われます。１つ目の図はER図とも言えますが、２つ目の図はレコードを１つのボックスとして模して考えたオブジェクト図的なものです。この時、やはり具体的に表で考えれば、それぞれに商品IDフィールドを備えた２つの表にすることが考えられます。同一の商品IDを持つレコードが対応関係にあります。

表を作るには、まず、商品があって、商品にそれぞれ一意な商品IDの番号を振ります。そして、商品在庫数は、すでに存在する商品IDの値を商品IDに入れて、在庫数をさらに別のフィールドで管理します。商品の側の商品IDに重複がないのは当然ですが、商品在庫数の側でも商品IDは複数存在することは問題が発生します。仮に、101に対するレコードが商品在庫数に複数あれば、「どちらが正しいのか？」という問題が発生するので、なんらかの方法で、商品IDの重複がないようにしなければなりません。システム上ではもちろんバグがないように作ると同時に、重複があればエラーになるような仕組みを使うのかもしれません。

ちなみに、商品IDを重複させていいのかということも気になるかもしません。一方の表の商品IDフィールドがないものとして考えるとどうでしょう？そちらの表の各行のレコードは、その値がどの商品のものなのか特定できなくなります。なので、商品IDが必要になるのです。

しかし、この２つの表をみていて思うことは、次のような表にまとめてしまえるのではないかということです。ちょうど、商品IDが同じような並びをしているので、２つの表を左右に並べてくっつけたような感じになります。前の２つの表でも管理はできそうですが、次のような表でもデータは問題なく管理できそうです。つまり、機能的にはほぼ同一と言えませんでしょうか？

多くの場合は１対１の関係は、同一の表にまとめてしまえることが一般的であったりします。つまり、商品と在庫数の関係は、１対１ですが、この場合、商品という抽象概念、つまり表の１行として構成されている存在に対して、在庫数はその商品の属性になります。ここでは、「商品」「商品ID」「商品名」「在庫数」が、商品に関して登場する概念ですが、「商品」以外はみんな「値を持つ」ことがまず挙げられます。「商品」はこれら値を持つものの集合のように思えないでしょうか？つまり、IDや名前、在庫数をひっくるめて商品なのです。そして、これら、「商品」「商品ID」「商品名」「在庫数」については、２つの要素の組み合わせ全てについて１対１の関係になっています。であれば、値のある「商品ID」「商品名」「在庫数」がフィールドになり、それらを総称的に表現している「商品」が表の名前になるという考え方ができるのです。

そうなると、設計の上で、１対１の関係は、全部同一の表、つまりER図的には１つのボックスにまとめてしまうのかというと、必ずしもそうではありません。設計の段階に応じていろいろな考え方が適用されます。例えば、要求段階で作成されるようなビジネスモデルのクラス図などでは、商品に対して在庫の管理が必要であることを明示するために、あえて別のボックスで示す場合もあります。おそらく、在庫が何に対しての在庫なのかが明白でないような場合、「近々、関係性を正しく定義して、どこかに収める」ということを意図したメモのような感じで１対１の存在を記載する場合もあります。実際、それは属性なのか、それとも表なのかが明白でないような場合も時々発生します。その場合、とりあえず１対１の関係が図に出てきます。ただ、完全に仕様が把握した上でのデータベース設計では１対１は不要とも考えられますが、システムの構成やあるいはロジックの構成によっては１対１の表、つまり、１つの表を分割することもあり得ます。例えば、それぞれが異なるサーバで発生するデータである場合は、別々の表で管理する方が、後から統合する手間がおそらく減るのではないでしょうか？ こうした実装上の工夫という点では１対１の表の存在も無視できないでしょう。また、対応する１対１のテーブルの一方の存在そのものが、何か処理をしたフラグ的な使い方もあるかもしれません。ワークフローが進んで行く時に、順次フィールドを更新するよりも、それぞれ別々の表で管理して、１対１のレコードが増えていくような作り方をする方が整理される感じがする場合もあると思われます。

なお、フィールドが多数になると１対１の表に分割というのはあるかもしれませんが、分割基準が雑だったりするとかえってどっちにあるのかわからんということになります。あまり多数だから減らすためというのはモチベーションとしは弱いと思われます。ちなみに、フィールドが数万個にもなるというのは、設計そのものをまずは疑います。通常、横に展開、つまりフィールドの定義が必要と思っても、あまりに多くなるのなら、それは縦に展開、つまりレコードを増やして記録する方法を考え、その手法で設計を考えることになるでしょう。ただ、縦に展開するのが必ずしも良いとは限らず、最終的には要求との擦り合わせが必要ですが、第一正規形を満たすという意味でも、多数のフィールドは「同質のものの繰り返し」があるのかもしれません。ここで何が言いたいかというと、病院の検査結果のデータベースを例に出すとわかりやすいのですが、これはまた別の機会にしましょう。

ということで、残るは多対多ですが、これは次回に説明します。

今回から数回に分けて、改めて関係の概念を整理しましょう。理解すべきことは、１対多、１対１、そして多対多がどんな状況なのかということです。すでに何度も説明している通り、リレーショナルデータベースは表形式のデータを多数扱うことが得意であり、複雑なデータは複数の表にうまく分割することで扱うことができるのです。そこで、システム化しようとしている世界の中で、特に１対多の関係にある表を見つけ出すことが非常に重要になります。ただ、そのためには、１レコードが何なのかということを決定しなければなりません。１レコードの基準が決まれば、それらが１対多なのかどうかということが決まりますが、実際の設計では、基準が揺らぎ、その都度検討は必要になります。つまり、決めたことが間違っていることもあるわけで、行きつ戻りつ設計は進むことになります。

今までのところで、いくつも１対多が出てきていますので、その概念を再掲しましょう。１つの商品が複数の販売機会において販売されるという事実を考えれば、商品と販売明細が、１対多の関係になります。それをER図的に示すとこう書きます。設計図自体は必要なことだけを記述してシンプルに記述する方が何かと便利なのですが、設計者はもちろん開発に関わる人たちはこれを逆に読み解けるようになっておかないといけません。線を引いてあるもの同士は原則として直接関係があり、この場合は、線で引かれることにより販売においては商品が指定されることを意味しているとも言えるでしょう。線の一方が烏の足跡のようになっていますが、それが「多」の側を示しています。「１」の側は単なる線です。この書き方は改めてまた説明しましょう。

ここで、それぞれの表で行が追加される状態、つまり、レコードが追加されていく様子を図にしてみました。前の図はER図なので、箱はエンティティ、つまり表の存在そのものを記述します。一方、以下の図はオブジェクト図的なもので、箱は１レコードと考えてください。ここでダイアグラムの解釈を切り替える必要がありますが、設計時にはそういう頭の切り替えは頻発します。これは慣れるしかなく、言い換えれば、それぞれの図の前提を把握した上で読解しないといけないということでもあります。このダイアグラムでのレコードとレコードの関連は、原則「１対１」を意味する単なる直線のみになりますが、１つのレコードから複数の線が引かれる点が「１対多」の意味になるのです。以下、レコードが何もないところからレコードが順次増えるということを想定して、表の中にあるレコードの関連を図にしています。

全ての商品は、何度も販売されるので、最後の商品Aのように、１つの商品が複数の販売明細に登場します。この事実が１対多の根拠です。商品Bを見れば、１つの商品に対して１つの販売明細2にだけ関係があるので、この商品だけを見れば１対１です。ですが、商品全体にわたって、１対多の関係を持つものがあれば、この関係は１対多と呼びます。いわば、１対多の可能性があれば、１対多であると認識するのです。実際どうなっているのかということよりも可能性で考えます。極端に言えば、レコードが全く存在しなくても１対多であると表現します。

実際にこうしたレコード間の関連をどうすれば構成できるのかについては、すでに説明していますが、この場合では以下のようになります。まず、１側の商品の表に、特定の商品を参照可能な「商品ID」のフィールドを確保します。商品の特定が可能になるには、商品の表の商品IDに重複があってはいけません。また、レコードとして同じ商品が複数の行に登場してはいけません。このような商品IDフィールドを主キーフィールドと呼びますが、キーフィールドの概念については改めて説明します。数値にするのは検索が安定しているからです。文字列だと全角だの半角だのということがあって間違いやすいから、一般には数値を使います。一方、多の側の販売明細でも、ここでは同じ名前の商品IDフィールドを用意しました。そして、商品名などを記載するのではなく、商品IDの値を書き込みます。ここでは商品Aは２つの販売で登場するので、商品IDフィールドには同じ値の101が２つのレコードに登場しますが、これがまさに「多」の意味でもあります。こうした主キーフィールドを参照するフィールドのことを外部キーフィールドとなどと呼びます。

ここでは商品の表が、主キー以外は商品名しか存在しないので、販売明細側の商品IDの代わりに商品名に置き換えれば、利用者が参照したい表の形式に近付くとも言えますが、正しくは以前に説明したように、販売明細の列が増えるのが表の結合の処理です。つまり、商品IDによって参照される商品の表の１レコードが、販売明細の右側に追加されるのです。この方法により、たくさんの販売明細から特定の商品が常に参照されることになります。こうした結合した表は、原則として一時的に作られて、例えば、明細一覧として画面に出ますが、通常はそれはその時に見せて終わりで、また次に同じ画面を呼び出すと、現状のデータに応じて結合した表を作り直し、画面に表示します。

ということで、１対多についてはほとんど復習のような感じですね。次回は１対１、そして引き続いて多対多についての説明を行いましょう。

販売とは別に出荷管理をしているという前提では、データの保持だけはなく、出荷残数を求める方法も確立しておかないといけないということで、前回は銀行口座方式を紹介しました。もちろん、それはそれでうまく行くでしょうけど、データ編集にロジックが絡む点が、複雑さを発生させることも紹介しました。今回は、この応用で、一定期間ごとに残数のキャッシュを作る方法を紹介しましょう。前回の手法の応用なのですが、いくつか利点が増えます。

早速スキーマと、表を示します。スキーマの基本的な形は前回と同じです。顧客と商品に紐づいた受注出荷集計という新たな表が加わりました。初期状態はレコードなしです。ここでは１つの期を１ヶ月とします。１ヶ月に納品書２枚ということはないでしょうけど、考えやすくするために小規模にデータ発生を考えます。２枚の納品書、３回の出荷予定があった場合、その気の受注数、出荷数、そしてここでは出荷残数もフィールドに求めています。

この状態で次の月になって、新たに納品書と出荷が作られ、月末になって集計するとします。とりあえず、受注出荷集計には、月末にレコードを追加する、つまり、期ごと、商品ごと、顧客ごと、という3つの分類軸で受注数、出荷数、出荷残を求めることにします。

こうなると、例えば、12月の途中では、11月までの集計結果を受注出荷集計から取り出し、12月1日以降の納品書、出荷予定の明細分の増減を行うことで、ある時点での出荷残が得られます。当初は出荷残を求めるために、明細をデータの蓄積を始めた最初の段階からチェックすることになってしまいましたが、期を決めて、期末以降をチェックすれば良いという方法であれば、データが増えてもその月のデータを舐めるだけで済むのので、年々線形的にパフォーマンスが悪くなることもまずはないと思われます。ただ、SQLのビュー等で残数を得られるようにすることになるでしょうけど、ビューの定義はちょっと込み入りそうです。

そして、銀行口座の残高のように、明細が発生するたびに残高を更新する方法だと、競合の問題や、ロジックを実施しないとデータが正しくなくなるなどの問題がありましたが、この方法だと、販売明細や出荷明細を作る上での追加のロジックはなく、いずれもレコードを作るだけで処理対象に加わります。もちろん、帳票を作成途中の場合はどうするというワークフローに関わる問題はありますが、編集に関わるロジックはかなりシンプルです。お客さんが勝手にレイアウトを作っても、きちんと出荷残の計算には明細が絡んでくるでしょう。

受注出荷集計のレコード追加は、期末あるいは期首に、バッチ処理を動かすことになります。もちろん、バッチ処理が正しく動くこと、そして、バッチ処理が失敗した時の対処など、バッチ処理の開発は大変ですが、ともかく、利用者が簡単にさわれないところにロジックがあるのはある意味、開発側からすると管理しやすいとも言えます。

受注出荷集計が多数のレコードになって重くないかという心配もあるかもしれませんが、過去に渡ってデータを舐めることはないので、あまり心配はいりません。どうしても心配なら、最新の期のデータ以外は削除するという方針でもいいでしょう。現状の出荷残を求めるということだけのためなら、過去の期のデータは不要になります。

棚卸し調整は、バッチ処理を動かした直後に、フィールド値を手作業で直すというのが１つの方法です。棚卸しについては、前回と手法的には同じになります。なお、期は一定である方がいいかもしれませんが、仕組み上は受注出荷集計のレコードは、気が向いた時に作ってもいいくらいのものです。要するに、明細の全部を舐めることを避けるための手法ということになります。

前回の「受注出荷数」は、出荷残のフィールドがなく、計算で求めることにしました。しかしながら、今回の「受注出荷集計」には出荷残フィールドがあります。これも、それぞれのロジックを作るときに「おそらくこう考えるだろうな」という意図が込められています。前回の銀行口座方式の場合、直接に出荷残のフィールドを増減する方法も考えられるのですが、競合する確率が上がることなどがあるので、増と減をそれぞれ別々のフィールドにしておきました。一方、今回の場合はバッチ処理で求めているので、残数はバッチ処理内で簡単に求められます。結果、残数が必要なので、それをフィールドとして置いておき、むしろ受注数や出荷数は不要になります。ですが、説明上ややこしくなるので、受注数と出荷数のフィールドは置いておきました。変に細かく考えているとも言えるかもしれませんが、残数の出所の違いでこうした変化が発生しうるということでもあります。

この方法は、期の概念の導入と、バッチ処理などのロジックの発生をどう評価するかです。ロジックは発生しますが、銀行口座方式のように、単一のフィールドを複数のユーザで更新するような仕組みよりも、バラバラにレコードを作っておいて必要な時に集計する方が、システムの動作上は安定することが期待できます。どちらが良いのかという問題は、実際の要求に照らし合わせないと結論は出ませんが、少なくとも、いろいろな実現方法があることは考慮すべきでしょう。

ということで、一旦、あるロボットおもちゃメーカーを題材にしたシリーズはここまでとします。しばらくは、データベースを理解するのに必要な概念を深掘りしていこうと思います。更新頻度は落ちるかな〜