自由設計
要件から守る条件を選ぶ
最後の設計課題です。通常の列定義をひな形で渡します。このコースの問いを使い、関係・制約・索引を自分で選んでください。
商品にレビューを付けたい。レビューには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 の書き方を覚えることではありません。
迷ったときにこの列に戻れるなら、このコースの目的は達成です。
評価の範囲を守る
BETWEEN は上下の端を含む範囲の条件です。読書評価が0から10の例なら次のように書けます。
SQL クエリ
ALTER TABLE reading_scores ADD CONSTRAINT reading_scores_value_ck
CHECK (score BETWEEN 0 AND 10);演習では5段階の評価です。通常の列やデータ投入は用意済みなので、要件から参照・範囲・一意性・索引を選びます。
手を動かす
レビュー機能を設計し、テーブル・制約・索引まで作り切ります。
レビュー機能を作ってください。通常の列はひな形にあります。関係と業務ルールを上の問いに沿って制約と索引にしてください。
テーブル名は reviews、列は次の6つです。
id integer PRIMARY KEYproduct_id integer NOT NULL→products (id)customer_id integer NOT NULL→customers (id)rating integer NOT NULLbody text NOT NULLcreated_at timestamptz NOT NULL DEFAULT now()
制約は2つです。
reviews_rating_ck—rating BETWEEN 1 AND 5reviews_product_customer_uk—(product_id, customer_id)の一意制約
索引は1本だけ作ります。
reviews_customer_id_idx—customer_id
作ったら、レビューを3件入れて動くことを確かめてください。(1, 1, 1, 5, 'とても良い')、(2, 1, 2, 4, '概ね満足')、(3, 2, 1, 3, 'ふつう') を (id, product_id, customer_id, rating, body) に入れます。
仕上げに、pg_indexes から tablename が 'reviews' の indexname と、外部キー・業務 CHECK・一意制約の合計 rule_count を、indexname の順に取り出してください。
通常の列、データ投入、確認は用意済みです。関係・評価の範囲・重複禁止・検索用の索引を担当します。
ひな形の「ここに学ぶ部分を追記する」を SQL に置き換えます。模範解答では 5 行を補います。確認用の 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 | rule_count |
|---|---|
| reviews_customer_id_idx | 4 |
| reviews_pkey | 4 |
| reviews_product_customer_uk | 4 |
ヒント
まだ開いていません
付箋は 3 枚。下の段ほど答えに近づきます
ヒント 2
ヒント 3答えに近い