第1正規形
1つのマスに複数の値を入れない
正規化は段階になっていて、下から順に進みます。最初の段階が第1正規形、略して 1NF です。条件はたった1つです。
どの列も、1つの行につき値を1つしか持たない。
言い換えると、1つのマスにカンマ区切りで詰め込まないということです。
詰め込まれた表
商品にタグを付けたい、という要件が来たとします。手っ取り早い実装はこうなりがちです。
| id | name | tags |
|---|---|---|
| 1 | ノートPC | laptop,sale |
| 2 | マウス | accessory |
| 3 | キーボード | accessory,sale |
動きはします。しかし、この形にした瞬間にできなくなることがあります。
タグで検索できません。 WHERE tags = 'sale' は1件も引っかかりません。LIKE '%sale%' なら引っかかりますが、presale という別のタグまで一緒に引っかかります。
タグの数を数えられません。 どのタグが何商品に付いているかを出すには、文字列を切り刻む処理が要ります。
タグ名を変えられません。 sale を bargain に変えるには、全行の文字列を置換することになります。第1章で見た更新不整合の入り口です。
行に分解する
直し方は決まっています。繰り返す部分を別のテーブルに切り出して、1つ1つを行にするのです。
| product_id | tag |
|---|---|
| 1 | laptop |
| 1 | sale |
| 2 | accessory |
こうなれば WHERE tag = 'sale' で正確に引けますし、GROUP BY tag で数も出せます。
列を横に増やすのも 1NF 違反
もう1つよくある形が tag1、tag2、tag3 と列を横に並べるものです。これも 1NF 違反です。
4つ目のタグが来たら列を足すことになり、そのたびに全部のクエリを書き直すことになります。繰り返すものは行にする、列にはしない。 これが 1NF の実践的な意味です。
分解を SQL でやる
PostgreSQL には文字列を配列に切る string_to_array と、配列を行に展開する unnest があります。
SELECT id, unnest(string_to_array(tags, ',')) FROM products_flat;1行が2行3行に増えて出てきます。これをそのまま新しいテーブルに入れれば、分解が終わります。
手を動かす
カンマ区切りのタグを、タグ1件1行の形に直します。
テーブル構造
CREATE TABLE products_flat (
id integer PRIMARY KEY,
name text NOT NULL,
tags text NOT NULL
);
INSERT INTO products_flat VALUES
(1, 'ノートPC', 'laptop,sale'),
(2, 'マウス', 'accessory'),
(3, 'キーボード', 'accessory,sale'),
(4, 'モニタ', 'display,sale');
期待される出力
| product_id | tag |
|---|---|
| 1 | laptop |
| 1 | sale |
| 2 | accessory |
| 3 | accessory |
| 3 | sale |
| 4 | display |
| 4 | sale |