第1正規形 (1NF)
「sql」で探すと「sqlite」の記事まで出てくる
記事にタグを付けます。タグは複数付くので、1 つの列にカンマで並べて入れました。
SQL クエリ
CREATE TABLE articles_bad (
id INT PRIMARY KEY,
title VARCHAR(200),
tags VARCHAR(500) -- 'sql,sqlite,beginner' のように詰め込む
);「sql タグの記事を一覧にする」機能を作る段になって、手が止まります。この列の中身はただの 1 本の文字列なので、tags = 'sql' で比べても 1 件も当たりません。LIKE '%sql%' に逃げると、今度は sqlite の記事まで混ざってきます。区切り文字ごと探せば当たりますが、先頭のタグと末尾のタグで条件の書き方が変わります。
タグを 1 つ外す処理も同じです。文字列から該当箇所を切り取り、余ったカンマをつなぎ直す処理を、アプリ側に書くことになります。タグの追加は文字列の末尾に足すだけに見えて、同じタグが二重に入っていないかを毎回調べる必要があります。
1 つのセルに、値は 1 つだけ
原因は検索の書き方ではなく、1 つのセルに複数の値を詰めたことです。セルの中身は、これ以上分解する必要のない 1 つの値にします。
詰め込んだ値は、横ではなく縦に開きます。タグが 3 つなら 3 行です。
SQL クエリ
CREATE TABLE articles (
id INT PRIMARY KEY,
title VARCHAR(200)
);
CREATE TABLE article_tags (
article_id INT,
tag VARCHAR(50),
PRIMARY KEY (article_id, tag)
);tag 列には常に 1 つのタグしか入りません。あいまい検索に頼らずに絞り込めますし、この列にインデックスを張れば速くもなります。タグを 1 つ外すのは 1 行の DELETE、足すのは 1 行の INSERT です。主キーを (article_id, tag) の組にしてあるので、同じタグを 2 回付けることもできません。
行は増えるが、扱いは軽くなる
縦に開くと行数は増えます。記事 100 本にタグが平均 3 つなら 300 行です。それでも、絞り込みも集計も普通の SQL で書けるようになるので、扱いは軽くなります。文字列を組み立てたり分解したりする処理がアプリ側から消えるぶん、バグの居場所も減ります。
第 1 正規形とは、すべての列の値が 1 つの値であり、同じ意味の項目が繰り返し詰め込まれていない状態のことです。ここを満たしていない表は、この先の正規形の議論に進めません。
JSON 型を使えば配列をそのまま保存できますが、それは 1 つのセルに複数の値を入れる設計に戻ることを意味します。絞り込みや集計の起点にする値は行に開き、まとめて読み書きするだけの設定値やログの中身だけを JSON にする、という線引きが現実的です。
テーブル構造
CREATE TABLE articles_bad (
id INT PRIMARY KEY,
title VARCHAR(200),
tags VARCHAR(500)
);
INSERT INTO articles_bad VALUES
(1, 'SQL 入門', 'sql,database,beginner'),
(2, 'インデックス入門', 'sql,index,performance'),
(3, 'Python 基礎', 'python,beginner'),
(4, 'GO で API 構築', 'go,api,backend');
CREATE TABLE articles (
id INT PRIMARY KEY,
title VARCHAR(200)
);
CREATE TABLE article_tags (
article_id INT,
tag VARCHAR(50),
PRIMARY KEY (article_id, tag)
);
INSERT INTO articles VALUES
(1, 'SQL 入門'),
(2, 'インデックス入門'),
(3, 'Python 基礎'),
(4, 'GO で API 構築');
INSERT INTO article_tags VALUES
(1,'sql'), (1,'database'), (1,'beginner'),
(2,'sql'), (2,'index'), (2,'performance'),
(3,'python'), (3,'beginner'),
(4,'go'), (4,'api'), (4,'backend');期待される出力
| id | title |
|---|---|
| 1 | SQL 入門 |
| 2 | インデックス入門 |