第14章 多対多のテーブル設計 — 中間テーブル・自己参照・タグ・RBAC

この章で学ぶこと

• 多対多関係を中間テーブル (ジャンクションテーブル) で実装できる
• 中間テーブルに関係の属性を持たせる設計ができる
• 自己参照多対多 (フォロー機能) を実装できる
• タグシステムと RBAC (ロールベース権限管理) の典型パターンを適用できる
• トリガーで集計カラムを更新するリスクと代替案を判断できる

前提知識

第 10 章 キー概念 と 第 12 章 E-R 図 を理解していること。

多対多は中間テーブルで実装する

リレーショナルデータベースには「多対多」を直接表す仕組みはなく、中間テーブル (junction / bridge table) で実装します。中間テーブルは「2 つのテーブルへの外部キーをまとめて主キーにする」のが基本パターンです。

履修 (enrollments) の実装

学生と授業の多対多 (PostgreSQL)

CREATE TABLE students (
    id              SERIAL PRIMARY KEY,
    student_number  VARCHAR(20) UNIQUE NOT NULL,
    name            VARCHAR(100) NOT NULL,
    email           VARCHAR(255) UNIQUE NOT NULL,
    enrolled_at     DATE NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE courses (
    id            SERIAL PRIMARY KEY,
    course_code   VARCHAR(20) UNIQUE NOT NULL,
    name          VARCHAR(200) NOT NULL,
    credits       INTEGER NOT NULL CHECK (credits > 0),
    max_students  INTEGER NOT NULL DEFAULT 30
);

CREATE TABLE enrollments (
    student_id    INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
    course_id     INTEGER NOT NULL REFERENCES courses(id) ON DELETE RESTRICT,
    enrolled_at   DATE NOT NULL DEFAULT CURRENT_DATE,
    status        VARCHAR(20) NOT NULL DEFAULT 'enrolled'
                  CHECK (status IN ('enrolled', 'dropped', 'completed')),
    grade         CHAR(2) CHECK (grade IS NULL OR grade IN ('A+','A','B+','B','C+','C','D','F')),
    PRIMARY KEY (student_id, course_id)
);

CREATE INDEX idx_enrollments_course_status ON enrollments(course_id, status);

ポイント:

• 主キーは (student_id, course_id) の複合キー → 「同じ学生が同じ授業を二重履修できない」を制約で保証
• 中間テーブルに属性 (enrolled_at / status / grade) を持たせられる
• course_id への ON DELETE は RESTRICT (履修者がいる授業は削除できない)

属性を持つ多対多 — 商品とサプライヤー

中間テーブルは「関係そのものに付属する属性」を保管できます。同じ商品を複数サプライヤーから仕入れ、それぞれの単価・リードタイム・契約期間が違うようなケースです。

商品とサプライヤーの多対多 (PostgreSQL)

CREATE TABLE suppliers (
    id            SERIAL PRIMARY KEY,
    company_name  VARCHAR(200) NOT NULL,
    email         VARCHAR(255) NOT NULL,
    country       VARCHAR(100) NOT NULL,
    is_active     BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE product_suppliers (
    product_id          INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    supplier_id         INTEGER NOT NULL REFERENCES suppliers(id) ON DELETE RESTRICT,
    unit_cost           NUMERIC(10, 2) NOT NULL CHECK (unit_cost > 0),
    lead_time_days      INTEGER NOT NULL CHECK (lead_time_days >= 0),
    min_order_quantity  INTEGER NOT NULL DEFAULT 1 CHECK (min_order_quantity > 0),
    is_primary          BOOLEAN NOT NULL DEFAULT FALSE,
    contract_start      DATE NOT NULL,
    contract_end        DATE CHECK (contract_end IS NULL OR contract_end > contract_start),
    PRIMARY KEY (product_id, supplier_id)
);

CREATE INDEX idx_ps_primary  ON product_suppliers(product_id) WHERE is_primary = TRUE;

is_primary で「この商品の第一サプライヤー」を表現できますが、「商品ごとに is_primary = TRUE は 1 件まで」を制約で保証したい場合、トリガーや部分一意インデックスを使います。PostgreSQL では:

-- 商品ごとに is_primary = TRUE は最大 1 件
CREATE UNIQUE INDEX idx_ps_unique_primary
    ON product_suppliers(product_id)
    WHERE is_primary = TRUE;

このように部分一意インデックスで制約を表現できる場面はトリガーより安全 (デッドロックリスクなし) です。部分インデックス自体の仕組みは第 19 章で扱います。

自己参照の多対多 — フォロー機能

同じテーブル (users) 内の多対多も、中間テーブル (follows) で表現します。フォロワーとフォロー先の 2 つの外部キーは、両方とも users.id を参照する自己参照です。

フォロー関係 (PostgreSQL)

CREATE TABLE users (
    id              SERIAL PRIMARY KEY,
    username        VARCHAR(50) UNIQUE NOT NULL,
    display_name    VARCHAR(100) NOT NULL,
    bio             TEXT,
    is_verified     BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE follows (
    follower_id   INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    following_id  INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    followed_at   TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (follower_id, following_id),
    CHECK (follower_id <> following_id)        -- 自分自身はフォロー不可
);

CREATE INDEX idx_follows_following ON follows(following_id);

フォロワー数の集計 — トリガー vs 代替案

「ユーザーのプロフィールにフォロワー数を表示したい」というニーズに対し、いくつかの実装手段があります。

方法	メリット	デメリット
都度 COUNT(*) クエリ	シンプル / 常に正確	フォロワー多いユーザーで遅い
アプリ層キャッシュ (Redis 等)	高速・コントロール容易	整合性管理が必要
マテリアライズドビュー	DB 内で完結 / 定期 REFRESH	リアルタイム性が低い
DB トリガー + 集計カラム	リアルタイム / アプリ変更不要	ロック競合・デッドロックリスク

DB トリガーでの集計カラム更新は慎重に

AFTER INSERT/DELETE ON follows トリガーで users.follower_count を更新する設計は、見た目シンプルですがロック競合とデッドロックの温床になります (AWS PostgreSQL lock contention guide 参照)。

特に「人気ユーザー (= 多くの人がフォローを開始/解除する)」では、users の同じ行への更新が殺到します。現代の Web アプリでは:

• アプリ層 + Redis 集計: フォロー操作時に Redis の INCR / DECR で高速更新
• マテリアライズドビュー + 定期 REFRESH: 「ほぼリアルタイム」で十分なケース
• そもそも users.follower_count カラムを持たない: 必要時に SELECT COUNT(*) FROM follows WHERE following_id = ? (idx_follows_following で十分高速)

がより現実的です。トリガー方式は「中規模・更新頻度が低い」場面に限定するのが安全です (oneuptime denormalization patterns 2026-01 参照)。

タグシステム

ブログ記事 / 商品 / 動画 などに「タグ」を付けて分類するシステムも、典型的な多対多です。

タグシステム (PostgreSQL)

CREATE TABLE articles (
    id            BIGSERIAL PRIMARY KEY,
    title         VARCHAR(255) NOT NULL,
    slug          VARCHAR(255) UNIQUE NOT NULL,
    content       TEXT NOT NULL,
    author_id     INTEGER NOT NULL REFERENCES users(id),
    published_at  TIMESTAMPTZ,
    status        VARCHAR(20) NOT NULL DEFAULT 'draft'
                  CHECK (status IN ('draft', 'published', 'archived'))
);

CREATE TABLE tags (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(50) NOT NULL,
    slug  VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE article_tags (
    article_id  BIGINT NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    tag_id      INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    tagged_at   TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (article_id, tag_id)
);

CREATE INDEX idx_at_tag ON article_tags(tag_id);

タグ検索クエリの例

-- あるタグの記事一覧
SELECT a.*
FROM   articles a
JOIN   article_tags at ON at.article_id = a.id
WHERE  at.tag_id = (SELECT id FROM tags WHERE slug = 'database')
  AND  a.status = 'published'
ORDER BY a.published_at DESC;

-- 複数タグの AND 検索 (全てのタグを持つ記事)
SELECT a.*
FROM   articles a
JOIN   article_tags at ON at.article_id = a.id
WHERE  at.tag_id IN (SELECT id FROM tags WHERE slug IN ('database', 'beginner'))
  AND  a.status = 'published'
GROUP BY a.id
HAVING COUNT(DISTINCT at.tag_id) = 2;

RBAC (ロールベース権限管理)

実務でよく出会う「ユーザーに役割を持たせ、役割に権限を持たせる」パターンです。ユーザーは複数役割を持ち、役割は複数権限を持つ — どちらも多対多です。

RBAC (PostgreSQL)

CREATE TABLE roles (
    id           SERIAL PRIMARY KEY,
    name         VARCHAR(50) UNIQUE NOT NULL,
    description  TEXT
);

CREATE TABLE permissions (
    id        SERIAL PRIMARY KEY,
    resource  VARCHAR(50) NOT NULL,                  -- e.g., 'posts'
    action    VARCHAR(50) NOT NULL,                  -- e.g., 'create' / 'read' / 'update' / 'delete'
    UNIQUE (resource, action)
);

CREATE TABLE user_roles (
    user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id     INTEGER NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    granted_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    granted_by  INTEGER REFERENCES users(id),
    PRIMARY KEY (user_id, role_id)
);

CREATE TABLE role_permissions (
    role_id        INTEGER NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    permission_id  INTEGER NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
    PRIMARY KEY (role_id, permission_id)
);

権限チェックのクエリ

-- 「user_id = 1 が posts:create を実行できるか?」
SELECT EXISTS (
    SELECT 1
    FROM   user_roles ur
    JOIN   role_permissions rp ON rp.role_id = ur.role_id
    JOIN   permissions p       ON p.id = rp.permission_id
    WHERE  ur.user_id = 1
      AND  p.resource = 'posts'
      AND  p.action = 'create'
);

RBAC のキャッシュ戦略

権限チェックはリクエストごとに発生するため、毎回 DB に問い合わせると性能を圧迫します。

• セッション内キャッシュ: ログイン時にユーザーの全権限をロードしてメモリに保持
• Redis 等の分散キャッシュ: ユーザーごとの権限セットをキャッシュ、ロール変更時に該当ユーザーの cache を invalidate

キャッシュ無効化のタイミング設計を怠ると「権限剥奪したのに 30 分間操作できてしまう」事故になるため、ロール変更イベントと連動した invalidation が必須です。

権限管理のさらに高度なパターン (ABAC: 属性ベース) は本ガイドの範囲外ですが、業務システムでよく出会うため、興味があれば Casbin や OPA (Open Policy Agent) のようなポリシーエンジンを調べてみてください。

演習

演習 1: 多対多の設計

オンライン学習サイトを設計しています。次の関係を中間テーブルで表現してください。

• ユーザーは複数のコース (講座) を購入する
• コースは複数のユーザーに購入される
• 中間テーブルには購入日時・購入金額・進捗 (完了レッスン数) を持たせたい

解答例

ユーザー × コース (購入) の多対多

CREATE TABLE courses (
    id      SERIAL PRIMARY KEY,
    title   VARCHAR(200) NOT NULL,
    price   NUMERIC(10, 2) NOT NULL CHECK (price >= 0)
);

CREATE TABLE user_course_purchases (
    user_id              INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    course_id            INTEGER NOT NULL REFERENCES courses(id) ON DELETE RESTRICT,
    purchased_at         TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    purchase_amount      NUMERIC(10, 2) NOT NULL,     -- 購入時の金額をスナップショット
    completed_lessons    INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (user_id, course_id)
);

CREATE INDEX idx_ucp_user ON user_course_purchases(user_id, purchased_at DESC);

ポイント:

• 主キー (user_id, course_id) で「同じユーザーが同じコースを 2 回購入できない」を保証 (再受講も同じ行で completed_lessons を更新)
• purchase_amount はスナップショット (将来のコース値上げから購入履歴を守る)
• ON DELETE RESTRICT: 購入履歴がある間はユーザー・コースを物理削除させない

ありがちな間違い: id SERIAL PRIMARY KEY を加えて複合キーをやめる。これだと「同じユーザーが同じコースを 100 回購入」できてしまう。再購入を禁止したいなら複合主キーが正解。

演習 2: 集計戦略の判断

SNS のフォロー機能で「ユーザーのフォロワー数を表示する」必要があります。次の状況それぞれで、適切な集計戦略を選んでください。

1. 月間 100 万ユーザーのサービス、フォロー操作は秒間 100 件、表示は秒間 5000 件
2. 社内向けの 50 人サービス、フォロー操作は 1 日 10 件、表示は 1 日 1000 件
3. 100 万ユーザー、フォロワー数は「だいたい合っていればよく、5 分ズレても問題ない」表示

解答例

ケース	推奨戦略	理由
1. 大規模・高頻度	Redis カウンター (アプリ層で INCR/DECR)	表示が秒間 5000 件で、COUNT(*) クエリでは性能不足。トリガー方式はロック競合リスク
2. 小規模	都度 COUNT(*)	SELECT COUNT(*) FROM follows WHERE following_id = ? で十分高速 (インデックスあれば数ミリ秒)。シンプルが正解
3. 大規模・ニアリアルタイム可	マテリアライズドビュー + 5 分ごと REFRESH	リアルタイム不要なら DB 内完結が運用シンプル

ありがちな間違い:

1. 「とりあえずトリガー」: 性能は良いが、人気ユーザー (= 多くのフォロー操作が集中する) でロック競合が起き、最悪デッドロックで一部操作が失敗する
2. 「キャッシュなしで COUNT(*)」を大規模で採用: 1 ユーザーあたり数十ミリ秒でも、秒間 5000 件なら DB 負荷が膨大
3. 「Redis にすればいつでも正確」: Redis とDB の同期失敗時 (Redis 再起動など) に整合性が崩れる。復旧時の reconcile (COUNT(*) で再計算) を仕込んでおく必要あり

判断軸の整理:

• スケール (ユーザー数・操作頻度・表示頻度)
• リアルタイム性の要求
• 障害復旧の容易さ

まとめ

この章で学んだことを整理します。

• 多対多は 中間テーブル で実装し、主キーは複合 (2 つの外部キーをまとめる) にする
• 中間テーブルに関係の属性 (履修日 / 単価 / 契約期間) を持たせられる
• 自己参照多対多は同じテーブルへの 2 つの外部キーで表現 (CHECK 制約で自分参照を禁止)
• タグシステム・RBAC は多対多の典型パターン
• 集計カラムを トリガーで自動更新 する方式はロック競合リスクあり、現代ではアプリ層 / Redis / マテリアライズドビューが主流
• 部分一意インデックス (UNIQUE INDEX ... WHERE) はトリガーの代わりに使える便利機能 (PostgreSQL)

次章では、認証認可システムの DB 設計を扱います。

次の章

第 15 章 認証・認可システムの設計