主キーと外部キーによる参照を通じて形成される対応関係には、いくつかの分類があります。ここではその分類について説明していきます。特に多対多の関係については、データベース学習における重要なポイントですので慎重に内容を確認していただきたいと思います。さらに、それらの対応関係はそのままテーブル間の対応関係として捉えることができます。多数のテーブルを概観しデータの全体像を速やかに理解するのに有益ですので、この点についても触れていきます。
各種の対応関係
先の節では、主キーと外部キーによる参照についていくつかの例を見てきました。
この参照についててですが、1つの主キーの値に対してそれを参照している外部キーがどれほどあるのか、そしてどのように参照しているのか、という観点から3種に分類されています。
用語として知らなくても支障はありませんが、データの全体像を理解するに当たって有益なものであり、容易に理解できるものです。
一対多の関係
ある主キーの値を外部キーにより一方的に参照しているという基本的な対応関係で、単純にいえば親子関係にあたる関係です。次に見る「多対多の関係」もこの関係の応用にすぎませんので、ほとんどの参照が一対多の関係に基づくものといえます。
先の節の繰り返しになりますが、メーカーと商品の例が適当ですのでもう一度見てみましょう。
| メーカーコード | メーカー名 |
|---|---|
| 1 | 篠山飲料 |
| 2 | 山本フーズ |
| 3 | 東亜ビバレッジ |
| 商品コード | メーカーコード | 商品名 |
|---|---|---|
| A00001 | 1 | 100%ドリアンジュース |
| A00002 | 1 | ホット専用冬のコーラ |
| A00003 | 2 | 半生茶 |
| A00004 | 2 | ミラクルエナジードレイン |
| A00005 | 3 | 水素水コーヒー |
| A00006 | 3 | 荒川深層水 |
| A00007 | 3 | はちみつメロン |
この例では商品テーブル側のメーカーコード（外部キー）によりメーカーテーブルのメーカーコード（主キー）を参照する形になっています。1つのメーカーコードに対し、複数の商品コードが対応していて、逆に1つの商品からは1つのメーカーコードにしか対応していません。1つのメーカーは一般に多数の商品を生産していて、1つの商品は通常1つのメーカーにより生産されている（最終製品として見た場合）からです。
このように主キーの1つの値に対して、それを参照しているいくつかの外部キーが存在している関係を、一対多の関係といいます。「一」は主キーの側を、「多」は外部キーの側を表しています。
多対多の関係
一対多のような一方的な参照関係ではなく、ある主キーの値と別の主キーの値がお互いを参照するという、幾分複雑な対応関係です。2つ以下のテーブルで表現すること自体は可能ですが正規化の観点から見て問題のあるものとなります。そこで、3つ（以上）のテーブルを用いて1対多の関係を複数適用することにより表すこととなります。例として、学生と履修科目の例を考えてみます。一般に、1人の学生が複数の科目を履修するので、親子関係に例えれば学生が親で科目が子であり、メーカーと商品の関係と同じような1対多の関係となる、とみることができます。しかし、この場合は逆も真なりで、1つの科目を複数の学生が履修するというのも事実です。科目が親で学生が子であるという逆転した見方もできるというわけです。つまり、メーカーと商品のような単純な親子関係ではないことがわかります。
しかし、この両方の視点を踏まえたテーブルをいきなり作ろうというのも難しそうですので、あえて前者（学生が親で科目が子）から2つのテーブルで表現してみると、例えば次のようになります。
| 学生番号 | 氏名 |
|---|---|
| 1 | 吉野 直子 |
| 2 | 杉山 陽一 |
| 3 | 柿崎 健吾 |
| 科目番号 | 科目名 | 学生番号 |
|---|---|---|
| S001 | 家族社会学 | 1 |
| S001 | 家族社会学 | 2 |
| S002 | マスコミュニケーション論1 | 1 |
| S002 | マスコミュニケーション論1 | 3 |
| S003 | ポップカルチャー概論 | 1 |
| S003 | ポップカルチャー概論 | 2 |
| S003 | ポップカルチャー概論 | 3 |
確かに科目テーブルをみると学生番号と科目番号の組み合わせによって学生と科目の間にある相互の参照関係はきちんと表現されているのですが、テーブルは第1正規形にとどまっており、科目名称を繰り返し記録しなければならないという冗長さが残っています。
そこでテーブルを分割し更なる正規化を図ることにより、結果的に次のようなテーブル構成となります。
| 学生番号 | 氏名 |
|---|---|
| 1 | 吉野 直子 |
| 2 | 杉山 陽一 |
| 3 | 柿崎 健吾 |
| 科目番号 | 科目名 |
|---|---|
| S001 | 家族社会学 |
| S002 | 現代マスコミュニケーション論Ⅰ |
| S003 | ポップカルチャー概論 |
| 科目番号 | 学生番号 |
|---|---|
| S001 | 1 |
| S001 | 2 |
| S002 | 1 |
| S002 | 3 |
| S003 | 1 |
| S003 | 2 |
| S003 | 3 |
科目が親で学生が子、という視点からスタートしても結果は同じものとなります。
なお、履修テーブルにある学生番号（外部キー）が学生テーブルの学生番号（主キー）を、そして科目番号（外部キー）が科目テーブルの科目番号（主キー）を参照し、それぞれ1対多の関係となっています。
結果的に、多対多の関係は1対多の関係を複数組み合わせることで表現されることがわかります。
ところで、1対多の関係が複数存在するからといって、それらが全体として多対多の関係を表しているとは限りません。
上記の社員テーブル等の再掲です。
| 社員番号 | 氏名 | 性別 |
|---|---|---|
| 1 | 吉田 素子 | 女 |
| 2 | 小芝 悦郎 | 男 |
| 3 | 神崎 真由 | 女 |
| 4 | 駒田 美樹 | 女 |
| 5 | 篠崎 圭吾 | 男 |
| 社員番号 | 年月日 | 開始時刻 | 終了時刻 |
|---|---|---|---|
| 1 | 2014/10/7 | 17:00 | 20:00 |
| 1 | 2014/10/21 | 17:00 | 21:00 |
| 3 | 2014/10/3 | 17:00 | 22:30 |
| 5 | 2014/10/2 | 17:00 | 19:00 |
| 5 | 2014/10/14 | 13:00 | 17:00 |
| 社員番号 | 資格コード | 取得年 |
|---|---|---|
| 1 | A102 | 2008 |
| 3 | B202 | 2011 |
| 3 | F311 | 2010 |
| 4 | G101 | 2012 |
| 5 | C210 | 2014 |
時間外勤務テーブルと所有資格テーブルの社員番号がそれぞれ社員テーブルの社員番号を参照しています。それぞれ1対多の関係ですが、全体として多対多の関係にはなっていません。親子関係に例えれば社員テーブルの社員番号が親であって時間外勤務テーブルの社員番号と所有資格テーブルの社員番号はそれぞれ子です。上記の学生番号と科目番号の例のように、お互いに親とみなせるようなもの（列）が存在していません。
一対一の関係
さて、最後の分類として一対一の関係というものを紹介します。かなり特殊なのであまり用いる機会もないのですが、どういう関係を指しているかというと、2つのテーブルで全く同じ主キーを共用しているような関係といえます。
具体的な例として、企業の拠点間における発送と受領が挙げられます。テーブルの例ですが、まず発送側が扱うテーブルは次のようになります。
| 発送ID | 製品コード | 数量 | 発送担当者コード | 発送日 |
|---|---|---|---|---|
| 1 | AX10021 | 450 | 201 | 2014/12/7 |
| 2 | CR20043 | 450 | 201 | 2014/12/10 |
| 3 | GS10439 | 800 | 204 | 2014/12/16 |
発送担当者は発送しようとする製品の内容を確認してこれらのテーブルに記録し、製品を送り出します。
一方で、受領側が扱うテーブルは次のようになります。
| 受領ID | 製品コード | 数量 | 受領担当者コード | 受領日 |
|---|---|---|---|---|
| 1 | AX10021 | 450 | 408 | 2014/12/9 |
| 2 | CR20043 | 450 | 412 | 2014/12/11 |
| 3 | GS10439 | 800 | 412 | 2014/12/17 |
形としては発送側のテーブル同じであり、受領担当者は伝票等により発送IDを識別し、受領した製品の検査を行った上で、受領テーブルにその結果を記録しています。この場合、1回の発送（1つの発送ID）に対し1回の受領（1つの受領ID）となります。受領IDは確かに発送IDを参照しているのですが、同じ値は2回以上現れません。よって受領IDも主キーとなります。これが典型的な1対1の関係の例です。
そこでですが、主キーが同じなのであれば1つのテーブルにまとめればいいのではないかとも思われます。確かにこれらのテーブルををまとめて、例えば
| 発送受領ID | 発送製品コード | 発送数量 | 発送担当者コード | 発送日 | 受領製品コード | 受領数量 | 受領担当者コード | 受領日 |
|---|---|---|---|---|---|---|---|---|
| 1 | AX10021 | 450 | 201 | 2014/12/7 | AX10021 | 450 | 408 | 2014/12/9 |
| 2 | CR20043 | 450 | 201 | 2014/12/10 | CR20043 | 450 | 412 | 2014/12/11 |
| 3 | GS10439 | 800 | 204 | 2014/12/16 | GS10439 | 800 | 412 | 2014/12/17 |
としても運用上大きな問題はなさそうですし、実際に有力な選択肢です。しかし、このテーブルの場合、発送内容を新たな行に記録した時点で受領に関する記録も同時に発生することとなります（その時点で受領製品コードなど受領に関する列の値はNullですが）。実際には発送、そして受領という事実が発生する時期は異なりますので、発送した時点で受領に関する記録が存在することは事実に即しているとはいえませんし、その度に厄介なNullを生じさせることは決して好ましいことではありません。そこで、別のテーブルによる取り扱い行うべきと判断し、発送テーブルと受領テーブルを分ける場合があるのです。
もう1つの例として、個人事業主と法人事業主のように、一定程度共通する部分を持ちながら異なる側面があるものを記録していく場合が挙げられます。
| 会員コード | 名称 | 所在地 | 業種 | 個人法人区分 |
|---|---|---|---|---|
| 1 | 山形屋商店 | 新町１−６−２ | 食料品店 | 個人 |
| 2 | （株）タケダマート新町店 | 新町１−６−４ | スーパー | 法人 |
| 3 | フラワーよしだ | 新町１−７−２ | 生花店 | 個人 |
| 4 | 杉本薬局 | 中町２−２−１ | 調剤薬局 | 個人 |
| 5 | 第百六十七銀行駅前支店 | 中町２−２−３ | 金融機関 | 法人 |
| 会員コード | 本店登記地 | 資本金額 |
|---|---|---|
| 2 | ○○市☆☆１−１１ | 30,000,000 |
| 5 | △△市□□３−１ | 18,000,000,000 |
法人テーブルには法人会員特有のデータが記録されるようになっていますが、1つの会員につき1行あれば十分なのでこちらも会員IDを主キーとしています。よってこれも一対一の関係となります。
この例でもテーブルを１つにまとめることはできます。実際にやってみると
| 会員コード | 名称 | 所在地 | 業種 | 個人法人区分 | 本店登記地 | 資本金額 |
|---|---|---|---|---|---|---|
| 1 | 山形屋商店 | 新町１−６−２ | 食料品店 | 個人 | ||
| 2 | （株）タケダマート新町店 | 新町１−６−４ | スーパー | 法人 | ○○市☆☆１−１１ | 30,000,000 |
| 3 | フラワーよしだ | 新町１−７−２ | 生花店 | 個人 | ||
| 4 | 杉本薬局 | 中町２−２−１ | 調剤薬局 | 個人 | ||
| 5 | 第百六十七銀行駅前支店 | 中町２−２−３ | 金融機関 | 法人 | △△市□□３−１ | 18,000,000,000 |
となります。ただ、個人事業主については記録されない列が2つありますので、やはりNullを安易に許容することとなってしまいます。それを避けたい場合に上記のようにテーブルを分けて一対一の関係にするというわけです。なお、個人事業主だけに該当する列があるとすればさらに個人テーブルを設けることとなります。
2つの例を紹介しましたが、いずれも潔癖といいますか格調の高い手法ですし、特に前者は遠隔地から同一のデータベースを利用する例ですので、個人ユースのデータベースではなかなかお目にかかれないかと思います。
その他に一対一の関係を用いる例として
- テーブルの列数が、設定できる上限を超えるため複数のテーブルに分割しなければならない場合
- テーブルごとにアクセス権を定める（あるテーブルについては決まった人しか扱えないようにする）ことを目的としてテーブルを分割する場合
といったものが考えられます。これらの理由の方がわかりやすいですし、こうした理由で一対一の関係としている例の方が実際には多いかもしれません。しかし、そもそも一対一の関係という分類がなされている理由とは異なりますし、具体的なテーブルの例を示すまでもない内容ですので詳細は省略します。
テーブル間の対応関係
ここまで、参照関係は主キーと外部キーの関係、つまり列どうしの関係であると説明してきました。
主キーはその性質上テーブルを代表する列となりますし、外部キーはその主キーを参照することによって、主キーがあるテーブルから行を特定し必要な列の値を知ることができますので、テーブルそのものを参照していると捉えることができます。このことから、主キーと外部キーによる参照関係はそのまま主キーが属するテーブルと外部キーが属するテーブルの対応関係であるとみなすことができます。
例えば、主キーと外部キーが一対多の関係にある場合、それらの属するテーブルが一対多の関係にあるものと解釈され、それぞれ「一側のテーブル」、「多側のテーブル」というように呼ばれます。
