つくる ER図v2
正規化の結果を図に戻す
第3章で見つけた違反を、第2章で作った ER図に反映します。新しいことは出てきません。
増えるテーブルは3つです。
| テーブル | なぜ生まれたか |
|---|---|
| prefectures | region が pref で決まっていた。3NF |
| categories | カテゴリ名がカテゴリ番号で決まっていた。3NF |
| product_tags | タグが1マスにカンマ区切りだった。1NF |
そして products の category 列は、categories を指す category_id に置き換わります。
ER図v2
線が7本から10本に増えました。正規化はテーブルと線を増やす方向にしか働きません。分ければ分けるほど図は複雑になります。
複雑になったのに、なぜ良いのか
図は複雑になりましたが、1つ1つのテーブルは単純になりました。
stores を見れば店舗のことだけが書いてあります。地域のことも、その店の売上のことも混ざっていません。テーブルを1つ開いたときに理解すべきことが減っています。
設計の複雑さは消えたわけではなく、テーブルの中から、テーブルのあいだへ移動しただけです。ただし、テーブルのあいだの関係は外部キーとしてデータベースが守ってくれます。テーブルの中の暗黙のルールは誰も守ってくれません。守ってもらえる場所に複雑さを移した、というのが正規化の正体です。
主キーが text でもよいのか
prefectures の主キーは pref という日本語の text です。整数の id を振らなくてよいのか、と気になるかもしれません。
都道府県名は変わりませんし、47件しかありません。id を振ると stores から都道府県名を見るのに JOIN が要りますが、text のままなら stores.pref を見るだけで済みます。変わらない値なら、それ自体を主キーにしてよいのです。この判断の基準は次の第4章で正面から扱います。
テーブル数と列数を読む
DISTINCT は集計関数の中でも使えます。
SQL クエリ
SELECT count(DISTINCT table_name) AS tables, count(*) AS columns
FROM information_schema.columns
WHERE table_schema = 'public';1表には列の数だけ行があるので、count(*) は全列数です。重複する table_name を除いて数えると表の数になります。演習ではこの確認を用意してあります。
手を動かす
3つのテーブルを足して、第2版の構造を完成させます。
第2章までの7テーブルがあります。正規化の結果を反映してください。
prefecturesを作る。pref text PRIMARY KEY、region text NOT NULLcategoriesを作る。id integer PRIMARY KEY、name text NOT NULLproduct_tagsを作る。product_id integer NOT NULL、tag text NOT NULL、主キーは(product_id, tag)、外部キーproduct_tags_product_fkがproducts (id)を参照するproductsのcategory列を消し、category_id integer NOT NULLを足す。外部キーproducts_category_fkがcategories (id)を参照するstoresに外部キーstores_pref_fkを足す。prefがprefectures (pref)を参照する
仕上げに、information_schema から table_schema が 'public' のテーブル数を table_count、列の総数を column_count として1行で取り出してください。テーブルは10、列は34になります。
マスタ表とタグ表の通常の列は用意済みです。関係を制約に写し、カテゴリ名を id に置き換えます。 ひな形の「ここに学ぶ部分を追記する」を SQL に置き換えます。模範解答では 5 行を補います。確認用の SQL は用意してあります。
テーブル構造
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 |
ヒント
まだ開いていません
付箋は 3 枚。下の段ほど答えに近づきます
ヒント 2
ヒント 3答えに近い