1つの表に全部入れると壊れる — テーブル設計と正規化

テーブル設計と正規化。大きな表から分かれた小さな表と関連を示す接続線。

1つの表に全部入れると、更新・挿入・削除で異常が起きます。わざと壊れる表を作って、正規化が何を解決するのかを見ます。

前回: SQLiteはおもちゃではない — 用途で選ぶデータベース 作業場所はVMの中(Ubuntu Server)です。MySQL に appdb があり、tasks テーブルと ~/server の Knex マイグレーションが動いている状態から始めます。

最初に、順番について正直に書きます。

テーブル設計の話は、本当ならもっと早い段階でやるものです。 MySQLの回で CREATE TABLE を打った、あの直後が「正しい」位置です。

それでもここまで引っ張りました。 理由は1つです。

困っていない人に「正規化」を説明しても、暗記にしかならないからです。

「第1正規形は繰り返しを無くす、第2正規形は部分関数従属を無くす、第3正規形は推移的関数従属を無くす」—— この3行を先に読んでも、何ひとつ手は動きません。 私は実際に、この3行を何度読んでも自分の表をどう直せばいいのか分かりませんでした。

いまなら違います。あなたはもう、

この3つを見たあとの「正規化」は、暗記ではなく修理になります。

なので、この記事では番号を先に出しません。 まずわざと壊れる表を1枚作って、実際に壊します。 壊れたところを直していったら、結果として第3正規形になっていた——という順番で進めます。番号との対応表は、いちばん最後に置きます。

対象レベル: 初級〜中級 / 区分: 必修

目次

この記事でわかること

  • 1つの表に全部入れると、同じ情報が何行にも重複すること。そして重複そのものより、重複が食い違うことが問題だということ
  • 「異常」には3種類あること。直したのに直っていない(更新時)/関係ないデータが無いと入れられない(挿入時)/消したら別の情報まで消える(削除時)
  • 正規化は「重複を消す作業」であって、番号を当てはめる作業ではないこと。第3正規形まででほぼ足りること
  • 外部キーは「繋ぐもの」ではなく「壊れた繋がりを拒否するもの」だということ。ON DELETE の選択が事故を防ぐこと
  • 主キーを連番にするかUUIDにするかで、索引の太り方が変わること
  • 正規化しすぎると JOIN が増えて遅くなること。あえて重複を残す判断は、測ってからしかできないこと
  • NULL は「無い」ではないこと。「空」「未定」「該当なし」を1つの記号で兼ねると、あとで区別できなくなること

この記事でやること

  1. わざと壊れる表を1枚作って、そこに現実的なデータを入れる
  2. SQLを打って、3つの異常を実際に起こす。 食い違った状態を目で見る
  3. 重複を外に出して表を分ける。 外部キーで繋ぎ直し、ON DELETE を3種類とも試す
  4. 主キーを連番とUUIDの2通りで作って、索引の大きさを比べる
  5. 正規化しすぎた形を EXPLAIN で測り、あえて重複を戻す判断をする
  6. できあがった設計を Knex のマイグレーションに落とす

先に知る言葉

この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。

言葉意味
正規化同じ情報が2箇所以上に書かれている状態を、1箇所にまとめていく作業。 表を分けることになる
異常(アノマリー)重複したせいで起きる不都合。 この記事では更新時・挿入時・削除時の3つを扱う
主キー1行を1つに特定するための列。 重複できず、空にできない
外部キー「この列の値は、あっちの表に必ず存在する」という決まり。 無い値を入れようとすると拒否される
ON DELETE参照先を消したとき、参照している側をどうするかの指定。拒否する/一緒に消す/空にする
候補キー主キーになれる資格のある列(の組)。メールアドレスのように「重複しない」列も候補キー
関数従属「Aが決まればBも決まる」という関係。 社員番号が決まれば氏名も決まる、など
推移的関数従属A→B→C と2段階で決まってしまっている状態。Cは本来Bの表に置くべき
非正規化速さのために、あえて重複を残す(戻す)こと。正規化の失敗ではなく、測ったうえでの判断
ER図箱(表)と線(関係)だけで設計を描いた図。 Entity-Relationship の略
カーディナリティ関係の個数の向き。1対1 / 1対多 / 多対多
中間テーブル多対多を表すために挟む表。 「どのタスクにどのタグが付いているか」だけを持つ
UUIDどこで作っても重複しないことになっている36文字の識別子。 550e8400-... の形

この記事を通して言いたいのは、たった1文です。 「同じことを2箇所に書くと、いつか片方だけ古くなる」。 正規化は、その「いつか」を構造的に起こせなくする作業です。

① わざと壊れる表を作る

説明を読むより、壊れるものを持っているほうが早いので、先に作ります。

この記事で作る表は、すべて d_ で始めます。 d は draft(下書き)のつもりです。既存の tasks や users には一切触りません。 最後に d_ で始まる表をまとめて消せば、元通りになります。

VMの中で MySQL に入ります。

bash
$ sudo mysql

「タスク管理アプリを作っていて、担当者とプロジェクトとタグも持ちたくなった」という、ごく普通の状況を想定します。何も考えずに列を足していくと、こうなります。

USE appdb;

CREATE TABLE d_flat (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  title          VARCHAR(200) NOT NULL,
  done           BOOLEAN NOT NULL DEFAULT FALSE,
  assignee_name  VARCHAR(100),
  assignee_email VARCHAR(255),
  project_name   VARCHAR(100),
  project_due    DATE,
  tags           VARCHAR(255)
);

この表は、動きます。 アプリからも普通に読めますし、JOIN も要りません。むしろ最初は、こちらのほうが書きやすいくらいです。

データを入れます。

INSERT INTO d_flat (title, assignee_name, assignee_email, project_name, project_due, tags) VALUES
('ログイン画面を作る',   '佐藤', 'sato@example.com',   'サイト移行', '2026-10-31', 'ui,api'),
('APIの仕様を書く',       '佐藤', 'sato@example.com',   'サイト移行', '2026-10-31', 'api'),
('DNSを切り替える',       '鈴木', 'suzuki@example.com', 'サイト移行', '2026-10-31', 'infra'),
('バックアップを取る',     '鈴木', 'suzuki@example.com', '運用改善',   '2026-11-30', 'infra,api-docs');

中身を見ます。

SELECT id, title, assignee_name, assignee_email, project_name, project_due FROM d_flat;
+----+--------------------+---------------+----------------------+--------------+-------------+
| id | title              | assignee_name | assignee_email       | project_name | project_due |
+----+--------------------+---------------+----------------------+--------------+-------------+
|  1 | ログイン画面を作る   | 佐藤          | sato@example.com     | サイト移行    | 2026-10-31  |
|  2 | APIの仕様を書く      | 佐藤          | sato@example.com     | サイト移行    | 2026-10-31  |
|  3 | DNSを切り替える      | 鈴木          | suzuki@example.com   | サイト移行    | 2026-10-31  |
|  4 | バックアップを取る    | 鈴木          | suzuki@example.com   | 運用改善      | 2026-11-30  |
+----+--------------------+---------------+----------------------+--------------+-------------+

ここで、目で見て気づくことがあります。

  • sato@example.com が2回書いてある
  • サイト移行 / 2026-10-31 が3回書いてある

タスクが4件でこれです。 タスクが1000件になれば、佐藤さんのメールアドレスは数百箇所に書かれます。

ここで多くの入門記事は「無駄だから消しましょう」と言います。 その説明では弱いです。 ディスクは安いので、「無駄」だけなら我慢すればいい話です。

本当の問題は、無駄ではありません。

同じ情報が何箇所にもあると、「全部を同時に直す」ことに失敗したときに、どれが正しいのか誰にも分からなくなります。

これを実際に起こします。

② 3つの異常を、実際に起こす

ここがこの記事の山場です。 読み飛ばさずに、手で打って結果を見てください。

異常その1 — 直したのに直っていない(更新時異常)

佐藤さんがメールアドレスを変えました。 アプリの「担当者の編集」画面から更新した——つもりの SQL がこれです。

UPDATE d_flat SET assignee_email = 'sato.new@example.com' WHERE id = 1;

1行だけ更新されます。 それはそのとおりです。問題は、この表には佐藤さんの行が2つあることです。

SELECT DISTINCT assignee_name, assignee_email FROM d_flat ORDER BY assignee_name;
+---------------+----------------------+
| assignee_name | assignee_email       |
+---------------+----------------------+
| 佐藤          | sato.new@example.com |
| 佐藤          | sato@example.com     |
| 鈴木          | suzuki@example.com   |
+---------------+----------------------+

[スクリーンショット: 01_更新時異常.png] 上の UPDATE と、その直後の SELECT DISTINCT を実行して、「佐藤」が2つのメールアドレスを持っている状態が写るように撮ってください。

佐藤さんが2人になりました。 データベースは何のエラーも出していません。制約に違反していないからです。

そして、ここからが本当に困るところです。

どちらが正しいのか、データベースに聞いても分かりません。

「新しいほう」と言いたくなりますが、この表には「いつ更新したか」も入っていません。 アプリ側で「タスク一覧の担当者メール」を表示すれば、同じ佐藤さんが、行によって違うアドレスで表示されます。

これが更新時異常です。 「1箇所だけ直してしまった」のではなく、「1箇所だけ直せてしまう構造になっている」のが問題です。

WHERE assignee_name = '佐藤' にすれば全部直る、と思いましたか。 それは「アプリが毎回正しく書けば大丈夫」という話で、NoSQLの回で見た「スキーマがアプリ側に移る」と同じ形です。 人が忘れられる場所に、正しさを置かない。 これが今回のテーマです。

戻しておきます。

UPDATE d_flat SET assignee_email = 'sato@example.com' WHERE assignee_name = '佐藤';

異常その2 — 関係ないデータが無いと入れられない(挿入時異常)

新しいプロジェクト「監視の導入」が決まりました。 締切は 2026年12月20日。タスクはまだ1件も決まっていません。

この表に、プロジェクトだけを登録しようとします。

INSERT INTO d_flat (project_name, project_due) VALUES ('監視の導入', '2026-12-20');
ERROR 1364 (HY000): Field 'title' doesn't have a default value

入りません。 title が NOT NULL だからです。

ここで「じゃあ title を NULL 可にすればいい」と考えたなら、それが罠です。 そうすると今度は、「タイトルの無いタスク」がタスク一覧に出てきます。

現場でよく起きる回避策がこれです。

INSERT INTO d_flat (title, project_name, project_due) VALUES ('(仮)', '監視の導入', '2026-12-20');

入りました。 そしてタスク一覧に「(仮)」という幽霊タスクが1件増えました。

SELECT id, title, project_name FROM d_flat WHERE title = '(仮)';

これが挿入時異常です。

プロジェクトを登録したいだけなのに、タスクを1件でっちあげないと登録できない。

原因は明確です。 この表は「タスク」の表なのに、「プロジェクト」の情報も持たされているからです。プロジェクトは、タスクとは独立して存在できるものです。独立して存在できるものは、独立した表にするべきです。

幽霊を消しておきます。

DELETE FROM d_flat WHERE title = '(仮)';

異常その3 — 消したら別の情報まで消える(削除時異常)

「運用改善」プロジェクトのタスクは、いま1件だけです(バックアップを取る)。 これが終わったので消します。

消す前に、必ず件数を見ます。

SELECT COUNT(*) FROM d_flat WHERE title = 'バックアップを取る';
DELETE FROM d_flat WHERE title = 'バックアップを取る';

タスクを1件消しただけのつもりです。 ところが。

SELECT DISTINCT project_name, project_due FROM d_flat;
+--------------+-------------+
| project_name | project_due |
+--------------+-------------+
| サイト移行    | 2026-10-31  |
+--------------+-------------+

「運用改善」というプロジェクトが、締切ごと消滅しました。

誰も消していません。 プロジェクトの情報が、タスクの行にしか存在していなかったからです。最後の1件を消した瞬間に、道連れになりました。

これが削除時異常です。 そして3つの中でいちばん怖いです。理由は、

エラーが出ないうえに、消えたことに気づくのが数か月後だからです。

更新時異常は「食い違い」として残ります。 削除時異常は、痕跡ごと消えます。

3つをまとめる

異常何が起きるか根本の原因
更新時異常1箇所だけ直せてしまい、同じものが食い違う同じ情報が複数の行に書かれている
挿入時異常関係ないデータをでっちあげないと登録できない独立して存在できるものが、別のものの表に同居している
削除時異常1件消したら、別の情報まで消えるその情報の置き場所が、そこしか無い

この3つは、全部「同じ1つの原因」から出ています。

1つの表に、2種類以上の「もの」を入れたから。

d_flat には「タスク」「担当者」「プロジェクト」「タグ」の4つが入っています。 だから4つ分の事故が起きます。

③ 直す — 番号ではなく「いま何をしているか」で

ここから直します。 正規形の番号は、まだ出しません。 やっていることだけを書きます。

直し1 — 1つのマスに2つ以上入れるのをやめる

まず tags を見てください。

SELECT id, title, tags FROM d_flat;
+----+--------------------+----------------+
| id | title              | tags           |
+----+--------------------+----------------+
|  1 | ログイン画面を作る   | ui,api         |
|  2 | APIの仕様を書く      | api            |
|  3 | DNSを切り替える      | infra          |
+----+--------------------+----------------+

1つのマスに、カンマ区切りで複数の値が入っています。 これをやると、SQLが使えなくなります。

「api タグの付いたタスクを探す」をやってみます。 api-docs というタグを付けた行は ② で消してしまったので、1件入れ直してから試します。

INSERT INTO d_flat (title, assignee_name, assignee_email, project_name, project_due, tags)
VALUES ('手順書を書く', '鈴木', 'suzuki@example.com', '運用改善', '2026-11-30', 'api-docs');

SELECT id, title, tags FROM d_flat WHERE tags LIKE '%api%';
+----+--------------------+----------------+
| id | title              | tags           |
+----+--------------------+----------------+
|  1 | ログイン画面を作る   | ui,api         |
|  2 | APIの仕様を書く      | api            |
|  6 | 手順書を書く         | api-docs       |
+----+--------------------+----------------+

api-docs が混ざりました。 別のタグなのに、文字列として含まれてしまうからです。

しかも、LIKE '%...%' は索引が効きません。 パフォーマンスの回でやったとおり、前が % で始まる検索は、必ず全件を読みます。

EXPLAIN SELECT id FROM d_flat WHERE tags LIKE '%api%';

type が ALL になっているはずです。 索引を張っても変わりません。

「部分一致を速くしたい」という話に見えますが、違います。 これは検索の問題ではなく、設計の問題です。 本当に部分一致で探したいものは全文検索の回の領域ですが、タグは部分一致で探すものではありません。

やることは1つです。

1つのマスには、1つの値しか入れない。 複数入れたいなら、行を増やす(=別の表にする)。

タグは「1つのタスクに複数」かつ「1つのタグが複数のタスクに」——多対多です。多対多は、間に表を1枚挟みます。

直し2 — 「そのもの自身の情報」を、そのものの表に移す

次に、assignee_email を見ます。

d_flat の主キーは id(タスクのID)です。 そこで、この表の各列が「本当にタスクの情報か」を1つずつ問います。

列これは何の情報か
titleタスクの情報。 タスクが決まればタイトルが決まる
doneタスクの情報
assignee_nameタスクの情報(誰が担当か、はタスクごとに決まる)
assignee_email違う。 これは「佐藤さん」が決まれば決まる。タスクは関係ない
project_nameタスクの情報(どのプロジェクトか)
project_due違う。 これは「サイト移行」が決まれば決まる。タスクは関係ない

assignee_email と project_due だけ、性質が違います。

タスクID → 担当者名 → メールアドレス タスクID → プロジェクト名 → 締切

2段階で決まっています。 これを推移的関数従属と呼びます。名前は覚えなくていいので、判定のしかたを覚えてください。

「この列は、主キーではなく、同じ行の別の列によって決まっていないか?」

決まっているなら、その列は「決めているほう」の表に移します。

直し3 — 中間テーブルの列を疑う

タグの中間テーブルを作るとき、こういう書き方をしたくなります。

-- ⚠️ これは作りません。悪い例です
CREATE TABLE d_bad_task_tags (
  task_id  INT NOT NULL,
  tag_id   INT NOT NULL,
  tag_name VARCHAR(50) NOT NULL,
  PRIMARY KEY (task_id, tag_id)
);

主キーが (task_id, tag_id) の2列組です。 そこに tag_name を持たせました。「毎回 JOIN しなくて済むから」という理由で、これは本当によく書かれます。

でも tag_name は、tag_id だけで決まります。 task_id は関係ありません。 つまり主キーの一部にしか依存していない——これを部分関数従属と呼びます。

何が起きるかは、もう分かるはずです。

タグ名を api から backend に改名したとき、この中間テーブルの全行を書き換えないと食い違います。

更新時異常が、そのまま再発します。 だから中間テーブルには、繋ぐための2列だけを置きます。

④ 表を分けて、外部キーで繋ぐ

直し1〜3をそのまま形にします。

CREATE TABLE d_members (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE d_projects (
  id     INT AUTO_INCREMENT PRIMARY KEY,
  name   VARCHAR(100) NOT NULL UNIQUE,
  due_on DATE NULL
);

CREATE TABLE d_tags (
  id   INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE d_tasks (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  title      VARCHAR(200) NOT NULL,
  done       BOOLEAN NOT NULL DEFAULT FALSE,
  project_id INT NULL,
  member_id  INT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_task_project FOREIGN KEY (project_id)
    REFERENCES d_projects(id) ON DELETE SET NULL,
  CONSTRAINT fk_task_member FOREIGN KEY (member_id)
    REFERENCES d_members(id) ON DELETE RESTRICT
);

CREATE TABLE d_task_tags (
  task_id INT NOT NULL,
  tag_id  INT NOT NULL,
  PRIMARY KEY (task_id, tag_id),
  CONSTRAINT fk_tt_task FOREIGN KEY (task_id)
    REFERENCES d_tasks(id) ON DELETE CASCADE,
  CONSTRAINT fk_tt_tag FOREIGN KEY (tag_id)
    REFERENCES d_tags(id) ON DELETE RESTRICT
);

表が1枚から5枚になりました。 増えたことに不安を感じるのが普通です。 ⑦でその不安に答えます。

データを入れます。

INSERT INTO d_members (name, email) VALUES
('佐藤', 'sato@example.com'), ('鈴木', 'suzuki@example.com');

INSERT INTO d_projects (name, due_on) VALUES
('サイト移行', '2026-10-31'), ('運用改善', '2026-11-30'), ('監視の導入', '2026-12-20');

INSERT INTO d_tags (name) VALUES ('ui'), ('api'), ('infra'), ('api-docs');

INSERT INTO d_tasks (title, project_id, member_id) VALUES
('ログイン画面を作る', 1, 1),
('APIの仕様を書く',   1, 1),
('DNSを切り替える',   1, 2),
('手順書を書く',      2, 2);

INSERT INTO d_task_tags (task_id, tag_id) VALUES
(1,1), (1,2), (2,2), (3,3), (4,4);

さっきの3つの異常が、どうなったか確かめます。

更新時異常 —— 佐藤さんのメールを変えます。

UPDATE d_members SET email = 'sato.new@example.com' WHERE id = 1;
SELECT t.title, m.name, m.email FROM d_tasks t JOIN d_members m ON t.member_id = m.id;

佐藤さんの担当タスクは、全部いっぺんに新しいアドレスになります。 1箇所しか無いからです。 「全部直す」を頑張ったのではなく、「1箇所だけ直す」が不可能になりました。

挿入時異常 —— タスクの無いプロジェクト。

SELECT * FROM d_projects WHERE name = '監視の導入';

もう入っています。 タスクをでっちあげる必要はありませんでした。

削除時異常 —— 「運用改善」の最後のタスクを消します。

DELETE FROM d_tasks WHERE title = '手順書を書く';
SELECT * FROM d_projects WHERE name = '運用改善';

プロジェクトは残っています。 消したのはタスクだけです。

3つとも、構造で消えました。 アプリ側は何も書いていません。

ON DELETE の選択が、事故を防ぐ

上の CREATE TABLE で、ON DELETE を3種類とも使い分けました。 ここが外部キーのいちばん重要な部分です。

書き方参照先を消そうとしたとき使いどころ
ON DELETE RESTRICT(既定)削除そのものを拒否する(エラー)消えたら困るもの。 迷ったらこれ
ON DELETE CASCADE参照している行も一緒に消す親が消えたら意味を失うもの(中間テーブル、明細行)
ON DELETE SET NULL参照している列を NULL にする(列が NULL 可であること)親が消えても、子は残ってよいもの

実際に試します。まず RESTRICT。

DELETE FROM d_members WHERE id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
(`appdb`.`d_tasks`, CONSTRAINT `fk_task_member` FOREIGN KEY (`member_id`) REFERENCES `d_members` (`id`))

[スクリーンショット: 02_外部キー拒否.png] 上の DELETE が ERROR 1451 で拒否されている画面を撮ってください。この失敗が正常な動作なので、そのまま撮って構いません。

「担当タスクが残っている人は消せない」という業務ルールが、SQLで書かれていないのに効いています。これが外部キーの本当の役割です。

外部キーは「表を繋ぐもの」ではありません。 繋ぐだけなら列に数字を入れるだけで足ります。 外部キーは「繋がっていない状態を作れなくするもの」です。

次に SET NULL。プロジェクトを消します。

DELETE FROM d_projects WHERE name = 'サイト移行';
SELECT id, title, project_id FROM d_tasks;
+----+--------------------+------------+
| id | title              | project_id |
+----+--------------------+------------+
|  1 | ログイン画面を作る   |       NULL |
|  2 | APIの仕様を書く      |       NULL |
|  3 | DNSを切り替える      |       NULL |
+----+--------------------+------------+

プロジェクトは消え、タスクは残り、所属だけが外れました。 意図どおりなら正解です。

最後に CASCADE。タスクを消します。

SELECT * FROM d_task_tags WHERE task_id = 1;
DELETE FROM d_tasks WHERE id = 1;
SELECT * FROM d_task_tags WHERE task_id = 1;

タグの結び付きも一緒に消えました。 タスクが消えたのに結び付きだけ残っても、誰も使えないので、これが正しい挙動です。

CASCADE は、いちばん事故を起こす選択肢でもあります。 「1行消したつもりが、その下の数千行が黙って消える」が起きます。しかもエラーは出ません。 削除時異常を自分で作っているのと同じなので、「消えても本当に困らないか」を1回止まって考えてください。 迷ったら RESTRICT です。拒否されたら、そのとき考えれば済みます。

プロジェクトを戻しておきます。

INSERT INTO d_projects (id, name, due_on) VALUES (1, 'サイト移行', '2026-10-31');
UPDATE d_tasks SET project_id = 1 WHERE id IN (2, 3);

⑤ 主キーは連番かUUIDか

ここまで、主キーは全部 INT AUTO_INCREMENT(連番)にしてきました。 これでいいのか、という話をします。

選択肢は大きく2つです。

連番(INT AUTO_INCREMENT)UUID
見た目1, 2, 3550e8400-e29b-41d4-a716-446655440000
誰が決めるかデータベース(入れてみないと分からない)アプリ側で先に作れる
大きさ4バイト(BIGINT なら8)文字列なら36バイト、BINARY(16) なら16
推測できるかできる。 /tasks/1 の次は /tasks/2できない
件数が漏れるか漏れる。 IDが 4821 なら「4821件くらい」と分かる漏れない
複数のDBで作れるかぶつかるぶつからない

「推測できる」は、思っているより現実的な問題です。

認証の回やセキュリティの回で見たとおり、/api/tasks/2 を /api/tasks/3 に書き換えて他人のデータが見えるなら、それは連番のせいではなく権限確認をしていないせいです。UUIDにしても穴は塞がりません。 ただし、総当たりで全件を舐めることは難しくなります。

索引への影響 —— ここがパフォーマンスの回の回収

UUIDには、「大きい」以外の落とし穴があります。 これはEXPLAINの回を踏まえないと意味が分からない部分です。

MySQL(InnoDB)では、次の2つが効いてきます。

  1. データは主キーの順に並べて格納される(クラスタ化索引)
  2. 主キー以外の索引は、内部に「主キーの値」を持っている

1 が意味すること。 連番なら、新しい行は必ず末尾に足されます。UUID(v4)はランダムなので、毎回まったく違う位置に割り込みます。 割り込まれたページは分割され、隙間が増えます。

2 が意味すること。 主キーが太ると、すべての索引が太ります。 主キーが36バイトの文字列なら、title の索引にも done の索引にも、その36バイトが付いてきます。

測ります。 パフォーマンスの回と同じ手で、10万行を2通り作ります。

CREATE TABLE d_pk_int (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  KEY idx_title (title)
);

CREATE TABLE d_pk_uuid (
  id    CHAR(36) PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  KEY idx_title (title)
);
SET SESSION cte_max_recursion_depth = 1000000;

INSERT INTO d_pk_int (title)
WITH RECURSIVE seq AS (
  SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 100000
)
SELECT CONCAT('ダミー', n) FROM seq;

INSERT INTO d_pk_uuid (id, title)
SELECT UUID(), title FROM d_pk_int;

UUID() は MySQL が持っている関数なので、追加のインストールは要りません。

大きさを比べます。

ANALYZE TABLE d_pk_int, d_pk_uuid;

SELECT table_name, data_length, index_length
FROM information_schema.tables
WHERE table_schema = 'appdb' AND table_name LIKE 'd\_pk\_%';
+------------+-------------+--------------+
| TABLE_NAME | data_length | index_length |
+------------+-------------+--------------+
| d_pk_int   |     5783552 |      3686400 |
| d_pk_uuid  |    12582912 |      8388608 |
+------------+-------------+--------------+

手元では、データも索引も2倍以上になりました。 数字はVMの状態で変わりますし、information_schema の値は概算です。 正確な比較ではなく、「桁で違う」ことを見てください。

索引が2倍になるということは、メモリに載る量が半分になるということです。 パフォーマンスの回で見た「索引が効いている / 効いていない」の手前に、「索引がメモリに載っているか」という段があります。

結局どうするか

この記事の結論は、こうです。

状況選ぶもの
1台のデータベースに書く。学習用。個人開発連番でよい。 迷わなくていい
IDをURLに出す。件数を隠したいUUID、または連番+別途「公開用ID」
複数の場所でIDを作る(オフライン対応、分散)UUID一択

そして、UUIDを使うときの実務的な逃げ道が2つあります。

  • CHAR(36) ではなく BINARY(16) に詰める。 MySQL 8 なら UUID_TO_BIN(uuid, 1) / BIN_TO_UUID(id, 1) が使えます。第2引数の 1 が重要で、時刻部分を前に並べ替えて、連番に近い順序にしてくれます
  • UUIDv7 を使う。 先頭に時刻が入っている新しい形式で、ランダムなのに時刻順に並びます。 ランダム割り込みの問題が起きません

「主キーは連番にしなさい」でも「UUIDにしなさい」でもありません。 どちらにも代償があり、その代償が索引に出る——これが分かっていれば十分です。

⑥ NULL は「無い」ではない

d_tasks で project_id INT NULL を許しました。 ここで NULL の話をします。

NULL は「値が無い」と説明されますが、実際には次の3つを兼ねてしまいます。

意味例
まだ決まっていない(未定)締切がまだ決まっていない
そもそも該当しないプロジェクトに属さないタスク
入力されなかった(不明)移行してきた古いデータで、値が取れなかった

この3つは、業務上まったく違う話です。 なのに、データベース上は全部 NULL になります。 あとから「これは未定だったのか、該当なしだったのか」を区別することはできません。

そして NULL は、SQLの挙動そのものを変えます。 これは実際に打ってみるのが早いです。 ここまでの手順で project_id が NULL のタスクは残っていないので、先に1件入れてから打ちます。

INSERT INTO d_tasks (title, project_id, member_id) VALUES ('備品を発注する', NULL, 2);

SELECT COUNT(*) AS 全件, COUNT(project_id) AS project_idが入っている件数 FROM d_tasks;
SELECT * FROM d_tasks WHERE project_id = NULL;
SELECT * FROM d_tasks WHERE project_id IS NULL;
SELECT * FROM d_tasks WHERE project_id <> 1;

4つとも、結果が違います。

打ったもの結果理由
COUNT(*) と COUNT(列)数が違うCOUNT(列) は NULL を数えない
WHERE project_id = NULL0件(エラーにもならない)NULL は「値」ではないので = で比較できない
WHERE project_id IS NULL出るNULL を調べる唯一の書き方
WHERE project_id <> 1NULL の行が出てこない「1ではない」の判定が、NULLに対しては「不明」になる

最後の行が、いちばん事故ります。

「プロジェクト1以外のタスクを全部出す」つもりのSQLから、所属なしのタスクが黙って消えます。

空文字列('')と NULL も別物です。

SELECT '' = NULL, '' IS NULL, LENGTH(''), LENGTH(NULL);

'' は「長さ0の文字列がある」、NULL は「そもそも無い」です。混ぜて入れると、WHERE name = '' と WHERE name IS NULL の両方を書く羽目になります。

だから、既定は NOT NULL にする

設計時の方針を1つ決めておきます。

列は、まず NOT NULL で作る。 NULL を許すのは、「NULLである」ことに意味があると説明できるときだけ。

d_tasks でいうと、こうです。

列判断
titleNOT NULL。 タイトルの無いタスクは存在しない
doneNOT NULL DEFAULT FALSE。 「未着手」は false であって NULL ではない
project_idNULL 可。 「どのプロジェクトにも属さない」に意味がある
member_idNULL 可。 「まだ誰も担当していない」に意味がある
due_onNULL 可。 「締切なし」に意味がある

「なんとなく NULL 可」を無くすだけで、あとで書くSQLがかなり減ります。

NOT NULL DEFAULT '' に逃げないでください。 「空文字を入れておけば NULL を避けられる」は、NULLの問題を「空文字とNULLの2種類ある」問題に増やしただけです。

⑦ 正規化しすぎると遅くなる

ここまで「分けろ」と言い続けてきました。 その代償を、いま払います。

「プロジェクトごとのタスク件数を出す」をやります。

d_flat(1枚の表)ならこうです。

SELECT project_name, COUNT(*) FROM d_flat GROUP BY project_name;

分けたあとはこうです。

SELECT p.name, COUNT(t.id)
FROM d_projects p
LEFT JOIN d_tasks t ON t.project_id = p.id
GROUP BY p.id, p.name;

さらに「担当者名とタグも一緒に出したい」となると、こうなります。

SELECT t.title, m.name AS 担当, p.name AS プロジェクト,
       GROUP_CONCAT(g.name) AS タグ
FROM d_tasks t
LEFT JOIN d_members  m ON t.member_id  = m.id
LEFT JOIN d_projects p ON t.project_id = p.id
LEFT JOIN d_task_tags tt ON tt.task_id = t.id
LEFT JOIN d_tags     g ON g.id = tt.tag_id
GROUP BY t.id, t.title, m.name, p.name;

JOIN が4つになりました。 これが正規化の代償です。

1枚にまとめた形正規化した形
書きやすさ簡単(SELECT * FROM d_flat)JOIN が要る
読む速さ速い(1つの表を読むだけ)遅くなりうる(表を突き合わせる)
正しさ壊れる壊れない
書く速さ遅い(同じ値を何度も書く)速い(1箇所だけ書く)

あえて重複を残す(非正規化)

「プロジェクト一覧に、タスク件数を出したい」という画面があったとします。毎回 JOIN して COUNT するのが重いなら、d_projects に件数の列を持たせる——これが非正規化です。

ALTER TABLE d_projects ADD COLUMN task_count INT NOT NULL DEFAULT 0;

これは「重複」です。 d_tasks を数えれば分かることを、もう1箇所に書いているからです。つまり、更新時異常が戻ってきます。

タスクを1件足したのに task_count を足し忘れたら、画面の数字が嘘になります。

それでもやることはあります。 やるなら、条件が3つあります。

条件中身
1. 先に測っているEXPLAIN と実測で、その JOIN が本当に遅いことを確認した。パフォーマンスの回の型
2. 更新を1箇所に閉じ込めたタスクを足す処理と task_count を足す処理を、トランザクションで1つにまとめた
3. ズレを直せる手があるd_tasks から数え直して上書きするSQLを用意してある

3つ目は、必ず用意してください。 非正規化した列は、いつか必ずズレます。

UPDATE d_projects p
SET task_count = (SELECT COUNT(*) FROM d_tasks t WHERE t.project_id = p.id);

順番を間違えないでください。 「正規化する → 測る → 遅ければ崩す」 であって、「最初から崩しておく」ではありません。 崩れた設計を後から正規化するのは、正規化した設計を後から崩すよりずっと大変です。壊れたデータが既に入っているからです。

⑧ 番号との対応表

ここでようやく、教科書の言葉と突き合わせます。 やったことは全部済んでいるので、名前を付けるだけです。

正規形教科書の言い方この記事でやったこと
第1正規形(1NF)繰り返し項目を無くすtags のカンマ区切りをやめて、1マス1値にした(直し1)
第2正規形(2NF)部分関数従属を無くす中間テーブルに tag_name を持たせなかった(直し3)
第3正規形(3NF)推移的関数従属を無くすassignee_email と project_due を、担当者とプロジェクトの表に移した(直し2)

判定のしかたも、日本語にしておきます。

番号自分に問う言葉
1NF「1つのマスに、2つ以上の値が入っていないか?」
2NF「主キーが複数列のとき、その一部だけで決まる列が混ざっていないか?」
3NF「主キーではなく、同じ行の別の列で決まる列が混ざっていないか?」

2NF は、主キーが1列(id の連番)なら自動的に満たされます。 id の一部、というものが存在しないからです。気にする必要があるのは、中間テーブルのように主キーが2列以上あるときだけです。

やりすぎない

第4、第5正規形、そしてボイス・コッド正規形(BCNF)というものがあります。 この記事では扱いません。

理由を正直に書きます。

実務で作る表の大半は、第3正規形で止めて問題が出ません。

BCNF 以降が効いてくるのは、「候補キーが複数あって、それが重なっている」ような表です。そういう表を設計する場面は、少なくとも最初の数年ではほとんど来ません。

来たときに調べれば間に合います。 名前だけ知っておけば、「あ、これはもっと上の正規形の話だ」と気づけます。

逆に、次のことは毎回やる価値があります。

  • 1つの表に、2種類の「もの」を入れていないか確かめる
  • 同じ文字列が何行にも出てきていないか、SELECT DISTINCT で見る
  • NOT NULL を既定にする
  • 外部キーを張って、ON DELETE を選ぶ

⑨ 設計は、先に紙でやる

ここまでの5枚を、いきなり CREATE TABLE から書き始めたわけではありません。 先に図を描きます。

ER図と呼ばれますが、道具は何でもかまいません。 紙とペンで十分です。 私は最初、罫線ノートに書きました。

描くのは3つだけです。

  1. 箱(=表)を書いて、中に列を並べる
  2. 箱と箱を線で結ぶ
  3. 線の両端に「1」か「多」を書く
[ d_members ]---1----多---[ d_tasks ]---多----1---[ d_projects ]
                              |
                             多
                              |
                        [ d_task_tags ]
                              |
                              多
                              |
                          [ d_tags ]

[スクリーンショット: 03_er図.png] 紙でもツールでもかまいません。上の5枚の関係を自分で描いた図を撮ってください。きれいである必要はありません。

この図を描く途中で、設計の間違いはほぼ全部見つかります。

図で気づけること意味
箱の中に、明らかに種類の違う列が並んでいるその箱は2つに分けるべき
線が「多対多」になっている中間テーブルが要る
どの箱にも繋がらない箱がある本当に要るのか考え直す
1つの箱に線が5本以上入っている悪いとは限らないが、その箱を触る処理は必ず重くなる

CREATE TABLE を書き始めてから設計を直すのは、コストが違います。 紙なら消しゴムで済むものが、データが入ってからだとマイグレーションとデータ移行になります。

Knex のマイグレーションに落とす

図が固まったら、ORMの回でやった形に落とします。 CREATE TABLE を手で打ちっぱなしにしないためです。

新しいマイグレーションを作ります。

bash
$ cd ~/server
$ node --env-file=.env.migrate node_modules/knex/bin/cli.js migrate:make add_projects_and_tags
bash
$ nano ~/server/migrations/*_add_projects_and_tags.js
exports.up = async function (knex) {
  await knex.schema.createTable("members", (t) => {
    t.increments("id");
    t.string("name", 100).notNullable();
    t.string("email", 255).notNullable().unique();
  });

  await knex.schema.createTable("projects", (t) => {
    t.increments("id");
    t.string("name", 100).notNullable().unique();
    t.date("due_on").nullable();
  });

  await knex.schema.createTable("tags", (t) => {
    t.increments("id");
    t.string("name", 50).notNullable().unique();
  });

  await knex.schema.alterTable("tasks", (t) => {
    t.integer("project_id").unsigned().nullable();
    t.integer("member_id").unsigned().nullable();
    t.foreign("project_id").references("projects.id").onDelete("SET NULL");
    t.foreign("member_id").references("members.id").onDelete("RESTRICT");
  });

  await knex.schema.createTable("task_tags", (t) => {
    t.integer("task_id").unsigned().notNullable();
    t.integer("tag_id").unsigned().notNullable();
    t.primary(["task_id", "tag_id"]);
    t.foreign("task_id").references("tasks.id").onDelete("CASCADE");
    t.foreign("tag_id").references("tags.id").onDelete("RESTRICT");
  });
};

exports.down = async function (knex) {
  await knex.schema.dropTableIfExists("task_tags");
  await knex.schema.alterTable("tasks", (t) => {
    t.dropForeign("project_id");
    t.dropForeign("member_id");
    t.dropColumn("project_id");
    t.dropColumn("member_id");
  });
  await knex.schema.dropTableIfExists("tags");
  await knex.schema.dropTableIfExists("projects");
  await knex.schema.dropTableIfExists("members");
};

3つ、読みどころがあります。

書き方意味
.unsigned()t.increments("id") は符号なし整数を作ります。参照する側も unsigned にしないと型が合わず、外部キーが作れません(errno: 150)
.onDelete("RESTRICT") を明示している既定でも RESTRICT ですが、書いておくと「考えた結果」だと分かります
down の順番が up の逆参照している側から消す。 逆にすると外部キーに拒否されます

流します。

bash
$ node --env-file=.env.migrate node_modules/knex/bin/cli.js migrate:latest
$ node --env-file=.env.migrate node_modules/knex/bin/cli.js migrate:status

戻せることも、必ず確認してください。

bash
$ node --env-file=.env.migrate node_modules/knex/bin/cli.js migrate:rollback
$ node --env-file=.env.migrate node_modules/knex/bin/cli.js migrate:latest

すでに本番にデータが入っている表に、あとから外部キーを張ると失敗します。 参照先に存在しない値が既に入っていると、ALTER TABLE が拒否されます(errno: 1452)。 これは外部キーが厳しいのではなく、「壊れたデータが既にある」ことが発覚しただけです。 先に SELECT で孤児を探して、片付けてから張ります。

⑩ NoSQL の場合はどうなるか

NoSQLの回を踏まえて、最後に1つ回収します。

MongoDB のようなドキュメント指向では、いまやった正規化を「やらない」のが普通です。 タスク1件が、こういう形になります。

{
  _id: ObjectId("..."),
  title: "ログイン画面を作る",
  done: false,
  assignee: { name: "佐藤", email: "sato@example.com" },
  project:  { name: "サイト移行", due_on: "2026-10-31" },
  tags: ["ui", "api"]
}

この記事で「壊れる」と言った形そのものです。 担当者の情報もプロジェクトの情報も、タスクの中に埋め込まれています。

では、なぜ成立するのか。

論点リレーショナルドキュメント指向
重複はあるか無くすある。前提として受け入れる
誰が食い違いを防ぐかデータベース(1箇所しか無い)アプリ(全ドキュメントを書き換える)
佐藤さんの改名1行 UPDATE佐藤さんを含む全ドキュメントを書き換える
1件を読む速さJOIN が要る1回で全部取れる

トレードオフが逆になっているだけです。「正規化しなくていい」のではなく、「正規化しない代わりに、整合性の責任をアプリが持つ」です。

NoSQLの回で書いた「スキーマは消えずにアプリへ移る」と、まったく同じ構図です。移る先が、今度は「整合性」になっただけです。

そして、これがこの記事をリレーショナルで書いた理由でもあります。 正規化を知らないままドキュメント指向に行くと、「埋め込むしかない」ではなく「埋め込んでいいのかどうかが分からない」状態になります。 崩し方を知るには、まず組み立て方を知る必要があります。

つまずいたところ

症状原因
ERROR 1364 Field 'title' doesn't have a default value正しい失敗。 ② の挿入時異常そのもの
ERROR 1451 Cannot delete or update a parent row正しい失敗。 ON DELETE RESTRICT が効いている。子を先に消すか、ON DELETE を選び直す
ERROR 1452 Cannot add or update a child row参照先に無いIDを入れようとした。 または外部キーを張る前から孤児が入っていた
errno: 150 Foreign key constraint is incorrectly formed型が合っていない。 t.increments() は unsigned なので、参照する側も .unsigned() が要る。文字コード(COLLATE)違いでも出る
Cannot drop index: needed in a foreign key constraint外部キーが索引を使っている。 先に外部キーを落としてから索引を落とす
外部キーを張ったのに効かないテーブルが MyISAM。 SHOW TABLE STATUS で Engine を確認。InnoDB でないと外部キーは無視される
WHERE 列 <> 1 で件数が合わないNULL の行が落ちている。 OR 列 IS NULL を足すか、そもそも NOT NULL にする
WHERE 列 = NULL が0件でエラーも出ない正常。 IS NULL を使う
GROUP_CONCAT の結果が途中で切れる既定で1024バイト。SET SESSION group_concat_max_len = 10000;
UNIQUE にしたのに NULL が複数入る正常。 NULL 同士は「等しい」と判定されないので、UNIQUE は NULL を何個でも許す
JOIN したら行が減ったINNER JOIN は、片方に無い行を落とします。 残したいなら LEFT JOIN
LEFT JOIN して COUNT(*) が1になるCOUNT(*) は行を数えるので、繋がらなくても1。 COUNT(子の列) にする
d_pk_uuid への INSERT が体感で遅い想定どおり。 ⑤ のランダム割り込みが起きている
information_schema の数字が変わらないANALYZE TABLE を打つ。 それでも概算です
cte_max_recursion_depth でエラーSET SESSION を同じ接続で打つ。sudo mysql を抜けるとリセットされます

後片づけ

この記事で作ったものは、すべて d_ で始まっています。

消す順番があります。 参照されている表は先に消せません。

bash
$ sudo mysql -e "SELECT table_name FROM information_schema.tables WHERE table_schema='appdb' AND table_name LIKE 'd\_%';"

中身を確認してから、まとめて消します。

USE appdb;

DROP TABLE IF EXISTS d_task_tags;
DROP TABLE IF EXISTS d_tasks;
DROP TABLE IF EXISTS d_tags;
DROP TABLE IF EXISTS d_projects;
DROP TABLE IF EXISTS d_members;
DROP TABLE IF EXISTS d_flat;
DROP TABLE IF EXISTS d_pk_int;
DROP TABLE IF EXISTS d_pk_uuid;

消えたことを確認します。

bash
$ sudo mysql -e "SHOW TABLES FROM appdb;"

tasks・users・sessions が残っていれば正常です。d_ で始まる表が1枚も無ければ、元通りです。

⑨ の Knex マイグレーションを流していて、要らなくなった場合。

bash
$ cd ~/server
$ node --env-file=.env.migrate node_modules/knex/bin/cli.js migrate:rollback

migrate:rollback は「最後のバッチだけ」を戻します。 --all を付けると全部消えますので、migrate:status で戻る範囲を確認してから打ってください。

まとめ

  • テーブル設計の話は、本当はもっと早い段階のもの。 ただし壊れたことがある人にしか意味が伝わらない
  • 1つの表に全部入れると、同じ情報が何行にも重複する。 問題は無駄ではなく、重複が食い違うこと
  • 更新時異常 —— 1箇所だけ直せてしまう構造だから、いつか食い違う。エラーは出ない
  • 挿入時異常 —— 独立して存在できるものが他の表に同居していると、でっちあげないと登録できない
  • 削除時異常 —— その情報の置き場所がそこしか無いと、1件消しただけで道連れになる。痕跡ごと消える
  • 3つの異常は、全部「1つの表に2種類以上のものを入れた」ことから出ている
  • 正規化は番号を当てはめる作業ではない。 「1マス1値にする」「そのもの自身の情報を、そのものの表に移す」の2つでほぼ終わる
  • 第3正規形まででほぼ足りる。 BCNF以降は、必要になってから調べれば間に合う
  • 2NF は主キーが1列なら自動的に満たされる。 気にするのは中間テーブルのような複合主キーのときだけ
  • 外部キーは「繋ぐもの」ではなく「繋がっていない状態を作れなくするもの」
  • ON DELETE は3択。 迷ったら RESTRICT。 CASCADE は削除時異常を自分で作ることがある
  • 主キーの連番とUUIDは、どちらにも代償がある。 代償は索引に出る
  • InnoDB は主キーの順に並べて格納し、全ての索引が主キーの値を持つ。 主キーが太ると索引が全部太る
  • UUIDを使うなら BINARY(16)(UUID_TO_BIN(x, 1))か UUIDv7。 ランダム割り込みを避ける
  • NULL は「無い」ではない。 「未定」「該当なし」「不明」を1つの記号が兼ねてしまう
  • = NULL は常に0件。 IS NULL だけが使える。 <> 1 は NULL の行を黙って落とす
  • 列はまず NOT NULL で作る。 NULL を許すのは、NULLであることに意味があると説明できるときだけ
  • NOT NULL DEFAULT '' に逃げない。 区別すべきものが2種類に増えるだけ
  • 正規化しすぎると JOIN が増えて遅くなる。 あえて重複を残す(非正規化)判断はある
  • 非正規化は「測ってから」。 正規化する → 測る → 遅ければ崩すの順番。最初から崩さない
  • 非正規化した列は必ずズレる。 数え直して上書きするSQLを、先に用意しておく
  • 設計は先に紙で描く。 箱と線と「1か多」の3つだけで、間違いのほとんどが見つかる
  • できた設計はマイグレーションに落とす。 CREATE TABLE を手で打ちっぱなしにしない
  • NoSQL では正規化しないのが普通。 その代わり整合性の責任がアプリに移る。 トレードオフが逆になっているだけ

参考リンク


次に読む

この記事は 【第5段階】バックエンドとデータベース の 18 / 29 本目です。

記事
← 前の記事SQLiteはおもちゃではない — 用途で選ぶデータベース
→ 次の記事文字化けは壊れたのではない — 文字コードとUTF-8

上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。

全体の順路は はじめに にあります。

roadmap.sh のどこをやっているか

Backend›Database Design
  • 正規化(更新時・挿入時・削除時の異常を実際に起こす)
  • 主キーの選び方・NULL・非正規化の判断

この記事は、上の項目を実際に手を動かして確かめる回です。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次