つくる ER図v1
ER図の第1版
新しいことは出てきません。ここまでに決めた線をすべて引いて、多店舗ECの ER図を1枚にまとめます。
7本の線があります。それぞれの記号を両方向から読んで、意味を確かめてください。
線の数を数える
この図から、外部キーが何本になるかは数えられます。
| テーブル | 外部キー |
|---|---|
| orders | store_id、customer_id の2本 |
| inventories | store_id、product_id の2本 |
| order_items | order_id、product_id の2本 |
| shipments | order_id の1本 |
合計7本です。図の線の数と外部キーの本数は必ず一致します。一致しなければ、図か DDL のどちらかが間違っています。この数え合わせは、設計をレビューするときの一番簡単な検算になります。
図に無い線は引かない
実装していると「ここも繋げておくと便利そう」と思う場面が出てきます。たとえば order_items に store_id を足せば、店舗別の売上が JOIN 1回で出せます。
しかしその店舗は orders.store_id を辿れば分かります。同じ事実が2か所に書かれるので、片方だけ書き換わる余地が生まれます。第1章の sales_flat と同じ構造です。
便利さのために線を足すのは、正規化を崩す判断です。やるなら意識してやることで、それは第3章の最後で扱います。いまは図のとおりに作ります。
第2章の成果物
これで、要件文から ER図、ER図から DDL まで一本の線で繋がりました。ただし、いまのテーブルにはまだ大きな穴があります。sales_flat のような冗長さが、形を変えて残っているかもしれません。それを見つけて直すのが第3章です。
手を動かす
残り3テーブルを作り、外部キーが7本揃っていることを数えて確かめます。
stores、products、customers、orders はすでにあります。ER図の残り3テーブルを作ってください。外部キーの制約名も指定します。
inventories—store_id、product_id、quantityの3列すべてinteger NOT NULL、主キーは(store_id, product_id)、外部キーはinventories_store_fkとinventories_product_fkorder_items—order_id、product_id、quantity、unit_priceの4列すべてinteger NOT NULL、主キーは(order_id, product_id)、外部キーはorder_items_order_fkとorder_items_product_fkshipments—id integer PRIMARY KEY、order_id integer NOT NULL UNIQUE、carrier text NOT NULL、shipped_on date、外部キーはshipments_order_fk
作ったら、information_schema から constraint_type が 'FOREIGN KEY' のものをテーブルごとに数え、table_name と件数を fk_count という名前で table_name の順に取り出してください。合計が7本になるはずです。
列と主キー・一意制約は用意済みです。ER図に対応する5本の外部キーを担当します。
外部キーは、表の終わりに 1 本 1 行で、名前を付けて書きます。形は CONSTRAINT 名前 FOREIGN KEY (列) REFERENCES 表 (列) です。列の後ろに REFERENCES だけを書く形では、名前が付けられません。見本は「スキーマ」のタブの orders の表の終わりにある 2 行 (CONSTRAINT orders_store_fk FOREIGN KEY (store_id) REFERENCES stores (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,
customer_id integer NOT NULL,
ordered_on date NOT NULL,
CONSTRAINT orders_store_fk FOREIGN KEY (store_id) REFERENCES stores (id),
CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id)
);
期待される出力
| table_name | fk_count |
|---|---|
| inventories | 2 |
| order_items | 2 |
| orders | 2 |
| shipments | 1 |
ヒント
まだ開いていません
付箋は 3 枚。下の段ほど答えに近づきます
ヒント 2
ヒント 3答えに近い