つくる ER図v2
正規化の結果を図に戻す
第3章で見つけた違反を、第2章で作った ER図に反映します。新しいことは出てきません。
増えるテーブルは3つです。
| テーブル | なぜ生まれたか |
|---|---|
| prefectures | region が pref で決まっていた。3NF |
| categories | カテゴリ名がカテゴリ番号で決まっていた。3NF |
| product_tags | タグが1マスにカンマ区切りだった。1NF |
そして products の category 列は、categories を指す category_id に置き換わります。
ER図v2
erDiagram
prefectures ||--o{ stores : locates
categories ||--o{ products : classifies
products ||--o{ product_tags : tagged_by
stores ||--o{ orders : places
customers ||--o{ orders : makes
stores ||--o{ inventories : has
products ||--o{ inventories : stocked_as
orders ||--|{ order_items : contains
products ||--o{ order_items : appears_in
orders ||--o| shipments : shipped_by線が7本から10本に増えました。正規化はテーブルと線を増やす方向にしか働きません。分ければ分けるほど図は複雑になります。
複雑になったのに、なぜ良いのか
図は複雑になりましたが、1つ1つのテーブルは単純になりました。
stores を見れば店舗のことだけが書いてあります。地域のことも、その店の売上のことも混ざっていません。テーブルを1つ開いたときに理解すべきことが減っています。
設計の複雑さは消えたわけではなく、テーブルの中から、テーブルのあいだへ移動しただけです。ただし、テーブルのあいだの関係は外部キーとしてデータベースが守ってくれます。テーブルの中の暗黙のルールは誰も守ってくれません。守ってもらえる場所に複雑さを移した、というのが正規化の正体です。
主キーが text でもよいのか
prefectures の主キーは pref という日本語の text です。整数の id を振らなくてよいのか、と気になるかもしれません。
都道府県名は変わりませんし、47件しかありません。id を振ると stores から都道府県名を見るのに JOIN が要りますが、text のままなら stores.pref を見るだけで済みます。変わらない値なら、それ自体を主キーにしてよいのです。この判断の基準は次の第4章で正面から扱います。
手を動かす
3つのテーブルを足して、第2版の構造を完成させます。
テーブル構造
CREATE TABLE stores (
id integer PRIMARY KEY,
name text NOT NULL,
pref text NOT NULL,
opened_on date
);
CREATE TABLE products (
id integer PRIMARY KEY,
name text NOT NULL,
price integer NOT NULL,
category text NOT NULL,
is_active boolean NOT NULL
);
CREATE TABLE customers (
id integer PRIMARY KEY,
name text NOT NULL,
email text NOT NULL,
joined_on date NOT NULL
);
CREATE TABLE orders (
id integer PRIMARY KEY,
store_id integer NOT NULL REFERENCES stores (id),
customer_id integer NOT NULL REFERENCES customers (id),
ordered_on date NOT NULL
);
CREATE TABLE order_items (
order_id integer NOT NULL REFERENCES orders (id),
product_id integer NOT NULL REFERENCES products (id),
quantity integer NOT NULL,
unit_price integer NOT NULL,
PRIMARY KEY (order_id, product_id)
);
CREATE TABLE inventories (
store_id integer NOT NULL REFERENCES stores (id),
product_id integer NOT NULL REFERENCES products (id),
quantity integer NOT NULL,
PRIMARY KEY (store_id, product_id)
);
CREATE TABLE shipments (
id integer PRIMARY KEY,
order_id integer NOT NULL UNIQUE REFERENCES orders (id),
carrier text NOT NULL,
shipped_on date
);
期待される出力
| table_count | column_count |
|---|---|
| 10 | 34 |