第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 |
| 3 | accessory |
| 3 | sale |
こうなれば WHERE tag = 'sale' で正確に引けますし、GROUP BY tag で数も出せます。
列を横に増やすのも 1NF 違反
もう1つよくある形が tag1、tag2、tag3 と列を横に並べるものです。これも 1NF 違反です。
4つ目のタグが来たら列を足すことになり、そのたびにタグを扱うクエリをすべて書き直すことになります。繰り返すものは行にする、列にはしない。これが 1NF の実践的な意味です。
分解を SQL でやる
PostgreSQL には文字列を配列に切る string_to_array と、配列を行に展開する unnest があります。
別題材の図書館で同じ書き方を試すと、次のようになります。
SQL クエリ
SELECT id, unnest(string_to_array(tags, ',')) FROM books_flat;1行が2行3行に増えて出てきます。これをそのまま新しいテーブルに入れれば、分解が終わります。
分解した行を移す
別題材として、読書記録 reading_notes の keywords が sql,index なら、分解後は sql と index の2行になります。
SQL クエリ
CREATE TABLE note_keywords (note_id integer, keyword text);
INSERT INTO note_keywords (note_id, keyword)
SELECT id, unnest(string_to_array(keywords, ','))
FROM reading_notes;string_to_array はカンマで分けた配列を作り、unnest は要素ごとに行を返します。INSERT INTO ... SELECT は SELECT が返した行を、指定した列の順で移送します。列名を明示しておけば移送先との対応を読みやすくできます。
手を動かす
カンマ区切りのタグを、タグ1件1行の形に直します。
products_flat の tags にカンマ区切りでタグが詰め込まれています。これを 1NF に直します。
product_tagsテーブルを作る。列はproduct_id integer NOT NULLとtag text NOT NULL、主キーは(product_id, tag)の複合主キーproducts_flatから、タグ1件を1行にしてproduct_tagsに入れる。string_to_arrayとunnestを使うproduct_tagsの全行をproduct_id、tagの順に取り出す
タグは1件も取りこぼさないでください。
分解先の表と確認は用意済みです。1タグ1行になるよう移送します。
ひな形の「ここに学ぶ部分を追記する」を SQL に置き換えます。模範解答では 3 行を補います。確認用の SQL は用意してあります。
テーブル構造
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 |
ヒント
まだ開いていません
付箋は 2 枚。下の段ほど答えに近づきます
ヒント 2答えに近い