リレーションと正規化

主キーと外部キー

主キー（Primary Key）は、テーブル内の各レコードを一意に識別するカラムです。外部キー（Foreign Key）は、別のテーブルの主キーを参照するカラムで、 テーブル間の関連を定義します。

-- 主キー: id が各ユーザーを一意に識別
CREATE TABLE users (
  id    SERIAL PRIMARY KEY,  -- 主キー（自動採番）
  name  VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

-- 外部キー: user_id が users テーブルの id を参照
CREATE TABLE posts (
  id       SERIAL PRIMARY KEY,
  user_id  INTEGER NOT NULL,
  title    VARCHAR(200) NOT NULL,
  body     TEXT,

  -- 外部キー制約
  CONSTRAINT fk_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE CASCADE  -- ユーザー削除時に投稿も削除
);

リレーションの種類

テーブル間の関連には3つのパターンがあります。適切なリレーションを設計することがデータベース設計の要です。

-- ユーザーとプロフィール（1対1）
CREATE TABLE users (
  id   SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE profiles (
  id      SERIAL PRIMARY KEY,
  user_id INTEGER UNIQUE NOT NULL REFERENCES users(id), -- UNIQUE で1対1を保証
  bio     TEXT,
  avatar  VARCHAR(500)
);

-- ユーザーと投稿（1対多）
-- 1人のユーザーが複数の投稿を持てる
CREATE TABLE posts (
  id      SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id), -- 外部キー（UNIQUEなし）
  title   VARCHAR(200) NOT NULL
);

-- users(1) ←→ posts(多)
-- user_id = 1 のレコードが posts に複数存在できる

-- 投稿とタグ（多対多）
CREATE TABLE tags (
  id   SERIAL PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL
);

-- 中間テーブル（結合テーブル）
CREATE TABLE post_tags (
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  tag_id  INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)  -- 複合主キー
);

-- 投稿にタグを紐づける
INSERT INTO post_tags (post_id, tag_id) VALUES (1, 1), (1, 2), (2, 1);

-- 特定の投稿のタグ一覧
SELECT t.name
FROM tags t
INNER JOIN post_tags pt ON t.id = pt.tag_id
WHERE pt.post_id = 1;

正規化（Normalization）

正規化とは、データの重複を排除し、整合性を保つためにテーブルを分割する設計手法です。 一般的に第3正規形（3NF）までを目指します。

-- 悪い例（1NFに違反）: カンマ区切りで複数の値
| id | name     | phones                    |
|----|----------|---------------------------|
| 1  | 田中太郎 | 090-1111-2222,03-3333-4444|

-- 良い例（1NF準拠）: 1カラム1値
| id | name     | phone          |
|----|----------|----------------|
| 1  | 田中太郎 | 090-1111-2222  |
| 1  | 田中太郎 | 03-3333-4444   |

-- 悪い例: student_name は student_id だけで決まる
| student_id | course_id | student_name | grade |
|------------|-----------|--------------|-------|
| 1          | 101       | 田中太郎     | A     |

-- 良い例: テーブルを分割
-- students: | student_id | student_name |
-- grades:   | student_id | course_id | grade |

-- 悪い例: department_name は department_id に依存
| employee_id | department_id | department_name |
|-------------|---------------|-----------------|
| 1           | 10            | 開発部          |

-- 良い例: 部署テーブルを分離
-- employees:   | employee_id | department_id |
-- departments: | department_id | department_name |

ER図（Entity-Relationship Diagram）

ER図はテーブル間の関係を視覚的に表現する図です。 テーブル設計の前に ER 図を書くと、全体の構造を把握しやすくなります。

ブログシステムの ER 図（テキスト表現）

┌──────────────┐       ┌──────────────┐       ┌──────────────┐
│   users      │       │   posts      │       │   tags       │
├──────────────┤       ├──────────────┤       ├──────────────┤
│ id (PK)      │──1:N──│ id (PK)      │──N:N──│ id (PK)      │
│ name         │       │ user_id (FK) │       │ name         │
│ email        │       │ title        │       └──────────────┘
│ created_at   │       │ body         │              │
└──────────────┘       │ published    │       ┌──────────────┐
       │               │ created_at   │       │  post_tags   │
       │               └──────────────┘       ├──────────────┤
       │                      │               │ post_id (FK) │
       │               ┌──────────────┐       │ tag_id (FK)  │
       └──────1:1──────│  profiles    │       └──────────────┘
                       ├──────────────┤
                       │ id (PK)      │       ┌──────────────┐
                       │ user_id (FK) │       │  comments    │
                       │ bio          │       ├──────────────┤
                       │ avatar       │       │ id (PK)      │
                       └──────────────┘       │ post_id (FK) │
                                              │ user_id (FK) │
                                              │ body         │
                                              └──────────────┘

実務では dbdiagram.io や draw.io などのツールで ER 図を作成します。設計段階でチームメンバーと共有し、合意を取ることが重要です。

テーブル設計のベストプラクティス