RDBMSで順番が違うだけのデータを「重複している」判定をさせるには？

Question

#実現したいこと
MySQLで以下のことを実現したいと思っています。

「レコードの入った『ノード』に対し、
各ノード同士のつながりをあらわす『線』
を扱う」

たとえば、以下のようなアプリをつくることを考えてみます。

「データベースに登録されている人たちの名前を表示し、友達同士の人は線で繋ぐアプリ」

すると、以下のように、
メンバーのデータを扱うテーブル『Member』と、
友達関係のメンバー同士のつながりのデータを扱うテーブル『Connection』
を作ることになると思います。

テーブル：Member

Mem_code	name	family name
1	John	Smith
2	Ellen	Jager
3	Ella	Fitzgerald
4	Takahiro	Karasawa
...	...	...

Mem_codeは、メンバーを区別する主キー。

テーブル：Connection

Con_code	person1	person2
1	3	1
2	26	4
3	1	13
4	2	4
…	…	…

person1とperson2の値は、テーブル『Member』のMem_code。
Con_codeは、つながりを区別する主キー。

こんな具合にデータベースを作ったら、あとはUIのプログラミングで、
各メンバーデータをノードに、各つながりデータを線に対応させるようにすることになります。

ここで問題なのが、テーブル『Connection』における、つながりデータの管理です。
Mem_codeが1の人と3の人のつながりを考えると、(person1, person2)の値は
上記のようなテーブルだと、そのままだと
(1,3)と(3,1)は別モノ
となりますが、
今回は、漠然とした「つながり」を考えているので、1 -> 3と 3 -> 1　という風に方向によって区別しないようにしたいです。
なので、
(person1,person2)に(1,3)が登録されている状態で(3,1)が入力されたら、重複判定をして登録をはじく
という設計にしたいです。

そうするためには、どうすればいいでしょうか？
「もし、RDBMSに順不同解析機能のようなものがあるのなら、Con_codeを使わず、(person1,person2)を複合主キーにしてしまえばいいのかもしれない」
などいろいろ考えましたが、
そこら辺の細かいRDBMSやMySQLの仕様がよくわからないので、質問させて頂きます。

それとも、冒頭のことを実現するには、根本的にデータベース設計を変えた方がいいのでしょうか？

ご回答お待ちしております。

Comments

[m.ts10806]

現在の内容だけだとあくまでDB設計だけの話でPHPは関係ないように思います。例えPHPで組む予定であったとしても質問内容自体に直接関係ないタグはあっても関連性はないのでタグを精査してください。

[m.ts10806]

むしろ「データベース設計」のタグがあったほうが質問としての有用性はあると思います

[Zuishin]

https://qiita.com/WeilSpinor/items/ba124355f5733a1adebd
これを元に作っているならそう書いた方が誤解なく伝わると思います。

Answer 1

例示されている程度のテーブル構造でしたら以下のような条件で単純に両方向の登録を検索すればよいのでは。

(person1 = 1 AND person2 = 3) OR (person1 = 3 AND person2 = 1)

またはperson1、person2を常に昇順で登録するようにプログラム側で制御する手もあります。
※その場合、例で挙げられているConnectionテーブルのCon_code 1、2のデータは修正しなければなりませんが。

Answer 2

一番簡単なのはperson1とperson2にまたがるunique属性をつけて
常にperson1<person2になるよう値を投入することです
（person1>person2となったとき値を入れ替えて投入する）

追記

よく考えたら仮想生成列(virtual generated column)を使うこともできますね

• テーブル作成

create table tbl (
Con_code int unique key,
person1 int,
person2 int,
person_unique varchar(100) as (concat(least(person1,person2),',',greatest(person1,person2))) unique key
);

• データ挿入

insert ignore into tbl(Con_code,person1,person2) values(1,3,1),(2,26,4),(3,1,13),(4,2,4),(5,1,3),(6,3,10);

上記Con_codeは1と前後逆の同等のデータになるため挿入されない

Comments

[nikuatsu]

突然すみません。同じ問題でしたので参考にさせて頂きました。素晴らしい方法をありがとうございます。

Con_codeカラムについて教えて頂きたいのですが、PKやAUTO_INCREMENTをつけていない点には何か理由があるのでしょうか？

[yambejp]

だいぶ前の回答ですね(苦笑)、当時の記憶はあまりありませんが。
Con_codeはPK/AUTO_INCREMENTで運用しても問題ないと思いますよ
サンプル提示としてidとなるCon_codeを指定してinsertすることによって
どのコードが挿入できなかったか明確になりますよね？
たとえばサンプルでいえば1と5がかぶるので5の挿入が失敗して4の後が6になっています。
挿入されなかったレコードが5番だと特定できます

[nikuatsu]

検索で出てきました(笑)
ご返答ありがとうございます。仰る通りでした。試してみるとAUTO_INCREMENTでは12345となり、なるほどと思いました。

Answer 3

今回は、漠然とした「つながり」を考えているので、1 -> 3と 3 -> 1　という風に方向によって区別しないようにしたいです。

逆に、「追加時にA-BとB-Aを両方登録してしまう」という方法も考えられます。

Answer 4

つながりを追加する際に、既存のつながりと重複しているか判断したい
という話でよろしいでしょうか。

処理の流れとしては下のような感じになると思いますが

1. 個人を特定する
2. 関係を追加する

上記 1) の時点でつながりも読み込んでおけば
それを使って重複チェックはできると思います。
つまり、(データベース側でなく)アプリ側で処理する、と。

複数のアプリで同時に処理されるとか、つながりがしこたま多くなるとか
した場合に難点はありますが、まあ、こういう感じではどうでしょうか。