こんな場合のER図はどうなりますか？

質問

こんにちは。データベースの勉強を始めて間もない者です。

例えば、ある中学校の１つのクラスの振る舞いを、ER図で表現することを考えます。各生徒をあらわす「生徒」テーブルを作ります。主キーは出席番号とします。

主キー以外には、名前、生年月日、得意科目、、、などを入れるのですが、「ライバル」という属性も入れたいと思います。自分（今、対象としている生徒）から見てライバルだと思うクラスメイトにあたるものです。

生徒によっては０人だったり多数だったりするので、「生徒」テーブルから出し、「ライバル」テーブルを作り、「生徒」：「ライバル」＝１：０以上のリレーションをはります。「ライバル」テーブルの主キーは親の生徒の出席番号（外部キー）とライバルだと思われた生徒の出席番号のペアでいいや、と思ったのですが、ここで問題に気づきました。

出席番号１さんがライバルだと思っているのが、出席番号３さん、５さんとします。３さんは１さんのことをライバルと思っていませんが、５さんは１さんのことをライバルだと思っています。この時の「ライバル」テーブルは、主キーに１と５のペアを持つレコードが２つできてしまって問題になるのでしょうか？それとも、外部キーが異なるので問題はないのでしょうか？

問題があるとすると、どのようにテーブルを定義すればよいのでしょうか。さっきの例では「ライバル」テーブルに１と３のペアをキーとするレコードが１つできますが、これも「１から３へ」という意味が消えないような設計をしたいのですが、よくわからなくなりました。

回答No.1

jjon-com · ベストアンサー

> 出席番号１さんがライバルだと思っているのが、
> 出席番号３さん、５さんとします。
> ３さんは１さんのことをライバルと思っていませんが、
> ５さんは１さんのことをライバルだと思っています。

という関係は，「ライバル」テーブルに次の3行を登録すれば表現できます。
(user_id, rival_id) = ((1, 3), (1, 5), (5, 1))

(user_id, rival_id) = (1, 5) と
(user_id, rival_id) = (5, 1) は，主キーの値はまったく別です。

ちなみに，
「ライバル」テーブルのrival_idが「生徒」テーブルの主キーを参照する外部キーであるのと同様，
「ライバル」テーブルの user_idも「生徒」テーブルの主キーを参照する外部キーです。

それから。
出席番号というのはクラス内の連番を指す概念ですか？
であるなら。
変則的な理由で年度の途中で出席番号が変わる可能性もありえますし，1年→2年→3年の進級によっても出席番号は維持できません。学籍番号（学生番号）に代表される，入学以降変化しない識別値を「生徒」テーブルの主キーにすべきです。

お礼

こんにちは。早速のご回答、ありがとうございます。

>(user_id, rival_id) = (1, 5) と
>(user_id, rival_id) = (5, 1) は，主キーの値はまったく別です。

ということで、当初考えた通りに作れそうで安心しました。
また、

>学籍番号（学生番号）に代表される，入学以降変化しない識別値を「生徒」テーブルの主キーにすべきです。

そうですね。その年度以降も使えるようにするには学年全体で考えた方がよさそうですね。中学校には学生番号は無いので、入学年度かクラス番号を組み合わせて、考えようと思います。
アドバイス、ありがとうございました。