レコード同士のペアをどうモデリングするか【姉妹都市の関連のデータモデリング】

Question

データモデリングの経験に乏しく、どのように検索すれば求める情報にたどり着くのか分からなかったのでこちらで質問いたしました

状況

都市のデータが入っているテーブルがあるとします
都市は追加されることがあります

cities

id	city-name
01	A
02	B
03	C
04	D
05	E

このうち、都市同士で以下の条件で姉妹都市協定が結ばれているとします

• 協定は、2都市間でのみ結ばれる
• 都市は、複数の都市と協定を結ぶことができる

DBに接続するアプリでは、都市IDを引数にその都市の姉妹都市の配列を返却する関数を実装したいとします、
このとき、どのようなデータモデリングがベストプラクティスになるのでしょうか

案 : ペアテーブルのようなものを作る

city-pairs

pair-id	city-id-1	city-id-2
1	01	02
2	01	03
3	01	05
4	02	03
5	03	04
6	03	05

ひとつの姉妹都市ペアをひとつのレコードとして表現するのは扱いづらいでしょうか

Answer 1

モデリングの観点から言うと、「姉妹都市協定」を独立したエンティティと考えるのが素直ですね。

そのエンティティは案の内容（組み合わせに重複が無い）で問題無いと思います。
※但し、ペアの格納順のルールは必要でしょうけど。

DBに接続するアプリでは、都市IDを引数にその都市の姉妹都市の配列を返却する

姉妹都市協定を結んでいる都市の一覧を配列でという事でしたら、SQLレベルで配列操作が可能なDBMS(Postgres、BigQueryなど）もありますので選定の条件とするのもアリだと思います。

例えばpostgresだと以下のSQLで配列が返却されます。

select array_agg(city_id order by city_id)
from (
  select unnest(array[city_id_1,city_id_2]::text[]) as city_id
  from city_pairs 
  where '02' in (city_id_1, city_id_2)
) step1
where city_id !='02'

Answer 2

その案でいいと思います。
入力もしやすいので。

特定都市の姉妹都市の一覧は、
city-pairs と city-id-1、city-id-2 を入れ替えたものをUNIONで連結したものに抽出条件を設定すればいいでしょう。

SELECT T.[city-id-2]
FROM
 (SELECT [city-id-1], [city-id-2]
  FROM [city-pairs]
  UNION ALL
  SELECT [city-id-2], [city-id-1]
  FROM [city-pairs]
 )  AS T
WHERE T.[city-id-1]=3;