database :: SQL/JOIN/自己結合

SNSではよくあることですが、あるユーザとそのユーザの友達関係を管理することになったとします。
例えば、こんなテーブルで管理するのはバッドノウハウです。

+-----------+--------+------------+------------+------------+----
| person_id | name   | friend1_id | friend2_id | friend3_id | … 
+-----------+--------+------------+------------+------------+----
|         1 | taro   |          2 |          3 |            | … 
|         2 | jiro   |          5 |          1 |          7 | … 
|         3 | saburo |          2 |            |            | … 
+-----------+--------+------------+------------+------------+----

テーブルが意味すること

• person_id:1 taro の友達は person_id が 2 と 3 のユーザ。
• person_id:2 jiro の友達は person_id が 5 と 1 と 7 のユーザ。
• person_id:3 saburo の友達は person_id が 2 のユーザ。

仮に友達になれる人数は100人までという制約を付けたとしましょう。
カラムは friend100_id まで作るわけですが、ほとんどの friend*_id カラムが空になることは容易に想像できます。
相応の無駄が発生しますし、そもそも美しくありません。拡張性も皆無です。


じゃ、こんなテーブルはどうでしょう。
友達の person_id をカンマ区切りで管理。

+-----------+--------+-----------+
| person_id | name   | friend_id |
+-----------+--------+-----------+
|         1 | taro   | 2,3       |
|         2 | jiro   | 5,1,7     |
|         3 | saburo | 2         |
+-----------+--------+-----------+

テーブルが意味することは先のテーブルと同じです。 これもあまり美しくありません。
アプリケーション側から利用する場合にも、参照・更新の際、カンマ区切りの friend_id をパースしなければならず、とても使いづらいです。


スマートな管理方法は「自己結合」で実現できます。
自己結合とはテーブルの結合において、自分自身と結合することです。テーブル「A」にテーブル「A」を結合します。
今回紹介する方法は 対照表テーブル も結合しているため、純粋な自己結合ではありませんが、あるテーブルとそのテーブル自身が結合するという意味では自己結合の部類に入ります。

まず、person_id と名前を管理する person テーブルを実装します。

mysql> select * from person;
+-----------+--------+
| person_id | name   |
+-----------+--------+
|         1 | taro   |
|         2 | jiro   |
|         3 | saburo |
+-----------+--------+

次に、友達関係を管理する person_friend テーブルを実装します。これは、対照表テーブルになります。
ここでは更に拡張して友達をグループ分けする目的で、group_id カラムも追加します。
友達でも「学生時代の友達」「会社の同僚」「草野球のメンバー」等とグルーピングできるようにします。

mysql> select * from person_friend;
+-----------+-----------+----------+
| person_id | friend_id | group_id |
+-----------+-----------+----------+
|         1 |         2 |        1 |
|         1 |         3 |        3 |
|         2 |         1 |        2 |
|         3 |         4 |        1 |
+-----------+-----------+----------+

テーブルが意味すること

• person_id:1 taro の友達は person_id が 2 と 3 のユーザで、person_id:2 の友達は group_id:1、person_id:3 の友達は group_id:3 に属する。
• person_id:2 jiro の友達は person_id が 1 のユーザで group_id は 2 に属する。
• person_id:3 saburo の友達は person_id が 4 のユーザで group_id は 1 に属する。

person_id:1 taro の友達(名前付き)を取得するときは以下の SQL を発行します。

mysql> SELECT p.person_id, p.name, pf.friend_id, j.name FROM person p 
    -> INNER JOIN person_friend pf USING(person_id) 
    -> INNER JOIN person j ON pf.friend_id=j.person_id 
    -> WHERE p.person_id=1;
+-----------+------+-----------+--------+
| person_id | name | friend_id | name   |
+-----------+------+-----------+--------+
|         1 | taro |         2 | jiro   |
|         1 | taro |         3 | saburo |
+-----------+------+-----------+--------+

さらにグループ指定して取得する場合。
person_id:1 taro の 友達で、group_id:1 に属する友達を取得する。

mysql> SELECT p.person_id, p.name, pf.friend_id, j.name FROM person p 
    -> INNER JOIN person_friend pf USING(person_id) 
    -> INNER JOIN person j ON pf.friend_id=j.person_id 
    -> WHERE p.person_id=1 AND group_id=1;
+-----------+------+-----------+------+
| person_id | name | friend_id | name |
+-----------+------+-----------+------+
|         1 | taro |         2 | jiro |
+-----------+------+-----------+------+

これなら、友達人数の制限が無制限になっても問題ありません。
グループ管理はユーザ自身が自由に定義し、設定することが可能です。
また、未知の改修にも柔軟に対応できます。