中間テーブルを正規化の観点から考える

一般に『多対多の時は中間テーブル（連関エンティティ）を作成せよ』とあると思うのですが、今回は、中間テーブルの存在意義を正規化の観点から考えてみました。

問題設定

教師と生徒がエンティティとしてあり、以下のようないわゆる多対多の関係をしているとします。

• 1人の先生が複数の生徒に教えることができる
  • 先生によって科目は決まってるとする
• 1人の生徒は複数の先生から教わることができる

ボトムアップ方式

まず、どの先生がどの生徒に教えるかという関係が、すでに以下のようなテーブルにあるとします。

先生 id	先生 name	科目	生徒 id	生徒 name
1	久保	数学	101	池田
1	久保	数学	102	井上
2	山下	英語	101	池田
2	山下	英語	102	井上

主キーは（先生 id, 生徒 id）です。

この状態では、以下の理由により第１正規化された状態といえます。

• 繰り返し項目（直積集合や冪集合）を持たない
• 主キーに対する部分関数従属がある
  • 真部分集合 {先生 id} → {先生 name} の従属性
  • 真部分集合 {先生 id} → {科目} の従属性
  • 真部分集合 {生徒 id} → {生徒 name} の従属性

この時、第二正規形にするために部分関数重属性を排除してみると、元のテーブルには {先生 id, 生徒 id} のみが残り、自然と先生と生徒のエンティティが出てくることがわかります！

先生 id	生徒 id
1	101
1	102
2	101
2	102

先生 id	先生 name	科目
1	久保	数学
2	山下	英語

生徒 id	生徒 name
101	池田
102	井上

つまり、すでに先生と生徒の関係がある立場（ボトムアップの見方とする）からすると、これは第２正規化の結果自然と中間テーブルが残ったことを意味しています。 （ER 図で書くと ↓）

erDiagram

students ||--o{ lecture: ""
teachers ||--o{ lecture: ""

students {
    string id
    string name
}

lecture {
    string student_id
    string teacher_id
}

teachers {
    string id
    string name
    string subject
}

トップダウン方式

次に、先に以下のような2つのエンティティ（students と teachers）があり、これらの間に関係を持たせたい場合を考えます。。

先生 id	先生 name	科目
1	久保	数学
2	山下	英語

生徒 id	生徒 name
101	池田
102	井上

中間テーブルを使わずに関係性を表現するとなると、各 RDMBS の用意する配列型（postgresql）を利用するしかなく、以下のようになります。

先生 id	先生 name	科目	生徒 ids
1	久保	数学	101, 102
2	山下	英語	101, 102

生徒 id	生徒 name
101	池田
102	井上

erDiagram

students }o--o{ teachers: ""

students {
    string id
    string name
}

teachers {
    string id
    string name
    string subject
    array student_ids
}

これは第１正規形に反しており更新時異常をおこしやすく、良い設計とはいえません。

まとめ

スタートをどの立場から始めるにせよ、第２正規形までを満たそうとするならば、中間テーブルが現れることは必然であることが確認できました。

IPA がんばります。