SNSではよくあることですが、あるユーザとそのユーザの友達関係を管理することになったとします。
例えば、こんなテーブルで管理するのはバッドノウハウです。
+-----------+--------+------------+------------+------------+---- | person_id | name | friend1_id | friend2_id | friend3_id | … +-----------+--------+------------+------------+------------+---- | 1 | taro | 2 | 3 | | … | 2 | jiro | 5 | 1 | 7 | … | 3 | saburo | 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 の友達(名前付き)を取得するときは以下の 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 |
+-----------+------+-----------+------+
これなら、友達人数の制限が無制限になっても問題ありません。
グループ管理はユーザ自身が自由に定義し、設定することが可能です。
また、未知の改修にも柔軟に対応できます。