【初心者向け】データベース設計の基礎 #6 - 実践的なテーブル設計パターン集📚

1. はじめに

前回は、トランザクションとACID特性について学びました。
データの整合性を守る仕組みが理解できましたね!🔒

今回は、もっと実践的な内容です!💪

「多対多のリレーションって、どう実装するの?🤔」
「履歴データはどう管理すればいいの?」
「論理削除と物理削除、どっちを使うべき?」
「階層構造(カテゴリのツリーとか)はどう表現する?」

こんな疑問に答えるのが、今回のテーマであるテーブル設計パターン集です!✨

実務でよく使うパターンを、具体例を交えて解説します!

1.1 前回のおさらい📝

• トランザクション - 複数の処理を1つの単位に
• ACID特性 - 原子性、一貫性、独立性、永続性
• 分離レベル - READ COMMITTED、REPEATABLE READ など
• ロック - 共有ロック、排他ロック

今回は、これらの知識を活かして、実践的なテーブル設計を学びます!

2. パターン1：多対多リレーション(Many-to-Many)🔗

2.1 問題：生徒と授業の関係

シナリオ

- 1人の生徒は、複数の授業を受けられる
- 1つの授業は、複数の生徒が受けられる

田中くん:数学、英語、理科を受講
佐藤さん:英語、理科を受講
鈴木くん:数学、理科を受講

数学:田中くん、鈴木くんが受講
英語:田中くん、佐藤さんが受講
理科:田中くん、佐藤さん、鈴木くんが受講

これが多対多の関係です!

2.2 ❌ 悪い設計：配列で持つ

-- 生徒テーブル
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    course_ids VARCHAR(100)  -- 'math,english,science' のように保存
);

-- 授業テーブル
CREATE TABLE courses (
    course_id VARCHAR(20) PRIMARY KEY,
    course_name VARCHAR(50),
    student_ids VARCHAR(100)  -- '1,2,3' のように保存
);

問題点:

• カンマ区切りの値は検索できない!🔍❌
• 第1正規形に違反!📋❌
• データの整合性が保てない!💥

2.3 ✅ 正しい設計：中間テーブルを作る

テーブル構造

-- 生徒テーブル
CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 授業テーブル
CREATE TABLE courses (
    course_id VARCHAR(20) PRIMARY KEY,
    course_name VARCHAR(50) NOT NULL,
    teacher VARCHAR(50),
    credits INT
);

-- 中間テーブル(履修テーブル)
CREATE TABLE enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id VARCHAR(20) NOT NULL,
    enrolled_date DATE DEFAULT (CURRENT_DATE),
    grade VARCHAR(2),  -- 'A', 'B', 'C' など
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    UNIQUE (student_id, course_id)  -- 同じ授業に2回登録できない
);

ER図

　（生徒）　　　　　　　　（履修）　　　　　　（授業）
students ───── enrollments ───── courses
      1   対   多        多  対   1

データの例

studentsテーブル

student_id	student_name
1	田中太郎
2	佐藤花子
3	鈴木一郎

coursesテーブル

course_id	course_name	teacher
math	数学	山田先生
english	英語	高橋先生
science	理科	伊藤先生

enrollmentsテーブル(中間テーブル)

enrollment_id	student_id	course_id	enrolled_date	grade
1	1	math	2026-04-01	A
2	1	english	2026-04-01	B
3	1	science	2026-04-01	A
4	2	english	2026-04-01	A
5	2	science	2026-04-01	B
6	3	math	2026-04-01	B
7	3	science	2026-04-01	A

よく使うクエリ

1. 田中くんが受講している授業を取得

SELECT 
    c.course_name,
    c.teacher,
    e.grade
FROM students s
INNER JOIN enrollments e ON s.student_id = e.student_id
INNER JOIN courses c ON e.course_id = c.course_id
WHERE s.student_name = '田中太郎';

結果:

course_name	teacher	grade
数学	山田先生	A
英語	高橋先生	B
理科	伊藤先生	A

2. 数学を受講している生徒を取得

SELECT 
    s.student_name,
    e.grade
FROM courses c
INNER JOIN enrollments e ON c.course_id = e.course_id
INNER JOIN students s ON e.student_id = s.student_id
WHERE c.course_name = '数学';

結果:

student_name	grade
田中太郎	A
鈴木一郎	B

3. 各授業の受講者数を集計

SELECT 
    c.course_name,
    COUNT(e.student_id) AS student_count
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.course_id, c.course_name;

結果:

course_name	student_count
数学	2
英語	2
理科	3

2.4 中間テーブルに追加情報を持たせる💡

中間テーブルには、リレーション固有の情報を持たせることができます!

CREATE TABLE enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id VARCHAR(20) NOT NULL,
    enrolled_date DATE,  -- 履修登録日📅
    grade VARCHAR(2),  -- 成績🎓
    attendance_rate DECIMAL(5,2),  -- 出席率(%)📊
    final_score INT,  -- 期末試験の点数✍️
    passed BOOLEAN,  -- 合格/不合格✅❌
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

ポイント:

• enrolled_date(履修登録日)は、生徒でも授業でもなく、履修という関係に属する情報!
• gradeも同様!

2.5 実例：ECサイトの商品とカテゴリ🛒

-- 商品テーブル
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

-- カテゴリテーブル
CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL
);

-- 中間テーブル
CREATE TABLE product_categories (
    product_id INT,
    category_id INT,
    PRIMARY KEY (product_id, category_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

1つの商品が複数のカテゴリに属する例:

iPhone 15:
  - スマートフォン
  - 家電
  - Apple製品

MacBook:
  - ノートPC
  - 家電
  - Apple製品

3. パターン2：履歴データの管理📜

3.1 問題：価格の変更履歴を記録したい

シナリオ

商品の価格が頻繁に変わる:
  2026-01-01: 10,000円
  2026-02-01: 9,000円(セール!)
  2026-03-01: 10,500円(値上げ)

過去の注文を見たとき、その時点の価格を知りたい!💰

3.2 ❌ 悪い設計：商品テーブルの価格を直接更新

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2)  -- これを直接更新してしまう
);

-- 価格変更
UPDATE products SET price = 9000 WHERE product_id = 1;

-- 問題:過去の価格がわからない!😱

3.3 ✅ 設計1：履歴テーブルを分ける

テーブル構造

-- 商品テーブル
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    description TEXT,
    current_price DECIMAL(10,2) NOT NULL  -- 現在の価格
);

-- 価格履歴テーブル
CREATE TABLE price_history (
    history_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE,  -- NULLの場合は現在も有効
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    INDEX idx_product_date (product_id, start_date)
);

データの例

productsテーブル

product_id	product_name	current_price
1	iPhone 15	10500

price_historyテーブル

history_id	product_id	price	start_date	end_date
1	1	10000	2026-01-01	2026-01-31
2	1	9000	2026-02-01	2026-02-29
3	1	10500	2026-03-01	NULL

end_date が NULL = 現在も有効! ✅

特定日時の価格を取得

-- 2026-02-15時点の価格を取得
SELECT price 
FROM price_history
WHERE product_id = 1
  AND start_date <= '2026-02-15'
  AND (end_date IS NULL OR end_date >= '2026-02-15');

結果: 9,000円 ✅

価格変更の実装

BEGIN;

-- 1. 現在の履歴の終了日を設定
UPDATE price_history 
SET end_date = '2026-02-29'
WHERE product_id = 1 AND end_date IS NULL;

-- 2. 新しい履歴を追加
INSERT INTO price_history (product_id, price, start_date, end_date)
VALUES (1, 10500, '2026-03-01', NULL);

-- 3. 商品テーブルの現在価格も更新
UPDATE products 
SET current_price = 10500 
WHERE product_id = 1;

COMMIT;

3.4 ✅ 設計2:注文時の価格を記録(スナップショット)📸

考え方

「注文時点の価格」を注文テーブルに保存する!

-- 注文明細テーブル
CREATE TABLE order_items (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,  -- 注文時点の単価を保存!✨
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

なぜこうするの?🤔

商品の価格:10,000円
  ↓
田中さんが注文(unit_price: 10,000円を保存)📝
  ↓
商品の価格が9,000円に変更
  ↓
佐藤さんが注文(unit_price: 9,000円を保存)📝
  ↓
後で田中さんの注文を見ても、
「あの時は10,000円で買った」とわかる!✅

メリット:

• 注文履歴から直接価格がわかる!
• JOIN不要で高速!🚀
• シンプル!

デメリット:

• データの重複(非正規化)
• 価格履歴の全体像は別途管理が必要

どちらを使うべき?⚖️

用途	推奨設計
注文システム	スナップショット方式(unit_priceを保存)
価格の変動分析	履歴テーブル方式
両方必要	両方を実装!💪

4. パターン3:論理削除 vs 物理削除🗑️

4.1 問題:ユーザーを削除したい

シナリオ

ユーザーが退会したい!

でも...
- 注文履歴は残したい📦
- 統計データに使いたい📊
- 後で復元できるようにしたい🔄

4.2 物理削除(Physical Delete)💥

データベースから完全に消す!

DELETE FROM users WHERE user_id = 1;

-- 完全に消える!復元不可能!💀

メリット:

• ディスク容量を節約💾
• シンプル

デメリット:

• 復元できない💀
• 外部キー制約で削除できないことも
• 監査(誰がいつ削除したか)が難しい

4.3 論理削除(Logical Delete)🏷️

削除フラグを立てるだけ、実際には消さない!

テーブル設計

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    is_deleted BOOLEAN DEFAULT FALSE,  -- 削除フラグ🏷️
    deleted_at TIMESTAMP NULL,  -- 削除日時
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

論理削除の実装

-- 「削除」する
UPDATE users 
SET is_deleted = TRUE, 
    deleted_at = CURRENT_TIMESTAMP 
WHERE user_id = 1;

-- データは残ってる!✅

アクティブなユーザーだけ取得

-- 削除されていないユーザーだけ
SELECT * FROM users WHERE is_deleted = FALSE;

-- または
SELECT * FROM users WHERE deleted_at IS NULL;

復元も可能!🔄

-- 復元
UPDATE users 
SET is_deleted = FALSE, 
    deleted_at = NULL 
WHERE user_id = 1;

-- 復活!🎉

4.4 ✅ 論理削除のベストプラクティス

パターン1：シンプルな論理削除

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(255),
    deleted_at TIMESTAMP NULL,  -- これだけでOK!
    
    INDEX idx_deleted_at (deleted_at)  -- インデックスも忘れずに!
);

-- アクティブなユーザー
SELECT * FROM users WHERE deleted_at IS NULL;

-- 削除されたユーザー
SELECT * FROM users WHERE deleted_at IS NOT NULL;

メリット:

• カラム1つで済む
• 削除日時もわかる

パターン2：詳細な論理削除

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(255),
    is_deleted BOOLEAN DEFAULT FALSE,
    deleted_at TIMESTAMP NULL,
    deleted_by INT,  -- 誰が削除したか
    delete_reason TEXT,  -- 削除理由
    FOREIGN KEY (deleted_by) REFERENCES users(user_id)
);

監査がしっかりできる! 📋

パターン3：ビュー(View)で隠蔽

-- 論理削除を隠蔽するビューを作成
CREATE VIEW active_users AS
SELECT 
    user_id,
    username,
    email,
    created_at
FROM users
WHERE deleted_at IS NULL;

-- アプリケーションからは、ビューを使う
SELECT * FROM active_users;

-- 削除されたユーザーは見えない!✅

アプリケーション側でWHERE句を書き忘れても安全! 🛡️

4.5 論理削除の注意点⚠️

1. ユニーク制約の問題

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,  -- 問題!💥
    deleted_at TIMESTAMP NULL
);

-- 田中さんが退会(論理削除)
UPDATE users SET deleted_at = NOW() WHERE email = '[e-mail]';

-- 新しい田中さんが登録しようとする
INSERT INTO users (email) VALUES ('[e-mail]');
-- エラー!UNIQUE制約違反!😱

解決策:

-- ユニーク制約を削除して、アプリケーション側でチェック
-- または、メールアドレスに削除IDを付ける

UPDATE users 
SET email = CONCAT(email, '_deleted_', user_id),
    deleted_at = NOW()
WHERE user_id = 1;

-- '[e-mail]' → '[e-mail]_deleted_1'

2. パフォーマンスの問題

-- 削除されたユーザーが増えると、WHERE句が遅くなる
SELECT * FROM users WHERE deleted_at IS NULL;

-- インデックスを作成!
CREATE INDEX idx_deleted_at ON users(deleted_at);

-- または、複合インデックス
CREATE INDEX idx_deleted_id ON users(deleted_at, user_id);

3. 外部キー制約との相性

-- 物理削除
DELETE FROM users WHERE user_id = 1;
-- エラー!ordersテーブルが参照している!💥

-- 論理削除
UPDATE users SET deleted_at = NOW() WHERE user_id = 1;
-- 成功!外部キー制約に引っかからない!✅

4.6 どちらを使うべき?⚖️

データの種類	推奨	理由
ユーザー	論理削除✅	復元、監査が必要
注文	論理削除✅	履歴として保存
一時データ	物理削除✅	不要になったら消す
ログ	物理削除✅	古いログは削除
マスタデータ	論理削除✅	参照整合性を保つ

**迷ったら論理削除!**💡

---

5. パターン4:階層構造(ツリー構造)🌳

5.1 問題：カテゴリの親子関係

シナリオ

カテゴリがツリー構造になっている:

家電
├── テレビ
│   ├── 液晶テレビ
│   └── 有機ELテレビ
├── 冷蔵庫
└── 洗濯機

ファッション
├── メンズ
│   ├── トップス
│   └── ボトムス
└── レディース
    ├── トップス
    └── ボトムス

5.2 ✅ 設計1：隣接リストモデル(Adjacency List)

最もシンプル!親のIDを持つ!

テーブル構造

CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL,
    parent_id INT NULL,  -- 親カテゴリのID(ルートはNULL)
    display_order INT DEFAULT 0,
    FOREIGN KEY (parent_id) REFERENCES categories(category_id),
    INDEX idx_parent (parent_id)
);

データの例

category_id	category_name	parent_id	display_order
1	家電	NULL	1
2	テレビ	1	1
3	液晶テレビ	2	1
4	有機ELテレビ	2	2
5	冷蔵庫	1	2
6	洗濯機	1	3
7	ファッション	NULL	2
8	メンズ	7	1
9	トップス	8	1
10	ボトムス	8	2

よく使うクエリ

1. ルートカテゴリを取得

SELECT * FROM categories WHERE parent_id IS NULL;

結果:

category_id	category_name
1	家電
7	ファッション

2. 「家電」の直下の子を取得

SELECT * FROM categories WHERE parent_id = 1;

結果:

category_id	category_name
2	テレビ
5	冷蔵庫
6	洗濯機

3. 「テレビ」のパンくずリストを取得

再帰的なクエリが必要!

-- MySQL 8.0+、PostgreSQL
WITH RECURSIVE breadcrumb AS (
    -- 開始点:「液晶テレビ」
    SELECT category_id, category_name, parent_id, 1 AS level
    FROM categories
    WHERE category_id = 3
    
    UNION ALL
    
    -- 再帰:親をたどる
    SELECT c.category_id, c.category_name, c.parent_id, b.level + 1
    FROM categories c
    INNER JOIN breadcrumb b ON c.category_id = b.parent_id
)
SELECT category_name 
FROM breadcrumb 
ORDER BY level DESC;

結果:

category_name
家電
テレビ
液晶テレビ

メリット:

• シンプル!✅
• 親子関係がわかりやすい
• ノードの追加・削除が簡単

デメリット:

• 深い階層の取得が遅い🐢
• 再帰クエリが必要

5.3 ✅ 設計2：パスエンコードモデル(Path Enumeration)

パス全体を文字列で保存!

テーブル構造

CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL,
    path VARCHAR(255) NOT NULL,  -- '/1/2/3/' のように保存
    level INT NOT NULL,  -- 階層の深さ
    UNIQUE (path),
    INDEX idx_path (path)
);

データの例

category_id	category_name	path	level
1	家電	/1/	1
2	テレビ	/1/2/	2
3	液晶テレビ	/1/2/3/	3
4	有機ELテレビ	/1/2/4/	3
5	冷蔵庫	/1/5/	2
6	洗濯機	/1/6/	2

よく使うクエリ

1. 「家電」配下の全カテゴリを取得

SELECT * 
FROM categories 
WHERE path LIKE '/1/%'
ORDER BY path;

結果:

category_name	path
家電	/1/
テレビ	/1/2/
液晶テレビ	/1/2/3/
有機ELテレビ	/1/2/4/
冷蔵庫	/1/5/
洗濯機	/1/6/

一発で取れる!速い!🚀

2. パンくずリストを取得

-- 「液晶テレビ」(path='/1/2/3/')のパンくずリスト
SELECT c.*
FROM categories c
WHERE '/1/2/3/' LIKE CONCAT(c.path, '%')
ORDER BY c.level;

結果:

category_name	level
家電	1
テレビ	2
液晶テレビ	3

これも一発! 🚀

メリット:

• 子孫の取得が超高速!⚡
• パンくずリストも簡単
• 再帰不要!

デメリット:

• ノードの移動が大変(パスを全部更新)
• パスの長さに制限がある

5.4 ✅ 設計3:入れ子集合モデル(Nested Set)

左右の番号で階層を表現!

テーブル構造

CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL,
    lft INT NOT NULL,  -- 左の番号
    rgt INT NOT NULL,  -- 右の番号
    UNIQUE (lft),
    UNIQUE (rgt),
    INDEX idx_lft_rgt (lft, rgt)
);

データの例

家電(1,12)
├── テレビ(2,7)
│   ├── 液晶テレビ(3,4)
│   └── 有機ELテレビ(5,6)
├── 冷蔵庫(8,9)
└── 洗濯機(10,11)

category_id	category_name	lft	rgt
1	家電	1	12
2	テレビ	2	7
3	液晶テレビ	3	4
4	有機ELテレビ	5	6
5	冷蔵庫	8	9
6	洗濯機	10	11

仕組み

ノードを包含する数値で表現:

家電(1,12)の中に、テレビ(2,7)が含まれる
  ↓
1 < 2 < 7 < 12 なので、
テレビは家電の子!✅

よく使うクエリ

「家電」配下の全カテゴリ

SELECT child.*
FROM categories parent
INNER JOIN categories child 
    ON child.lft BETWEEN parent.lft AND parent.rgt
WHERE parent.category_name = '家電'
ORDER BY child.lft;

超高速! ⚡⚡

メリット:

• 子孫の取得が最速!⚡⚡
• 階層の深さに関係なく高速

デメリット:

• 理解が難しい😵
• ノードの追加・削除が複雑
• 番号の振り直しが必要

どの設計を使うべき?⚖️

設計	適している用途	速度	実装の簡単さ
隣接リスト	親子関係がメイン	普通🏃	簡単✅
パスエンコード	検索が多い	速い⚡	普通
入れ子集合	読み取り専用に近い	最速⚡⚡	難しい😵

初心者は隣接リストから始めよう! 💡

---

6. パターン5:ポリモーフィック関連(Polymorphic Association)🔀

6.1 問題:いいね!機能

シナリオ

「いいね!」をつけられるものが複数ある:

- 投稿(posts)にいいね!
- コメント(comments)にいいね!
- 写真(photos)にいいね!

全部同じlikesテーブルで管理したい!

6.2 ❌ 悪い設計:全部のIDを持つ

CREATE TABLE likes (
    like_id INT PRIMARY KEY,
    user_id INT,
    post_id INT NULL,
    comment_id INT NULL,
    photo_id INT NULL
);

-- 投稿にいいね
INSERT INTO likes (user_id, post_id) VALUES (1, 100);

-- コメントにいいね
INSERT INTO likes (user_id, comment_id) VALUES (1, 50);

問題:

• カラムがどんどん増える📏
• NULLだらけ
• 外部キー制約が使えない❌

6.3 ✅ 正しい設計：ポリモーフィック関連

テーブル構造

CREATE TABLE likes (
    like_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    likeable_type VARCHAR(50) NOT NULL,  -- 'post', 'comment', 'photo'
    likeable_id INT NOT NULL,  -- 対象のID
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    UNIQUE (user_id, likeable_type, likeable_id),  -- 二重いいね防止
    INDEX idx_likeable (likeable_type, likeable_id)
);

データの例

like_id	user_id	likeable_type	likeable_id	created_at
1	1	post	100	2026-02-01
2	1	comment	50	2026-02-02
3	2	post	100	2026-02-03
4	2	photo	25	2026-02-04

いいね!する

-- 投稿100にいいね!
INSERT INTO likes (user_id, likeable_type, likeable_id)
VALUES (1, 'post', 100);

-- コメント50にいいね!
INSERT INTO likes (user_id, likeable_type, likeable_id)
VALUES (1, 'comment', 50);

投稿100のいいね数を取得

SELECT COUNT(*) AS like_count
FROM likes
WHERE likeable_type = 'post' AND likeable_id = 100;

田中さんがいいね!した投稿一覧

SELECT p.*
FROM posts p
INNER JOIN likes l 
    ON l.likeable_id = p.post_id 
    AND l.likeable_type = 'post'
WHERE l.user_id = 1;

##　6.4 注意点⚠️

外部キー制約が使えない!

-- これはできない!💥
FOREIGN KEY (likeable_id) REFERENCES posts(post_id)
-- likeable_typeによって参照先が変わるため!

解決策:

1. アプリケーション側でチェック
2. トリガーで整合性をチェック
3. または、テーブルごとに分ける(likesを使わず、post_likes、comment_likesなど)

6.5 どちらを使うべき?⚖️

ケース	推奨設計
対象が2-3種類	ポリモーフィック関連
対象が5種類以上	ポリモーフィック関連
外部キー必須	テーブルを分ける
統計・集計が重要	テーブルを分ける

7. パターン6:監査ログ(Audit Log)📋

問題:誰がいつ何を変更したか記録したい

シナリオ

ユーザー情報が変更された!
- 誰が?
- いつ?
- 何を?
- 変更前の値は?
- 変更後の値は?

これを全部記録したい!

7.1 ✅ 設計:監査ログテーブル

テーブル構造

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    status VARCHAR(20),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 監査ログテーブル
CREATE TABLE audit_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(50) NOT NULL,  -- 'users'
    record_id INT NOT NULL,  -- user_id
    action VARCHAR(10) NOT NULL,  -- 'INSERT', 'UPDATE', 'DELETE'
    field_name VARCHAR(50),  -- 'email'
    old_value TEXT,  -- 変更前
    new_value TEXT,  -- 変更後
    changed_by INT,  -- 誰が変更したか
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_table_record (table_name, record_id),
    INDEX idx_changed_at (changed_at)
);

データの例

log_id	table_name	record_id	action	field_name	old_value	new_value	changed_by	changed_at
1	users	1	INSERT	NULL	NULL	NULL	1	2026-01-01 10:00
2	users	1	UPDATE	email	[e-mail]	[e-mail]	1	2026-02-01 15:30
3	users	1	UPDATE	status	active	suspended	2	2026-02-10 09:15
4	users	1	DELETE	NULL	NULL	NULL	2	2026-03-01 16:00

トリガーで自動記録

-- UPDATEトリガー
DELIMITER //

CREATE TRIGGER users_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    -- emailが変更された場合
    IF OLD.email != NEW.email THEN
        INSERT INTO audit_logs (table_name, record_id, action, field_name, old_value, new_value, changed_by)
        VALUES ('users', NEW.user_id, 'UPDATE', 'email', OLD.email, NEW.email, @current_user_id);
    END IF;
    
    -- statusが変更された場合
    IF OLD.status != NEW.status THEN
        INSERT INTO audit_logs (table_name, record_id, action, field_name, old_value, new_value, changed_by)
        VALUES ('users', NEW.user_id, 'UPDATE', 'status', OLD.status, NEW.status, @current_user_id);
    END IF;
END//

DELIMITER ;

使用例

-- 現在のユーザーIDを設定
SET @current_user_id = 1;

-- メールアドレスを変更
UPDATE users 
SET email = '[e-mail]' 
WHERE user_id = 1;

-- 自動的にaudit_logsに記録される!✨

ユーザー1の変更履歴を取得

SELECT 
    log_id,
    action,
    field_name,
    old_value,
    new_value,
    changed_at
FROM audit_logs
WHERE table_name = 'users' AND record_id = 1
ORDER BY changed_at DESC;

7.4 簡易版:変更日時と変更者だけ記録

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by INT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updated_by INT,
    FOREIGN KEY (created_by) REFERENCES users(user_id),
    FOREIGN KEY (updated_by) REFERENCES users(user_id)
);

**最低限これだけは入れておこう!**✅

---

8. まとめ🎓

お疲れ様でした!🎉
実践的なテーブル設計パターンを学びましたね!

8.1 今回学んだパターン📚

1. 多対多リレーション🔗

• 中間テーブルを作成
• リレーション固有の情報を持たせる
• 例:生徒と授業、商品とカテゴリ

2. 履歴データ📜

• 履歴テーブルで時系列管理
• スナップショット方式で注文時の情報を保存
• 例:価格履歴、注文履歴

3. 論理削除🗑️

• deleted_atフラグで「削除」を表現
• 復元可能
• 監査に便利
• 例:ユーザーの退会、商品の削除

4. 階層構造🌳

• 隣接リスト:シンプル
• パスエンコード:検索が速い
• 入れ子集合:最速
• 例:カテゴリのツリー、組織図

5. ポリモーフィック関連🔀

• likeable_type + likeable_idで複数テーブルを参照
• 柔軟だが外部キー制約が使えない
• 例:いいね機能、コメント機能

6. 監査ログ📋

• 誰が、いつ、何を変更したか記録
• トリガーで自動化
• コンプライアンスに必須
• 例:ユーザー情報の変更履歴

8.2 実務での使い方💼

1. 要件定義:どの機能が必要か洗い出す
     ↓
2. パターン選択:適切な設計パターンを選ぶ
     ↓
3. 実装:テーブルを作成、テストデータを投入
     ↓
4. テスト:クエリのパフォーマンスを確認
     ↓
5. 運用:必要に応じて改善

8.3 設計のコツ💡

✅ シンプルから始める - 複雑な設計は必要になってから
✅ パフォーマンスを測定 - 推測ではなく実測
✅ 将来の拡張を考える - でも過度な汎用化は避ける
✅ チームで共有 - 設計パターンをドキュメント化

8.4 次回予告🚀

次回はよくあるアンチパターンと対策について学びます!

「やってはいけないテーブル設計とは?」
「このパターンはなぜダメなの?」
「よくある失敗とその修正方法」

現場でよく見る「やりがちな失敗」を学んで、
良い設計者になりましょう!💪

お楽しみに!👋

---

データベース設計基礎シリーズ

1. データベース設計の基本概念(ER図、エンティティ)
2. SQLの基本をマスターしよう
3. 正規化入門(第1〜第3正規形)
4. インデックスの仕組みと使い方
5. トランザクションとACID特性
6. 実践的なテーブル設計パターン集 ← 今回✅
7. よくあるアンチパターンと対策 ← 次回

💬 質問や感想があれば、コメント欄でお気軽にどうぞ!
👍 役に立ったら、いいね&ストックをお願いします!

それでは、また次回!🙌