データベース設計

Q: 質問：テーブルを分割すべき場合と、大きなテーブルのままにするべき場合の判断基準は？

回答： 一部のフィールドが他よりはるかに頻繁に更新される場合（例：ユーザ基本情報 vs ログインログ）は分割し、フィールドが頻繁に一緒にクエリされる場合は1つのテーブルにまとめます。

Q: 質問：論理削除と物理削除のどちらを選ぶべき？

回答： 財務や注文などのコアデータには論理削除（is_deleted）を使用し、ログやキャッシュなどの補助データには物理削除を使用します。

Q: 質問：JSONフィールドはどのような場面で適している？

回答： 構造が一貫しておらず、クエリ頻度が低い拡張属性（ユーザー設定や商品仕様など）の格納に適しています。インデックスクエリが必要なフィールドをJSONに入れないでください。

Q: 質問：オートインクリメントIDとUUIDのどちらが良い？

回答： シングルサーバー環境ではオートインクリメントID（順序付き、コンパクト）を優先し、分散システム（グローバルに一意）ではUUIDまたはSnowflakeアルゴリズムを検討してください。

🎯 たとえ話

図書館を開くところを想像してください：

要件分析：まずどの本を貸し出すか、誰が借りるか、どう登録するかを把握する → ビジネス要件を理解する
ER図：「読者 — 貸出 — 本」の関係図を描く → エンティティ関係モデリング
正規化：読者の住所を貸出カードに繰り返し記載せず、読者ファイルにのみ保管する → 冗長性の排除
非正規化：人気書籍ランキングを計算して壁に直接貼り出す。毎回貸出記録を検索するのではなく → スペースと時間のトレードオフ
命名規則：統一された書棚番号形式「A-01」を使い、時には「棚1」、時には「列1」にしない → 統一された命名

📚 コアコンセプト

1. 要件分析

SQLを書く前に、以下の質問に答えましょう：

質問	例
システムが管理するエンティティは？	ユーザー、記事、コメント、タグ
エンティティ間の関係は？	1人のユーザーが複数の記事を書く（1対多）
サポートすべきクエリは？	タグで検索、時間でソート
推定データ量は？	記事数百万件、コメント数千万件
読み取り集中か書き込み集中か？	ブログシステムは読み取り集中

2. ER図（エンティティ関係図）

ER図は3つのコア要素でデータモデルを記述します：

TEXT

[エンティティ] —— 属性1, 属性2, 属性3
   |
  (リレーションシップ)  カーディナリティ: 1:1, 1:N, M:N
   |
[エンティティ] —— 属性1, 属性2

一般的なリレーションシップタイプ：

リレーションシップ	例	実装方法
1対1 (1:1)	ユーザー ↔ ユーザー詳細	外部キーまたは1つのテーブルに統合
1対多 (1:N)	ユーザー → 記事	記事テーブルに `user_id` 外部キーを追加
多対多 (M:N)	記事 ↔ タグ	ジャンクションテーブル `article_tag`

3. 正規化理論

第一正規形（1NF）：列はアトミックでなければならない

SQL

-- ❌ 1NF違反：phone列に複数の値を格納
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    phones VARCHAR(200)  -- '138xxx,139xxx,137xxx'
);

-- ✅ 1NF準拠：各フィールドに単一のアトミックな値を格納
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
CREATE TABLE user_phones (
    id INT PRIMARY KEY,
    user_id INT,
    phone VARCHAR(20)
);

第二正規形（2NF）：非キー列は主キー全体に完全依存しなければならない

SQL

-- ❌ 2NF違反：注文項目において、product_nameはproduct_idのみに依存し、order_idには依存しない
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),  -- 部分依存
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- ✅ 2NF準2NF準拠：productsテーブルに分割
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

第三正規形（3NF）：非キー列に推移的依存があってはならない

SQL

-- ❌ 3NF違反：city_nameはcity_idに依存、city_idはidに依存 → 推移的依存
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    city_id INT,
    city_name VARCHAR(50)  -- 推移的依存
);

-- ✅ 3NF準3NF準拠：citiesテーブルに分割
CREATE TABLE cities (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    city_id INT
);

BCNF（Boyce-Codd正規形）

3NFより厳格：すべての決定因子が候補キーでなければならない。実際には、3NFを満たせば通常十分です。

4. 非正規化

クエリパフォーマンスのために、意図的に冗長性を導入することがあります：

SQL

-- 記事テーブルにコメント数を冗長に格納し、毎回のCOUNTを回避
ALTER TABLE articles ADD COLUMN comment_count INT DEFAULT 0;

-- 冗長フィールドを更新
UPDATE articles SET comment_count = (
    SELECT COUNT(*) FROM comments WHERE comments.article_id = articles.id
);

非正規化すべき場面：

説読み取りが書き込みを大幅に上回る
集計クエリが非常に頻繁に行われる
インデックス最適化だけではパフォーマンス要件を満たせない

5. テーブルリレーションシップ設計

主キーの選択肢：

タイプ	メリット	デメリット	ユースケース
オートインクリメント `AUTO_INCREMENT`	順序付き、コンパクト、高速な挿入	予測可能、シャーディングの問題	ほとんどのビジネステーブル
UUID	グローバルに一意、予測不能	サイズが大きい、順序なしインデックスが遅い	分散システム
複合主キー	セマンティックに明確	外部キー参照が煩雑	ジャンクションテーブル、関連テーブル
Snowflake ID	順序付き、グローバルに一意	追加の依存関係が必要	分散・高並行

SQL

-- オートインクリメント主キー
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

-- UUID主キー
CREATE TABLE users (
    id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    name VARCHAR(50)
);

-- 複合主キー（ジャンクションテーブル）
CREATE TABLE article_tag (
    article_id BIGINT,
    tag_id BIGINT,
    PRIMARY KEY (article_id, tag_id)
);

6. フィールド命名規則

規則	✅ 推奨	❌ 避けるべき
小文字＋アンダースコアを使用	`user_name`	`UserName`, `username`
テーブル名は複数形	`users`	`user`, `tbl_user`
外部キーフィールド名	`user_id`	`uid`, `userId`
論理フィールド	`is_deleted`	`deleted`, `flag`
タイムスタンプフィールド	`created_at`	`createTime`, `add_time`
金額フィールド	`DECIMAL(10,2)`	`FLOAT`, `DOUBLE`

7. 設計のベストプラクティス

すべてのテーブルには主キーが必要
外部キー列にインデックスを追加（データベースが外部キー制約を強制しなくても）
最小限の十分なデータ型を選択：ステータス値には INT ではなく TINYINT
金額には DECIMAL を使用、浮動小数点型は使わない
時刻フィールドには DATETIME または TIMESTAMP を使用、文字列は使わない
拡張フィールドを予約：extra JSON またはビット演算による status
論理削除を物理削除の代わりに：is_deleted TINYINT DEFAULT 0

💡 基本構文

SQL

-- テーブル作成の基本テンプレート
CREATE TABLE table_name (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主キー',
    -- ビジネスフィールド
    name VARCHAR(100) NOT NULL COMMENT '名前',
    status TINYINT NOT NULL DEFAULT 1 COMMENT 'ステータス: 1-有効 0-無効',
    -- 外部キーフィールド
    user_id BIGINT NOT NULL COMMENT 'ユーザーID',
    -- タイムスタンプフィールド
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成日時',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日時',
    -- インデックス
    INDEX idx_user_id (user_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='テーブルコメント';

💡 ヒント：設計に1時間余計に費やすことで、後の10時間のリファクタリングを節約できます。まず紙にER図を描いてからSQLを書きましょう。

例：簡単な学生履修管理システムの設計（難易度⭐）

要件：学生、科目、履修記録を管理する。

SQL

-- 学生テーブル
CREATE TABLE students (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '学生ID',
    student_no VARCHAR(20) NOT NULL UNIQUE COMMENT '学籍番号',
    name VARCHAR(50) NOT NULL COMMENT '氏名',
    gender TINYINT NOT NULL DEFAULT 0 COMMENT '性別: 0-不明 1-男性 2-女性',
    enrollment_year INT NOT NULL COMMENT '入学年度',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生テーブル';

-- 科目テーブル
CREATE TABLE courses (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '科目ID',
    course_no VARCHAR(20) NOT NULL UNIQUE COMMENT '科目番号',
    name VARCHAR(100) NOT NULL COMMENT '科目名',
    credit DECIMAL(3,1) NOT NULL COMMENT '単位数',
    teacher VARCHAR(50) COMMENT '担当講師',
    max_students INT NOT NULL DEFAULT 60 COMMENT '履修上限人数',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='科目テーブル';

-- 履修テーブル（ジャンクションテーブル、多対多リレーションシップ）
CREATE TABLE enrollments (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    student_id BIGINT NOT NULL COMMENT '学生ID',
    course_id BIGINT NOT NULL COMMENT '科目ID',
    score DECIMAL(5,2) COMMENT '成績',
    enrolled_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '履修日時',
    UNIQUE KEY uk_student_course (student_id, course_id),
    INDEX idx_course_id (course_id),
    INDEX idx_student_id (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='履修テーブル';

▶ 試してみよう

例：ECサイト注文システムのコアテーブル設計（難易度⭐⭐）

SQL

-- 商品カテゴリテーブル
CREATE TABLE categories (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL COMMENT 'カテゴリ名',
    parent_id BIGINT DEFAULT NULL COMMENT '親カテゴリID、NULLはトップレベル',
    sort_order INT NOT NULL DEFAULT 0 COMMENT 'ソート順',
    INDEX idx_parent_id (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品カテゴリテーブル';

-- 商品テーブル
CREATE TABLE products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    category_id BIGINT NOT NULL COMMENT 'カテゴリID',
    name VARCHAR(200) NOT NULL COMMENT '商品名',
    price DECIMAL(10,2) NOT NULL COMMENT '販売価格',
    stock INT NOT NULL DEFAULT 0 COMMENT '在庫数',
    status TINYINT NOT NULL DEFAULT 1 COMMENT 'ステータス: 1-出品中 0-非出品',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category_id),
    INDEX idx_status_price (status, price)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品テーブル';

-- 注文テーブル（非正規化：配送先住所のスナップショットを冗長格納）
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL UNIQUE COMMENT '注文番号',
    user_id BIGINT NOT NULL COMMENT 'ユーザーID',
    total_amount DECIMAL(12,2) NOT NULL COMMENT '注文合計金額',
    status TINYINT NOT NULL DEFAULT 0 COMMENT 'ステータス: 0-未払い 1-支払済 2-発送済 3-完了 4-キャンセル',
    receiver_name VARCHAR(50) NOT NULL COMMENT '受取人名',
    receiver_phone VARCHAR(20) NOT NULL COMMENT '受取人電話番号',
    receiver_address VARCHAR(500) NOT NULL COMMENT '配送先住所',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    paid_at DATETIME COMMENT '支払日時',
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='注文テーブル';

-- 注文項目テーブル
CREATE TABLE order_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT NOT NULL COMMENT '注文ID',
    product_id BIGINT NOT NULL COMMENT '商品ID',
    product_name VARCHAR(200) NOT NULL COMMENT '商品名（スナップショット）',
    product_price DECIMAL(10,2) NOT NULL COMMENT '単価（スナップショット）',
    quantity INT NOT NULL COMMENT '数量',
    subtotal DECIMAL(12,2) NOT NULL COMMENT '小計',
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='注文項目テーブル';

▶ 試してみよう

設計のポイント：

注文テーブルには配送先住所を冗長に格納（注文時のスナップショット）。ユーザーが後から住所を変更しても過去の注文に影響しない
注文項目テーブルには商品名と価格を冗長に格納。商品価格が変更されても既存の注文に影響しない
金額フィールドは統一して DECIMAL(12,2) を使用
ステータスフィールドは TINYINT を使用し、各値の意味をコメントで説明

🏢 シナリオ1：ブログシステムのデータベース設計

要件：ユーザー登録、記事執筆、タグ付け、コメント投稿をサポートする。

SQL

-- ユーザーテーブル
CREATE TABLE blog_users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    avatar_url VARCHAR(500),
    bio TEXT,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ブログユーザー テーブル';

-- 記事テーブル
CREATE TABLE blog_articles (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    category_id BIGINT,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    status TINYINT NOT NULL DEFAULT 0 COMMENT '0-下書き 1-公開 2-非公開',
    view_count INT NOT NULL DEFAULT 0,
    like_count INT NOT NULL DEFAULT 0,
    published_at DATETIME,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_category (category_id),
    INDEX idx_status_published (status, published_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='記事テーブル';

-- タグテーブル
CREATE TABLE blog_tags (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='タグテーブル';

-- 記事-タグジャンクションテーブル
CREATE TABLE blog_article_tag (
    article_id BIGINT NOT NULL,
    tag_id BIGINT NOT NULL,
    PRIMARY KEY (article_id, tag_id),
    INDEX idx_tag_id (tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='記事-タグ関連テーブル';

-- コメントテーブル（ネストコメント対応）
CREATE TABLE blog_comments (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    article_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    parent_id BIGINT COMMENT '親コメントID、NULLはトップレベルコメント',
    content TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_article_id (article_id),
    INDEX idx_parent_id (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='コメントテーブル';

🏢 シナリオ2：マルチテナントSaaSシステム設計

要件：単一のシステムで複数の企業クライアントにサービスを提供し、データ分離を実現する。

SQL

-- テナントテーブル
CREATE TABLE tenants (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT '会社名',
    plan VARCHAR(20) NOT NULL DEFAULT 'free' COMMENT 'プラン: free/basic/pro',
    max_users INT NOT NULL DEFAULT 10,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='テナントテーブル';

-- ユーザーテーブル（各レコードは1つのテナントに属する）
CREATE TABLE tenant_users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL COMMENT 'テナントID',
    username VARCHAR(50) NOT NULL,
    role VARCHAR(20) NOT NULL DEFAULT 'member',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_tenant_username (tenant_id, username),
    INDEX idx_tenant_id (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='テナント ユーザーテーブル';

-- すべてのビジネステーブルにtenant_idフィールドを含める
CREATE TABLE tenant_orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant_id (tenant_id),
    INDEX idx_tenant_user (tenant_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='テナント注文テーブル';

💡 ポイント：すべてのSQLクエリには WHERE tenant_id = ? 条件を含め、データ漏洩を防止する必要があります。

❓ よくある質問

質問：テーブルを分割すべき場合と、大きなテーブルのままにするべき場合の判断基準は？ 回答： 一部のフィールドが他よりはるかに頻繁に更新される場合（例：ユーザ基本情報 vs ログインログ）は分割し、フィールドが頻繁に一緒にクエリされる場合は1つのテーブルにまとめます。

質問：論理削除と物理削除のどちらを選ぶべき？ 回答： 財務や注文などのコアデータには論理削除（is_deleted）を使用し、ログやキャッシュなどの補助データには物理削除を使用します。

質問：JSONフィールドはどのような場面で適している？ 回答： 構造が一貫しておらず、クエリ頻度が低い拡張属性（ユーザー設定や商品仕様など）の格納に適しています。インデックスクエリが必要なフィールドをJSONに入れないでください。

質問：オートインクリメントIDとUUIDのどちらが良い？ 回答： シングルサーバー環境ではオートインクリメントID（順序付き、コンパクト）を優先し、分散システム（グローバルに一意）ではUUIDまたはSnowflakeアルゴリズムを検討してください。

📖 まとめ

このレッスンでは、データベース設計方法論を体系的に学びました：

要件分析は設計の出発点であり、エンティティ、リレーションシップ、クエリパターンを明確にします
ER図は1対1、1対多、多対多のリレーションシップを視覚化するのに役立ちます
正規化（1NF→2NF→3NF→BCNF）はデータの冗長性を段階的に排除します
非正規化はパフォーマンスボトルネック時に適度な冗長性を導入します
主キーの選択はオートインクリメント、UUID、複合キーの長所と短所を比較検討する必要があります
命名規則はチーム協業の一貫性を確保します

📝 演習

オンライン試験システムのデータベースを設計してください。ユーザー、試験問題、問題、選択肢、回答記録、成績を含みます。ER図を描き、CREATE TABLE文を記述してください。
次のテーブルを正規化してください：orders(order_id, customer_name, customer_phone, product_name, product_price, quantity)。少なくとも3NFまで分割してください。
あなたのプロジェクトに適した主キー戦略（オートインクリメント vs UUID）を選択し、その理由を説明してください。

次のレッスン →26-sql-injection.md

Web-Tutorial 技術チーム

複数の開発者によって共同維持されているプログラミングチュートリアルプラットフォーム。各チュートリアルは専門分野の開発者が執筆・レビューしています。正確で信頼性の高いコンテンツを目指しています — 問題を見つけた場合はお知らせください。

私たちについて