自由設計
今度は手順だけ渡す
最後の設計課題です。要件は渡しますが、テーブルの形も制約も索引も指定しません。このコースでやってきた手順を、自分で回してください。
商品にレビューを付けたい。レビューには5段階の評価と本文がある。誰がいつ書いたかを残す。1人の顧客が同じ商品に書けるレビューは1件までにしたい。商品ページではその商品のレビューを表示し、マイページではその顧客が書いたレビューを表示する。
自分に問う順番
第1章から第6章までの問いを、順に自分に投げます。
エンティティは何か。 レビューです。評価・本文・書いた日時はその属性です。
関係はどうか。 1つの商品に複数のレビュー、1人の顧客も複数のレビュー。つまり商品と顧客の多対多で、レビュー自身が属性を持つ中間テーブルの形になります。外部キーは多の側、つまりレビューに置きます。
正規化されているか。 商品名や顧客名をレビューに持たせたくなりますが、それは他のテーブルにある事実の写しです。持たせません。
主キーは。 サロゲートキーの id にします。(product_id, customer_id) は業務上一意ですが、主キーではなく UNIQUE で守ります。
NULL は。 評価も本文も、レビューを書く時点で必ず決まっています。すべて NOT NULL です。
CHECK は。 5段階なので rating BETWEEN 1 AND 5 です。範囲を型では守れないので制約が要ります。
索引は。 商品ページの検索は product_id、マイページの検索は customer_id です。(product_id, customer_id) の一意制約が左端で product_id を兼ねるので、必要なのは customer_id 単独の索引だけです。
この問いの列が設計そのもの
7つの問いに答え終わると、DDL はもう書けています。設計とは、この順番で問いを立てられることであって、SQL の書き方を覚えることではありません。
迷ったときにこの列に戻れるなら、このコースの目的は達成です。
手を動かす
レビュー機能を設計し、テーブル・制約・索引まで作り切ります。
テーブル構造
CREATE TABLE customers (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE products (
id integer PRIMARY KEY,
name text NOT NULL,
price integer NOT NULL
);
INSERT INTO customers VALUES (1, '田中あかり'), (2, '佐藤けんじ');
INSERT INTO products VALUES (1, 'ノートPC', 128000), (2, 'マウス', 3200);
期待される出力
| indexname |
|---|
| reviews_customer_id_idx |
| reviews_pkey |
| reviews_product_customer_uk |