一対多・多対多・一対一の関係

 主キーと外部キーによる参照を通じて形成される対応関係には、いくつかの分類があります。ここではその分類について説明していきます。特に多対多の関係については、データベース学習における重要なポイントですので慎重に内容を確認していただきたいと思います。さらに、それらの対応関係はそのままテーブル間の対応関係として捉えることができます。多数のテーブルを概観しデータの全体像を速やかに理解するのに有益ですので、この点についても触れていきます。

各種の対応関係

 先の節では、主キーと外部キーによる参照についていくつかの例を見てきました。
 この参照についててですが、1つの主キーの値に対してそれを参照している外部キーがどれほどあるのか、そしてどのように参照しているのか、という観点から3種に分類されています。
 用語として知らなくても支障はありませんが、データの全体像を理解するに当たって有益なものであり、容易に理解できるものです。

一対多の関係

 ある主キーの値を外部キーにより一方的に参照しているという基本的な対応関係で、単純にいえば親子関係にあたる関係です。次に見る「多対多の関係」もこの関係の応用にすぎませんので、ほとんどの参照が一対多の関係に基づくものといえます。
 先の節の繰り返しになりますが、メーカーと商品の例が適当ですのでもう一度見てみましょう。

メーカーテーブル
メーカーコードメーカー名
1篠山飲料
2山本フーズ
3東亜ビバレッジ

商品テーブル
商品コードメーカーコード商品名
A000011100%ドリアンジュース
A000021ホット専用冬のコーラ
A000032半生茶
A000042ミラクルエナジードレイン
A000053水素水コーヒー
A000063荒川深層水
A000073はちみつメロン

この例では商品テーブル側のメーカーコード(外部キー)によりメーカーテーブルのメーカーコード(主キー)を参照する形になっています。1つのメーカーコードに対し、複数の商品コードが対応していて、逆に1つの商品からは1つのメーカーコードにしか対応していません。1つのメーカーは一般に多数の商品を生産していて、1つの商品は通常1つのメーカーにより生産されている(最終製品として見た場合)からです。
 このように主キーの1つの値に対して、それを参照しているいくつかの外部キーが存在している関係を、一対多の関係といいます。「一」は主キーの側を、「多」は外部キーの側を表しています。

多対多の関係

 一対多のような一方的な参照関係ではなく、ある主キーの値と別の主キーの値がお互いを参照するという、幾分複雑な対応関係です。2つ以下のテーブルで表現すること自体は可能ですが正規化の観点から見て問題のあるものとなります。そこで、3つ(以上)のテーブルを用いて1対多の関係を複数適用することにより表すこととなります。
 例として、学生と履修科目の例を考えてみます。一般に、1人の学生が複数の科目を履修するので、親子関係に例えれば学生が親で科目が子であり、メーカーと商品の関係と同じような1対多の関係となる、とみることができます。しかし、この場合は逆も真なりで、1つの科目を複数の学生が履修するというのも事実です。科目が親で学生が子であるという逆転した見方もできるというわけです。つまり、メーカーと商品のような単純な親子関係ではないことがわかります。
 しかし、この両方の視点を踏まえたテーブルをいきなり作ろうというのも難しそうですので、あえて前者(学生が親で科目が子)から2つのテーブルで表現してみると、例えば次のようになります。

学生テーブル
学生番号氏名
1吉野 直子
2杉山 陽一
3柿崎 健吾

科目テーブル
科目番号科目名学生番号
S001家族社会学1
S001家族社会学2
S002マスコミュニケーション論11
S002マスコミュニケーション論13
S003ポップカルチャー概論1
S003ポップカルチャー概論2
S003ポップカルチャー概論3

確かに科目テーブルをみると学生番号と科目番号の組み合わせによって学生と科目の間にある相互の参照関係はきちんと表現されているのですが、テーブルは第1正規形にとどまっており、科目名称を繰り返し記録しなければならないという冗長さが残っています。
 そこでテーブルを分割し更なる正規化を図ることにより、結果的に次のようなテーブル構成となります。

学生テーブル
学生番号氏名
1吉野 直子
2杉山 陽一
3柿崎 健吾

科目テーブル
科目番号科目名
S001家族社会学
S002現代マスコミュニケーション論Ⅰ
S003ポップカルチャー概論

履修テーブル
科目番号学生番号
S0011
S0012
S0021
S0023
S0031
S0032
S0033

 科目が親で学生が子、という視点からスタートしても結果は同じものとなります。
 なお、履修テーブルにある学生番号(外部キー)が学生テーブルの学生番号(主キー)を、そして科目番号(外部キー)が科目テーブルの科目番号(主キー)を参照し、それぞれ1対多の関係となっています。
 結果的に、多対多の関係は1対多の関係を複数組み合わせることで表現されることがわかります。

 ところで、1対多の関係が複数存在するからといって、それらが全体として多対多の関係を表しているとは限りません。
 上記の社員テーブル等の再掲です。

社員テーブル
社員番号氏名性別
1吉田 素子
2小芝 悦郎
3神崎 真由
4駒田 美樹
5篠崎 圭吾

時間外勤務テーブル
社員番号年月日開始時刻終了時刻
12014/10/717:0020:00
12014/10/2117:0021:00
32014/10/317:0022:30
52014/10/217:0019:00
52014/10/1413:0017:00

所有資格テーブル
社員番号資格コード取得年
1A1022008
3B2022011
3F3112010
4G1012012
5C2102014

 時間外勤務テーブルと所有資格テーブルの社員番号がそれぞれ社員テーブルの社員番号を参照しています。それぞれ1対多の関係ですが、全体として多対多の関係にはなっていません。親子関係に例えれば社員テーブルの社員番号が親であって時間外勤務テーブルの社員番号と所有資格テーブルの社員番号はそれぞれ子です。上記の学生番号と科目番号の例のように、お互いに親とみなせるようなもの(列)が存在していません。

一対一の関係

 さて、最後の分類として一対一の関係というものを紹介します。かなり特殊なのであまり用いる機会もないのですが、どういう関係を指しているかというと、2つのテーブルで全く同じ主キーを共用しているような関係といえます。
 具体的な例として、企業の拠点間における発送と受領が挙げられます。テーブルの例ですが、まず発送側が扱うテーブルは次のようになります。

発送テーブル
発送ID製品コード数量発送担当者コード発送日
1AX100214502012014/12/7
2CR200434502012014/12/10
3GS104398002042014/12/16

発送担当者は発送しようとする製品の内容を確認してこれらのテーブルに記録し、製品を送り出します。
 一方で、受領側が扱うテーブルは次のようになります。

受領テーブル
受領ID製品コード数量受領担当者コード受領日
1AX100214504082014/12/9
2CR200434504122014/12/11
3GS104398004122014/12/17

形としては発送側のテーブル同じであり、受領担当者は伝票等により発送IDを識別し、受領した製品の検査を行った上で、受領テーブルにその結果を記録しています。この場合、1回の発送(1つの発送ID)に対し1回の受領(1つの受領ID)となります。受領IDは確かに発送IDを参照しているのですが、同じ値は2回以上現れません。よって受領IDも主キーとなります。これが典型的な1対1の関係の例です。
 そこでですが、主キーが同じなのであれば1つのテーブルにまとめればいいのではないかとも思われます。確かにこれらのテーブルををまとめて、例えば

発送受領テーブル
発送受領ID発送製品コード発送数量発送担当者コード発送日受領製品コード受領数量受領担当者コード受領日
1AX100214502012014/12/7AX100214504082014/12/9
2CR200434502012014/12/10CR200434504122014/12/11
3GS104398002042014/12/16GS104398004122014/12/17

としても運用上大きな問題はなさそうですし、実際に有力な選択肢です。しかし、このテーブルの場合、発送内容を新たな行に記録した時点で受領に関する記録も同時に発生することとなります(その時点で受領製品コードなど受領に関する列の値はNullですが)。実際には発送、そして受領という事実が発生する時期は異なりますので、発送した時点で受領に関する記録が存在することは事実に即しているとはいえませんし、その度に厄介なNullを生じさせることは決して好ましいことではありません。そこで、別のテーブルによる取り扱い行うべきと判断し、発送テーブルと受領テーブルを分ける場合があるのです。
 もう1つの例として、個人事業主と法人事業主のように、一定程度共通する部分を持ちながら異なる側面があるものを記録していく場合が挙げられます。

商業会員テーブル
会員コード名称所在地業種個人法人区分
1山形屋商店新町1−6−2食料品店個人
2(株)タケダマート新町店新町1−6−4スーパー法人
3フラワーよしだ新町1−7−2生花店個人
4杉本薬局中町2−2−1調剤薬局個人
5第百六十七銀行駅前支店中町2−2−3金融機関法人

法人テーブル
会員コード本店登記地資本金額
2○○市☆☆1−1130,000,000
5△△市□□3−118,000,000,000

 法人テーブルには法人会員特有のデータが記録されるようになっていますが、1つの会員につき1行あれば十分なのでこちらも会員IDを主キーとしています。よってこれも一対一の関係となります。
 この例でもテーブルを1つにまとめることはできます。実際にやってみると

商業会員テーブル
会員コード名称所在地業種個人法人区分本店登記地資本金額
1山形屋商店新町1−6−2食料品店個人
2(株)タケダマート新町店新町1−6−4スーパー法人○○市☆☆1−1130,000,000
3フラワーよしだ新町1−7−2生花店個人
4杉本薬局中町2−2−1調剤薬局個人
5第百六十七銀行駅前支店中町2−2−3金融機関法人△△市□□3−118,000,000,000

となります。ただ、個人事業主については記録されない列が2つありますので、やはりNullを安易に許容することとなってしまいます。それを避けたい場合に上記のようにテーブルを分けて一対一の関係にするというわけです。なお、個人事業主だけに該当する列があるとすればさらに個人テーブルを設けることとなります。
 2つの例を紹介しましたが、いずれも潔癖といいますか格調の高い手法ですし、特に前者は遠隔地から同一のデータベースを利用する例ですので、個人ユースのデータベースではなかなかお目にかかれないかと思います。

 その他に一対一の関係を用いる例として

  • テーブルの列数が、設定できる上限を超えるため複数のテーブルに分割しなければならない場合
  • テーブルごとにアクセス権を定める(あるテーブルについては決まった人しか扱えないようにする)ことを目的としてテーブルを分割する場合

といったものが考えられます。これらの理由の方がわかりやすいですし、こうした理由で一対一の関係としている例の方が実際には多いかもしれません。しかし、そもそも一対一の関係という分類がなされている理由とは異なりますし、具体的なテーブルの例を示すまでもない内容ですので詳細は省略します。

テーブル間の対応関係

 ここまで、参照関係は主キーと外部キーの関係、つまり列どうしの関係であると説明してきました。
 主キーはその性質上テーブルを代表する列となりますし、外部キーはその主キーを参照することによって、主キーがあるテーブルから行を特定し必要な列の値を知ることができますので、テーブルそのものを参照していると捉えることができます。このことから、主キーと外部キーによる参照関係はそのまま主キーが属するテーブルと外部キーが属するテーブルの対応関係であるとみなすことができます。
 例えば、主キーと外部キーが一対多の関係にある場合、それらの属するテーブルが一対多の関係にあるものと解釈され、それぞれ「一側のテーブル」、「多側のテーブル」というように呼ばれます。