多対多のテーブル設計｜中間テーブルの主キー・命名・属性の決め方

多対多のテーブル設計とは、「1人のメンバーが複数のチームに入り、1つのチームに複数のメンバーがいる」ような関係を、2つのテーブルの間に中間テーブル（交差テーブル）を1つ置いて表すことです。 中間テーブルは両方の id を外部キーとして持ち、1行が「この組がつながっている」ことを表します。

設計で迷いやすいのは、中間テーブルの主キーを複合主キーにするか連番（サロゲートキー）にするか、名前をどう付けるか、役割や開始日といった属性をどう持たせるかの3点です。この記事では、それぞれの決め方を判断表で示し、DDL・登録・エラー・EXPLAIN・集計の結果を実際に流した出力で確かめます。

確認環境：PostgreSQL 18.4、MySQL 8.0.46（いずれも Linux 上、確認日 2026-10-06）。出力例のデータベース名は appdb に置き換えています。

この記事の結論

• 関連だけを表すなら複合主キー（2つの外部キーの組を主キーにする）。重複登録を主キーがそのまま防ぎます。
• 中間テーブルの1行が他のテーブルから参照される、または同じ組が複数回ありうるなら、サロゲートキー＋UNIQUE。UNIQUE を付け忘れると同じ組が何件でも入ります。
• 複合主キー (team_id, member_id) は member_id だけの検索には効きにくい ので、逆順のインデックス (member_id, team_id) を足します。50万行での計測では、約20ミリ秒が 0.06 ミリ秒になりました。
• MySQL（InnoDB）は外部キーの列に自動でインデックスを作るため、外部キーを付けていれば逆向きの検索も最初から速く、PostgreSQL は自分で作る必要がありました。
• 名前は「関係を表す名詞」があればそれを使い、無ければ両テーブル名の連結にします。

多対多の中間テーブル設計の早見表

基本形：複合主キーの中間テーブル

メンバー（members）とチーム（teams）を、中間テーブル team_members でつなぎます。次の DDL は PostgreSQL 18.4 と MySQL 8.0.46 の両方でそのまま流せます。

CREATE TABLE members (
  id   INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);
CREATE TABLE teams (
  id   INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);
-- 中間テーブル（複合主キー）
CREATE TABLE team_members (
  team_id   INT NOT NULL,
  member_id INT NOT NULL,
  PRIMARY KEY (team_id, member_id),
  FOREIGN KEY (team_id)   REFERENCES teams(id)   ON DELETE CASCADE,
  FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE
);
INSERT INTO members (id, name) VALUES (1, '佐藤'), (2, '鈴木'), (3, '高橋'), (4, '田中');
INSERT INTO teams (id, name) VALUES (10, '開発チーム'), (20, '品質チーム'), (30, '広報チーム');
INSERT INTO team_members (team_id, member_id) VALUES
 (10, 1), (10, 2), (10, 3), (20, 1), (20, 4), (30, 1);

重複と、存在しない id の登録はエラーになる

INSERT INTO team_members (team_id, member_id) VALUES (10, 1);   -- 同じ組をもう一度
INSERT INTO team_members (team_id, member_id) VALUES (10, 99);  -- 存在しないメンバー

PostgreSQL 18.4 の出力です。

ERROR:  duplicate key value violates unique constraint "team_members_pkey"
DETAIL:  Key (team_id, member_id)=(10, 1) already exists.
ERROR:  insert or update on table "team_members" violates foreign key constraint "team_members_member_id_fkey"
DETAIL:  Key (member_id)=(99) is not present in table "members".

MySQL 8.0.46 の出力です。

ERROR 1062 (23000) at line 1: Duplicate entry '10-1' for key 'team_members.PRIMARY'
ERROR 1452 (23000) at line 2: Cannot add or update a child row: a foreign key constraint fails (`appdb`.`team_members`, CONSTRAINT `team_members_ibfk_2` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE)

「すでに入っていれば何もしない」登録にしたいときは、アプリ側で先に SELECT するより、主キーにまかせるほうが、同時に2件届いた場合も1件に保てます。PostgreSQL は INSERT ... ON CONFLICT DO NOTHING、MySQL は INSERT IGNORE で、同じ組の2回目がエラーにならず 0 件の登録として扱われることを確かめました（MySQL は警告 1062 が残ります）。

JOIN で一覧を取る

-- 開発チームのメンバー
SELECT t.name AS team, m.name AS member
FROM team_members tm
JOIN teams t   ON t.id = tm.team_id
JOIN members m ON m.id = tm.member_id
WHERE tm.team_id = 10
ORDER BY m.id;

-- メンバーごとの所属チーム数（所属なしも 0 で出す）
SELECT m.name AS member, COUNT(tm.team_id) AS team_count
FROM members m LEFT JOIN team_members tm ON tm.member_id = m.id
GROUP BY m.id, m.name ORDER BY m.id;

    team    | member 
------------+--------
 開発チーム | 佐藤
 開発チーム | 鈴木
 開発チーム | 高橋
(3 rows)

 member | team_count 
--------+------------
 佐藤   |          3
 鈴木   |          1
 高橋   |          1
 田中   |          1
(4 rows)

MySQL でも同じ行が返りました。

複合主キーの列順と逆順インデックス

複合主キー (team_id, member_id) のインデックスは、先頭の team_id で絞る検索に向いています。PostgreSQL の公式ドキュメント（Multicolumn Indexes）は「複数列の B-tree インデックスは先頭（左端）の列に条件があるときに最も効率がよい」と説明し、MySQL のリファレンス（Multiple-Column Indexes）も「列がインデックスの左端からの並びになっていないと、そのインデックスで検索できない」としています。

実際にどうなるかを、メンバー 100,000 人・チーム 5,000・中間テーブル 500,000 行（1人が5チームに所属）で確かめました。

PostgreSQL 18.4 の結果

CREATE TABLE big_members (id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE big_teams   (id int PRIMARY KEY, name text NOT NULL);
INSERT INTO big_members SELECT i, 'member' || i FROM generate_series(1, 100000) i;
INSERT INTO big_teams   SELECT i, 'team' || i   FROM generate_series(1, 5000) i;
CREATE TABLE big_team_members (
  team_id   int NOT NULL REFERENCES big_teams(id),
  member_id int NOT NULL REFERENCES big_members(id),
  PRIMARY KEY (team_id, member_id)
);
INSERT INTO big_team_members
SELECT ((m * 7 + k * 997) % 5000) + 1, m
FROM generate_series(1, 100000) m, generate_series(0, 4) k;
ANALYZE big_team_members;

EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF)
SELECT member_id FROM big_team_members WHERE team_id = 1234;
EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF)
SELECT team_id FROM big_team_members WHERE member_id = 4321;

先頭の列 team_id での検索は、主キーのインデックスが使われました。

 Bitmap Heap Scan on big_team_members  (cost=5.20..336.58 rows=100 width=4) (actual rows=100.00 loops=1)
   Recheck Cond: (team_id = 1234)
   Heap Blocks: exact=100
   ->  Bitmap Index Scan on big_team_members_pkey  (cost=0.00..5.17 rows=100 width=0) (actual rows=100.00 loops=1)
         Index Cond: (team_id = 1234)
         Index Searches: 1
 Planning Time: 0.041 ms
 Execution Time: 0.148 ms

2番目の列 member_id だけでの検索は、テーブル全体を読んでいます。

 Gather  (cost=1000.00..6889.97 rows=5 width=4) (actual rows=5.00 loops=1)
   Workers Planned: 1
   Workers Launched: 1
   ->  Parallel Seq Scan on big_team_members  (cost=0.00..5889.47 rows=3 width=4) (actual rows=2.50 loops=2)
         Filter: (member_id = 4321)
         Rows Removed by Filter: 249998
 Planning Time: 0.022 ms
 Execution Time: 20.377 ms

逆順のインデックスを足して、同じ SQL をもう一度流します。

CREATE INDEX idx_big_team_members_member ON big_team_members (member_id, team_id);

 Bitmap Heap Scan on big_team_members  (cost=4.46..23.81 rows=5 width=4) (actual rows=5.00 loops=1)
   Recheck Cond: (member_id = 4321)
   Heap Blocks: exact=5
   ->  Bitmap Index Scan on idx_big_team_members_member  (cost=0.00..4.46 rows=5 width=0) (actual rows=5.00 loops=1)
         Index Cond: (member_id = 4321)
         Index Searches: 1
 Planning Time: 0.233 ms
 Execution Time: 0.064 ms

サイズは、テーブル本体 17 MB、主キーのインデックス 14 MB、追加したインデックス 11 MB でした。外部キーを付けていても、PostgreSQL では member_id のインデックスは作られていません（インデックスは主キーの1つだけでした）。

MySQL 8.0.46 の結果

MySQL では、外部キーの有無で結果が変わりました。同じ 500,000 行を、外部キーなしの big_tm_nofk と、外部キーありの big_tm_fk に入れて比べます。

CREATE TABLE big_tm_nofk (
  team_id   INT NOT NULL,
  member_id INT NOT NULL,
  PRIMARY KEY (team_id, member_id)
);
CREATE TABLE big_tm_fk (
  team_id   INT NOT NULL,
  member_id INT NOT NULL,
  PRIMARY KEY (team_id, member_id),
  FOREIGN KEY (team_id)   REFERENCES big_teams(id),
  FOREIGN KEY (member_id) REFERENCES big_members(id)
);

SHOW INDEX で見ると、外部キーありのテーブルには、定義していない member_id という名前のインデックスが自動で作られていました。

| Table     | Non_unique | Key_name  | Seq_in_index | Column_name |
| big_tm_fk |          0 | PRIMARY   |            1 | team_id     |
| big_tm_fk |          0 | PRIMARY   |            2 | member_id   |
| big_tm_fk |          1 | member_id |            1 | member_id   |

EXPLAIN ANALYZE SELECT team_id FROM ... WHERE member_id = 4321; の結果です。

-- 外部キーなし（主キーだけ）
-> Filter: (big_tm_nofk.member_id = 4321)  (cost=21073 rows=48887) (actual time=0.81..15.1 rows=5 loops=1)
    -> Covering index skip scan on big_tm_nofk using PRIMARY over member_id = 4321  (cost=21073 rows=48887) (actual time=0.809..15.1 rows=5 loops=1)

-- 外部キーあり（自動で作られたインデックス）
-> Covering index lookup on big_tm_fk using member_id (member_id=4321)  (cost=0.751 rows=5) (actual time=0.00808..0.0105 rows=5 loops=1)

-- 外部キーなし + 逆順インデックス (member_id, team_id) を追加
-> Covering index lookup on big_tm_nofk using idx_big_tm_nofk_member (member_id=4321)  (cost=1.51 rows=5) (actual time=0.00985..0.0123 rows=5 loops=1)

主キーだけの場合、MySQL は先頭列の値を1つずつ飛ばしながら探すスキップスキャンを選びましたが、逆向きのインデックスがある場合とは 1000 倍以上の差がありました。

列順とインデックスの決め方

逆順インデックスを (member_id) だけでなく (member_id, team_id) の2列にすると、相手の id までインデックスの中で分かり、テーブル本体を読まずに済む場面が増えます。MySQL（InnoDB）は外部キーで自動的に作られますが、どのインデックスが使われるかを読み手に伝える意味で、PostgreSQL と同じく明示して作っておくと設計書と実物が一致します。

サロゲートキー＋UNIQUE にする場合

次の2つに当てはまるときは、連番などの単一列の主キー（サロゲートキー）を置きます。

• 同じ組が複数回ありうる：一度チームを抜けたメンバーが、時期を変えてもう一度入る
• 中間テーブルの1行を他のテーブルが参照する：所属ごとのメモ、評価、履歴など

複合主キーのままだと、2回目の所属は主キーの重複になります（後述の memberships テーブルで確認）。

ERROR:  duplicate key value violates unique constraint "memberships_pkey"
DETAIL:  Key (team_id, member_id)=(10, 2) already exists.

サロゲートキーにして、重複させたくない組み合わせを UNIQUE で宣言します。両方のデータベースで流せる形です（連番の自動採番は、PostgreSQL は GENERATED ALWAYS AS IDENTITY、MySQL は AUTO_INCREMENT を id 列に付けます。ここでは違いを避けるため id を明示しています）。

CREATE TABLE membership_periods (
  id         INT PRIMARY KEY,
  team_id    INT NOT NULL,
  member_id  INT NOT NULL,
  role       VARCHAR(20) NOT NULL DEFAULT 'member',
  joined_on  DATE NOT NULL,
  left_on    DATE NULL,
  UNIQUE (team_id, member_id, joined_on),
  FOREIGN KEY (team_id)   REFERENCES teams(id),
  FOREIGN KEY (member_id) REFERENCES members(id),
  CHECK (left_on IS NULL OR left_on > joined_on)
);
CREATE INDEX idx_membership_periods_member ON membership_periods (member_id, team_id);

-- 中間テーブルの1行を参照する子テーブル
CREATE TABLE membership_notes (
  id            INT PRIMARY KEY,
  membership_id INT NOT NULL,
  note          VARCHAR(200) NOT NULL,
  FOREIGN KEY (membership_id) REFERENCES membership_periods(id) ON DELETE CASCADE
);

INSERT INTO membership_periods (id, team_id, member_id, role, joined_on, left_on) VALUES
 (1, 10, 2, 'member', '2025-04-01', '2026-03-31'),
 (2, 10, 2, 'leader', '2026-10-01', NULL);
INSERT INTO membership_notes (id, membership_id, note) VALUES (1, 2, 'リーダーとして再参加');
-- 同じ開始日でもう一度
INSERT INTO membership_periods (id, team_id, member_id, role, joined_on) VALUES (3, 10, 2, 'member', '2026-10-01');

ERROR:  duplicate key value violates unique constraint "membership_periods_team_id_member_id_joined_on_key"
DETAIL:  Key (team_id, member_id, joined_on)=(10, 2, 2026-10-01) already exists.

 id |    team    | member |  role  | joined_on  |  left_on   |         note         
----+------------+--------+--------+------------+------------+----------------------
  1 | 開発チーム | 鈴木   | member | 2025-04-01 | 2026-03-31 | 
  2 | 開発チーム | 鈴木   | leader | 2026-10-01 |            | リーダーとして再参加
(2 rows)

MySQL 8.0.46 でも同じ2行が残り、3件目は ERROR 1062 (23000): Duplicate entry '10-2-2026-10-01' for key 'membership_periods.team_id' になりました。

UNIQUE を付け忘れるとどうなるか

サロゲートキーだけで UNIQUE が無いと、主キーは毎回違う値になるので、同じ組が何件でも入ります。

CREATE TABLE tm_no_unique (
  id INT PRIMARY KEY, team_id INT NOT NULL, member_id INT NOT NULL
);
INSERT INTO tm_no_unique VALUES (1, 10, 1), (2, 10, 1);
SELECT team_id, member_id, COUNT(*) AS cnt
FROM tm_no_unique GROUP BY team_id, member_id HAVING COUNT(*) > 1;

 team_id | member_id | cnt 
---------+-----------+-----
      10 |         1 |   2
(1 row)

最後の SELECT は、既存のテーブルに重複が入っていないかを調べるときにもそのまま使えます。

複合主キーとサロゲートキーの比較

属性（役割・開始日・並び順）を持たせる列設計

中間テーブルには、「その組に対して1つに決まる値」を列として置けます。メンバーの名前はメンバーのもの、チームの名前はチームのものですが、「そのチームでの役割」は組に対する値です。

CREATE TABLE memberships (
  team_id    INT NOT NULL,
  member_id  INT NOT NULL,
  role       VARCHAR(20) NOT NULL DEFAULT 'member',
  joined_on  DATE NOT NULL,
  sort_order INT  NOT NULL DEFAULT 0,
  PRIMARY KEY (team_id, member_id),
  FOREIGN KEY (team_id)   REFERENCES teams(id)   ON DELETE CASCADE,
  FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE,
  CHECK (role IN ('leader', 'member', 'guest'))
);
CREATE INDEX idx_memberships_member ON memberships (member_id, team_id);
INSERT INTO memberships (team_id, member_id, role, joined_on, sort_order) VALUES
 (10, 1, 'leader', '2025-04-01', 1), (10, 2, 'member', '2025-04-01', 2), (10, 3, 'member', '2026-04-01', 3);
-- 決めていない役割
INSERT INTO memberships (team_id, member_id, role, joined_on) VALUES (10, 4, 'owner', '2026-10-06');

-- PostgreSQL 18.4
ERROR:  new row for relation "memberships" violates check constraint "memberships_role_check"
DETAIL:  Failing row contains (10, 4, owner, 2026-10-06, 0).

-- MySQL 8.0.46
ERROR 3819 (HY000) at line 16: Check constraint 'memberships_chk_1' is violated.

属性が増えてきたら、その中間テーブルは「所属」「受講」「注文明細」のような1つの実体として扱うサインです。名前も関係を表す名詞に変え、必要ならサロゲートキーに切り替えます。

中間テーブルの命名：3つの流儀

列名は、参照先が分かる team_id、member_id の形にそろえます。同じテーブルを2回参照する関係（メンバーがメンバーをフォローする等）では、follower_id と followee_id のように役割を名前に入れます。どの流儀を選んでも、1つのデータベースの中で統一することが読みやすさにつながります。

集計で件数が増える例と直し方

1つのテーブルから、多対多の関係を2つ同時に JOIN すると、行が掛け算で増えます。メンバーにスキル（member_skills）の関係も足して確かめます。

CREATE TABLE skills (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL);
CREATE TABLE member_skills (
  member_id INT NOT NULL,
  skill_id  INT NOT NULL,
  PRIMARY KEY (member_id, skill_id),
  FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE,
  FOREIGN KEY (skill_id)  REFERENCES skills(id)  ON DELETE CASCADE
);
INSERT INTO skills (id, name) VALUES (1, 'SQL'), (2, 'Java'), (3, 'Python'), (4, 'テスト設計');
INSERT INTO member_skills (member_id, skill_id) VALUES (1, 1), (1, 2), (1, 3), (1, 4), (2, 1), (4, 4);

-- 件数が増える書き方
SELECT m.name, COUNT(tm.team_id) AS team_count, COUNT(ms.skill_id) AS skill_count
FROM members m
LEFT JOIN team_members  tm ON tm.member_id = m.id
LEFT JOIN member_skills ms ON ms.member_id = m.id
GROUP BY m.id, m.name ORDER BY m.id;

 name | team_count | skill_count 
------+------------+-------------
 佐藤 |         12 |          12
 鈴木 |          1 |           1
 高橋 |          1 |           0
 田中 |          1 |           1
(4 rows)

佐藤さんは 3 チーム・4 スキルですが、3 × 4 = 12 行に増えた後で数えているため、どちらも 12 になりました。鈴木さんのように片方が1件だと正しく見えるので、テストデータによっては気づきにくい点です。

-- 直し方1: COUNT(DISTINCT)
SELECT m.name, COUNT(DISTINCT tm.team_id) AS team_count, COUNT(DISTINCT ms.skill_id) AS skill_count
FROM members m
LEFT JOIN team_members  tm ON tm.member_id = m.id
LEFT JOIN member_skills ms ON ms.member_id = m.id
GROUP BY m.id, m.name ORDER BY m.id;

-- 直し方2: 先に集計してから結合
SELECT m.name, COALESCE(t.cnt, 0) AS team_count, COALESCE(s.cnt, 0) AS skill_count
FROM members m
LEFT JOIN (SELECT member_id, COUNT(*) AS cnt FROM team_members  GROUP BY member_id) t ON t.member_id = m.id
LEFT JOIN (SELECT member_id, COUNT(*) AS cnt FROM member_skills GROUP BY member_id) s ON s.member_id = m.id
ORDER BY m.id;

 name | team_count | skill_count 
------+------------+-------------
 佐藤 |          3 |           4
 鈴木 |          1 |           1
 高橋 |          1 |           0
 田中 |          1 |           1
(4 rows)

どちらの直し方も、両方のデータベースで同じ結果でした。件数だけなら COUNT(DISTINCT) で足りますが、合計（SUM）は DISTINCT では直せないので、先に集計してから結合する形が確実です。

1対多・配列型・JSON列で持つ方法との違い

「今は所属が1つだけ」という関係でも、後から複数になりそうなら最初から中間テーブルにしておくと、列の持ち替えが要りません。

配列型と JSON 列では、存在しないチーム 999 を入れても受け付けられました。

-- PostgreSQL 18.4
CREATE TABLE members_arr (
  id       int PRIMARY KEY,
  name     varchar(50) NOT NULL,
  team_ids int[] NOT NULL DEFAULT '{}'
);
INSERT INTO members_arr VALUES (1, '佐藤', '{10,20,30}'), (2, '鈴木', '{10}'), (3, '高橋', '{10,999}');
SELECT m.name, x.team_id, t.name AS team
FROM members_arr m CROSS JOIN unnest(m.team_ids) AS x(team_id)
LEFT JOIN teams t ON t.id = x.team_id
WHERE m.id = 3;
ALTER TABLE members_arr ADD FOREIGN KEY (team_ids) REFERENCES teams(id);

 name | team_id |    team    
------+---------+------------
 高橋 |      10 | 開発チーム
 高橋 |     999 | 
(2 rows)

ERROR:  foreign key constraint "members_arr_team_ids_fkey" cannot be implemented
DETAIL:  Key columns "team_ids" of the referencing table and "id" of the referenced table are of incompatible types: integer[] and integer.

-- MySQL 8.0.46
CREATE TABLE members_json (
  id       INT PRIMARY KEY,
  name     VARCHAR(50) NOT NULL,
  team_ids JSON NOT NULL
);
INSERT INTO members_json VALUES (1, '佐藤', '[10,20,30]'), (2, '鈴木', '[10]'), (3, '高橋', '[10,999]');
SELECT id, name FROM members_json WHERE 10 MEMBER OF (team_ids) ORDER BY id;

チーム 10 を含む3人（佐藤・鈴木・高橋）が返り、999 を含む行もそのまま登録されています。配列や JSON は「1行で読める」手軽さがある一方、相手の存在確認、役割や開始日といった属性の追加、チームを削除したときの後始末は自分で書くことになります。チームのようにマスタとして管理する相手との関係は、中間テーブルが向いています。

確かめて分かったこと

• PostgreSQL 18.4 では、外部キーを付けても逆向きのインデックスは作られませんでした。 member_id だけの検索は 50万行の全件読みになり、逆順インデックスの追加で 20.377 ms が 0.064 ms になりました。
• MySQL 8.0.46 は、外部キーの列に自動でインデックスを作りました。 外部キーを付けたテーブルでは、何も足さなくても逆向きの検索が 0.01 ms 台でした。
• MySQL は主キーだけでも、2番目の列の検索にスキップスキャンを使いました。 ただし所要時間は 15.1 ms で、専用のインデックスとは大きな差がありました。
• 2つの多対多を同時に JOIN した COUNT は、3 × 4 = 12 に増えました。 片方が1件のメンバーでは正しく見えます。
• サロゲートキーだけのテーブルは、同じ組の2件目をそのまま受け付けました。 UNIQUE の宣言が重複を防ぐ役目を持ちます。
• 配列型の列には外部キーを付けられず、存在しない id も登録できました。

よくある質問

Q. 中間テーブルとは？ A. 多対多の関係にある2つのテーブルの間に置き、両方の主キーを外部キーとして持つテーブルです。交差テーブル、関連テーブル、連関エンティティとも呼ばれます。

Q. 中間テーブルに id（連番）は必要？ A. つながりの有無だけを表すなら不要で、2つの外部キーの複合主キーで足ります。中間テーブルの行を他のテーブルが参照する、同じ組が複数回ありうる、単一列の主キーが前提の道具を使う、のいずれかなら id を置き、UNIQUE を必ず併せて付けます。

Q. 複合主キーの列の順番はどう決める？ A. よく絞り込みに使う側を先頭にします。反対側からも検索するなら、逆順のインデックスを1つ足します。

Q. 中間テーブルの名前の付け方は？ A. 関係を表す自然な名詞（memberships など）があればそれを使い、無ければ両テーブル名を連結します。語順と単数・複数の決まりを1つに統一します。

Q. JOIN して集計したら件数が合わないときは？ A. 多対多を2つ以上同時に JOIN して行が掛け算で増えている可能性が高いです。COUNT(DISTINCT) にするか、関係ごとに先に集計してから結合します。

まとめ

• 多対多は中間テーブルで表し、両方の列に外部キーを付ける。
• 関連だけなら複合主キー、参照される・同じ組が複数回ありうるならサロゲートキー＋UNIQUE。
• 複合主キーは先頭の列に効く。逆順インデックス (b_id, a_id) を足す。
• PostgreSQL は外部キーの列にインデックスを自動では作らない。MySQL（InnoDB）は作る。
• 属性は「その組に対して1つに決まる値」だけを置き、増えてきたら名前を関係の名詞に。
• 2つの多対多を同時に JOIN すると件数が増える。先に集計してから結合する。
• 配列型・JSON 列は外部キーが使えない。マスタとの関係は中間テーブルで持つ。

参考・出典

• PostgreSQL 18 Documentation「Multicolumn Indexes」 https://www.postgresql.org/docs/18/indexes-multicolumn.html
• MySQL 8.4 Reference Manual「Multiple-Column Indexes」 https://dev.mysql.com/doc/refman/8.4/en/multiple-column-indexes.html

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。