一覧DB-06-1 · 正規化と設計

データの重複と第一正規形

注文と商品を一表にまとめた状態で、更新・追加・削除の問題を再現します。商品表を分けて同じ変更を行い、最後に商品コードのカンマ区切り一覧を明細行へ整理して、検索する単位の違いを確認します。ここで商品名は現在の名称を表し、取引時の名称履歴ではありません。

説明

注文の明細に商品名まで繰り返し保存すると、一つの商品名を変更するために複数行を直す必要があります。また、注文前の商品を登録できない、最後の注文を消すと商品情報まで失う、といった問題が起こります。

1商品名を注文ごとに繰り返す表

一行は注文内の商品明細ですが、商品名も同じ行へ保存します。

CREATE TABLE flat(order_id INTEGER,sku TEXT,product_name TEXT,qty INTEGER,PRIMARY KEY(order_id,sku)一行は注文と商品の組

) STRICT;
INSERT INTO flat VALUES

(101,'P1','ノート',2),(102,'P1','ノート',1)同じ商品名を複数行に保存

,(102,'P2','ペン',3);
SELECT * FROM flat ORDER BY order_id,sku;

商品P1の名前が二行にあります。この例ではP1の現在名は一つという業務上の規則がありますが、表の主キーは注文と商品の組なので、商品名の一致までは検査していません。

2一行だけ名前を変更する

注文101のP1だけを方眼ノートへ変えます。

UPDATE flat SET product_name='方眼ノート'一方の名称を変更 WHERE order_id=101一つの注文だけが対象

 AND sku='P1';
SELECT order_id,sku,product_name FROM flat WHERE sku='P1' ORDER BY order_id;

P1に方眼ノートとノートの二つの名前が残ります。これは同じ事実を複数行で持ち、一部だけ更新したための不整合です。注文ごとの履歴として意図した差ではありません。

3商品コードごとの不一致を探す

同じSKUに複数の名前があるものを集計します。

SELECT sku,COUNT(DISTINCT product_name)同じ商品の名称の種類数 AS names FROM flat GROUP BY sku HAVING COUNT(DISTINCT product_name)>1複数の名称がある商品;

P1に2種類と表示されます。この検査は現在のデータにある矛盾を見つけるもので、今後の更新で不整合を防ぐ制約にはなっていません。

4注文前の商品を追加しようとする

注文のないP3を保存しようとします。

INSERT INTO flat VALUES(NULL,'P3'注文番号なしの商品,'消しゴム',NULL);

STRICT表の複合主キーorder_idがNULLになり、拒否されます。商品を登録したいだけなのに注文番号を要求されるのは、異なる対象を一つの行に押し込めた設計上の問題です。

5最後の明細を消すと商品情報も失う

BEGIN内でP2の最後の明細を削除します。

BEGIN取消し可能な範囲を開始

DELETE FROM flat WHERE order_id=102 AND sku='P2'最後の商品明細を削除

;
SELECT COUNT(*) AS p2_rows FROM flat WHERE sku='P2';

P2の行は0件となり、現在名がペンである情報も表から消えました。注文明細の削除と商品情報の削除は、別々に判断できる設計が必要です。次にROLLBACKでこの試行を戻します。

6商品名の不一致を解消して分割する

削除を戻し、P1の現在名を方眼ノートに統一してから表を分けます。

ROLLBACK;

UPDATE flat SET product_name='方眼ノート'移行前に正しい名称へ統一

 WHERE sku='P1';
CREATE TABLE products(sku TEXT PRIMARY KEY,name TEXT NOT NULL) STRICT;
INSERT INTO products

SELECT DISTINCT sku,product_name商品情報を一件ずつ取得

 FROM flat;
CREATE TABLE lines(order_id INTEGER,sku TEXT REFERENCES products(sku),qty INTEGER,PRIMARY KEY(order_id,sku)) STRICT;

INSERT INTO lines SELECT order_id,sku,qty明細に属する値を移す

 FROM flat;
SELECT * FROM products ORDER BY sku;

商品表はP1・P2の二行です。分割前に矛盾した名前のどちらを正しいとするか決めています。DISTINCTを使うだけで矛盾が自動解決するわけではなく、異なる名前が残れば商品表の主キー重複になります。

7一か所の名前変更を一覧へ反映する

商品表のP1を改名し、明細と結びます。

UPDATE products SET name='A5ノート'商品名の保存先一か所を変更

 WHERE sku='P1';
SELECT l.order_id,l.sku,p.name,l.qty FROM lines AS l

JOIN products AS p ON p.sku=l.sku必要時に現在名を取得 ORDER BY l.order_id,l.sku;

二つの注文でP1の名前がA5ノートになりました。現在の商品名を商品表一行に保存し、明細はSKUで参照しているため、名前を複数箇所で同期する必要がなくなります。

8注文なしの商品と、明細削除後の商品

P3を商品表へ追加し、P2の明細を消します。

INSERT INTO products VALUES('P3','消しゴム')注文なしの商品を登録

DELETE FROM lines WHERE sku='P2'商品を残して明細だけ削除

;
SELECT * FROM products ORDER BY sku;

商品表にはP1・P2・P3が残ります。P3は注文がなくても登録でき、P2は明細を消しても商品として残せます。商品の存在と注文の存在を分けて保存できました。

9カンマ区切りの一覧を部分一致で探す

商品コードの一覧を文字列にした表で、P1を検索します。

CREATE TABLE packed(order_id INTEGER PRIMARY KEY,skus TEXT) STRICT;
INSERT INTO packed VALUES(201,

'P1,P2'複数コードを一つの文字列に保存

),(202,'P10');
SELECT * FROM packed WHERE skus

LIKE '%P1%'コードの境界を知らない部分一致 ORDER BY order_id;

注文201だけでなく202のP10も一致します。文字列の部分一致は商品コード単位の一致ではありません。商品一覧を一つの値に押し込めると、参照や数量も文字列の解析に依存してしまいます。

10商品コードを明細の行にする

同じ二注文を、一つのコードが一行になる表で表します。

CREATE TABLE unpacked(order_id INTEGER,sku TEXT,PRIMARY KEY(order_id,sku)) STRICT;
INSERT INTO unpacked VALUES

(201,'P1'),(201,'P2')一注文の複数商品を別行へ

,(202,'P10');
SELECT * FROM unpacked

WHERE sku='P1'コード全体の等号で検索 ORDER BY order_id;

注文201だけが返ります。各列を用途上の一つの値として扱い、繰返しを行へ分けるのが第一正規形を考える出発点です。文字列が分割可能というだけで問題なのではなく、ここでは商品コードを個別に検索・参照する必要があることが設計の理由です。

まとめ

同じ現在情報を複数行へ保存すると、更新の取りこぼしや追加・削除の異常が起こります。

商品と注文の明細を分け、必要な一覧はキーで結んで取得します。

繰返しをカンマ区切りへ押し込めず、個別に扱う値の単位に合わせて行を設計します。

操作履歴と解説を振り返れます。

SQLite 3.54.0

CREATE TABLE flat(order_id INTEGER,sku TEXT,product_name TEXT,qty INTEGER,PRIMARY KEY(order_id,sku)) STRICT;
INSERT INTO flat VALUES(101,'P1','ノート',2),(102,'P1','ノート',1),(102,'P2','ペン',3);
SELECT * FROM flat ORDER BY order_id,sku;

╭──────────┬─────┬──────────────┬─────╮
│ order_id │ sku │ product_name │ qty │
╞══════════╪═════╪══════════════╪═════╡
│      101 │ P1  │ ノート       │   2 │
│      102 │ P1  │ ノート       │   1 │
│      102 │ P2  │ ペン         │   3 │
╰──────────┴─────┴──────────────┴─────╯

UPDATE flat SET product_name='方眼ノート' WHERE order_id=101 AND sku='P1';
SELECT order_id,sku,product_name FROM flat WHERE sku='P1' ORDER BY order_id;

╭──────────┬─────┬──────────────╮
│ order_id │ sku │ product_name │
╞══════════╪═════╪══════════════╡
│      101 │ P1  │ 方眼ノート   │
│      102 │ P1  │ ノート       │
╰──────────┴─────┴──────────────╯

SELECT sku,COUNT(DISTINCT product_name) AS names FROM flat GROUP BY sku HAVING COUNT(DISTINCT product_name)>1;

╭─────┬───────╮
│ sku │ names │
╞═════╪═══════╡
│ P1  │     2 │
╰─────┴───────╯

INSERT INTO flat VALUES(NULL,'P3','消しゴム',NULL);

Error: NOT NULL constraint failed: flat.order_id

BEGIN;
DELETE FROM flat WHERE order_id=102 AND sku='P2';
SELECT COUNT(*) AS p2_rows FROM flat WHERE sku='P2';

╭─────────╮
│ p2_rows │
╞═════════╡
│       0 │
╰─────────╯

ROLLBACK;
UPDATE flat SET product_name='方眼ノート' WHERE sku='P1';
CREATE TABLE products(sku TEXT PRIMARY KEY,name TEXT NOT NULL) STRICT;
INSERT INTO products SELECT DISTINCT sku,product_name FROM flat;
CREATE TABLE lines(order_id INTEGER,sku TEXT REFERENCES products(sku),qty INTEGER,PRIMARY KEY(order_id,sku)) STRICT;
INSERT INTO lines SELECT order_id,sku,qty FROM flat;
SELECT * FROM products ORDER BY sku;

╭─────┬────────────╮
│ sku │    name    │
╞═════╪════════════╡
│ P1  │ 方眼ノート │
│ P2  │ ペン       │
╰─────┴────────────╯

UPDATE products SET name='A5ノート' WHERE sku='P1';
SELECT l.order_id,l.sku,p.name,l.qty FROM lines AS l JOIN products AS p ON p.sku=l.sku ORDER BY l.order_id,l.sku;

╭──────────┬─────┬──────────┬─────╮
│ order_id │ sku │   name   │ qty │
╞══════════╪═════╪══════════╪═════╡
│      101 │ P1  │ A5ノート │   2 │
│      102 │ P1  │ A5ノート │   1 │
│      102 │ P2  │ ペン     │   3 │
╰──────────┴─────┴──────────┴─────╯

INSERT INTO products VALUES('P3','消しゴム');
DELETE FROM lines WHERE sku='P2';
SELECT * FROM products ORDER BY sku;

╭─────┬──────────╮
│ sku │   name   │
╞═════╪══════════╡
│ P1  │ A5ノート │
│ P2  │ ペン     │
│ P3  │ 消しゴム │
╰─────┴──────────╯

CREATE TABLE packed(order_id INTEGER PRIMARY KEY,skus TEXT) STRICT;
INSERT INTO packed VALUES(201,'P1,P2'),(202,'P10');
SELECT * FROM packed WHERE skus LIKE '%P1%' ORDER BY order_id;

╭──────────┬───────╮
│ order_id │ skus  │
╞══════════╪═══════╡
│      201 │ P1,P2 │
│      202 │ P10   │
╰──────────┴───────╯

CREATE TABLE unpacked(order_id INTEGER,sku TEXT,PRIMARY KEY(order_id,sku)) STRICT;
INSERT INTO unpacked VALUES(201,'P1'),(201,'P2'),(202,'P10');
SELECT * FROM unpacked WHERE sku='P1' ORDER BY order_id;

╭──────────┬─────╮
│ order_id │ sku │
╞══════════╪═════╡
│      201 │ P1  │
╰──────────┴─────╯

演習完了