データベースでAとBの関連性を表現するときの設計

Question

peopleというテーブルがあったときに、Aliceさんに関連する人物としてBobさんとCharlieさんがいるということを表現したい場合、以下のようにすればいいのではないかと思いました。

でもこれだと、Bobさんに関連する人物としてAliceさんが登録できるので、データが冗長になってしまいます。うまく表現する方法があれば教えてください。

環境はMySQLで実現したいです。

Comments

[maisumakun]

フォロー/フォロワーのような向きはない、ということで間違いないですか？

[makkuro]

はい、間違いないです。向きがあれば上記のような設計で可能（冗長にならない）のはわかるのですが、向きがない場合に冗長でない設計があるのか知りたいです。

Answer 1

お望みの要件を網羅するかはわかりませんが、「関連する」を事象として関連IDを振るのはどうでしょうか？会社関連とか、飲み友とか、それぞれの関連IDにpeopleIDを付けたジャンクションテーブルを作成するです。

Answer 2

考え方だけの問題です。
・RDBで表現する多対多自己結合の最小モデルにはフォロー/フォロワーのように方向性がある。
・方向性を無くすにはひと手間必要。
ってことです。

Answer 3

構造を簡単にするだけならvarcharにしてidをカンマ区切りで保持し
find_in_setなどで検索する手はあります
しかし効率的な検索はできないのでおすすめできません。

Answer 4

Oracle SQL勉強用ユーザー作成スクリプト ですが、

EMP 従業員テーブルに EMPNO 従業員NO, MGR マネージャー

で上司やさらにその上の上司を調べるSQLを書くという、[関連する人物]の勉強ができます。
再帰呼び出しもあるのでお試し下さい。

VARCHAR2型はOracle独自の可変長文字列用のデータ型なのでVARCHAR型に直すなどの必要があります。

Answer 5

AliceとBobさんの関係が(Alice,Bob)があったとして(Bob, Alice)が登録できるので冗長だという事ですね。

冗長なのは順列であるからで、組合せのみにしたいのならルールが必要であり、それはテーブルのリレーションではどうしようもありません。

組合せのみの登録にするには、(Alice,Bob)や(Bob, Alice)の登録の際、その組み合わせを昇順で並べ替えた上で登録済みのデータとチェックするなどの仕組みが必要になります。

Comments

[xebme]

組み合わせを作るとき、Θ結合で ＜ 条件を指定することがあります。同様に、このテーブルにチェック制約（people_id_a < people_id_b）を付けることができますか。（people_id_a, people_id_b）はpkでかつそれぞれfkでもあるという条件のもとで、長短はなんでしょうか。

[sazi]

チェック制約は可能だと思います。
何に対する比較で、長短を聞かれているのか分からないのですけど。

[xebme]

つけるつけないで長短があるのかどうか知りたいと思ったのですが。

[sazi]

チェック制約を付けた場合は、データを登録後の後処理を考えないと駄目なので、チェック制約を付けるぐらいなら、プログラム側で並べ替えをする方が効率的だと思います。

[xebme]

チェック制約があれば、people_id_a と people_id_b を入れ替えた「重複」と、people_id_a と people_id_b が「等しい」場合が排除できるので、プログラムに頼らなくても良いと思ったのですが。ありがとうございました。

Answer 6

relationテーブルでは、

• 複合主キー、かつpeopleテーブルのidを参照する外部キー制約を持つ people_id_a と people_id_b を定義し、

アプリケーション側では、

• Peopleオブジェクトのidをソートした状態でinsertする

のはいかがでしょうか。