一気通貫設計
新しい要件が来た
最後の章では、このコースで身に付けたものを全部使います。通常の列定義は用意し、業務ルールを制約に落とす判断に集中します。
受け取った要件は次のとおりです。
クーポンを配って、注文のときに使えるようにしたい。クーポンにはコードと割引額と有効期限がある。同じクーポンを複数の顧客が使えるが、1人の顧客が同じクーポンを使えるのは1回だけにしたい。どの注文でいくら割り引いたかは、あとから確認できるようにしておきたい。
手順は第1章から変わらない
進め方は最初に決めたとおりです。
1. 名詞を拾う。クーポン、コード、割引額、有効期限、顧客、注文、割り引いた額。
2. 実体と属性に分ける。クーポンは実体です。コード・割引額・有効期限はその属性です。顧客と注文はすでにある実体です。
3. 関係を見る。ここが今回の肝です。1つのクーポンを複数の顧客が使い、1人の顧客が複数のクーポンを使えます。多対多です。
第2章で見たとおり、多対多は中間テーブルで表します。名前は coupon_usages、クーポンの利用実績です。
中間テーブルに属性が付く
「いくら割り引いたか」は、クーポンの属性でも注文の属性でもありません。その利用1回ごとに決まる事実です。だから中間テーブルの列になります。
注文明細の unit_price と同じ形です。多対多の中間テーブルは、たいてい自分の属性を持ちます。
業務ルールを制約に落とす
要件文の中に、制約に落とせる文が3つ隠れています。
| 要件の文 | 制約 |
|---|---|
| コードは配る前に一意でなければ困る | coupons (code) に UNIQUE |
| 1人が同じクーポンを使えるのは1回だけ | (coupon_id, customer_id) に UNIQUE |
| 1つの注文に使えるクーポンは1枚 | (order_id) に UNIQUE |
3つ目は要件文に直接書かれていません。書かれていないことに気づいて確認するのも設計の仕事です。今回は1枚だけという前提で進めます。
割引額が0円や負の数では意味がないので、CHECK も1本足します。
用意済みの確認を読む
制約数を表ごとに添える副問い合わせでは、外側の表名と対応させます。
SQL クエリ
SELECT c.table_name, count(*) AS column_count,
(SELECT count(*) FROM information_schema.table_constraints t
WHERE t.table_name = c.table_name AND t.constraint_type = 'FOREIGN KEY') AS fk_count
FROM information_schema.columns c
WHERE c.table_name IN ('libraries', 'borrowings')
GROUP BY c.table_name
ORDER BY column_count, c.table_name;t.table_name と c.table_name の一致で、その表の制約だけを数えます。列数だけでは関係や重複禁止の抜けが分からないので、演習の確認には業務制約の件数も添えます。
手を動かす
要件から2つのテーブルを設計し、制約まで含めて作ります。
要件からクーポンの2テーブルを作ってください。customers と orders はすでにあります。
coupons
id integer PRIMARY KEYcode text NOT NULLdiscount_amount integer NOT NULLexpires_on date NOT NULLcoupons_code_uk—codeの一意制約coupons_discount_ck—discount_amount > 0
coupon_usages
id integer PRIMARY KEYcoupon_id integer NOT NULL→coupons (id)order_id integer NOT NULL→orders (id)customer_id integer NOT NULL→customers (id)discounted_amount integer NOT NULLcoupon_usages_order_uk—order_idの一意制約coupon_usages_customer_uk—(coupon_id, customer_id)の一意制約
仕上げに、information_schema から table_name が 'coupons' か 'coupon_usages' の table_name と列数 column_count、外部キー・一意制約・業務 CHECK の合計 rule_count を、列数の少ない順に取り出してください。
両表の通常の列は用意済みです。3本の外部キー、3本の一意制約、割引額の CHECK を担当します。一意制約と CHECK を同じ行に並べることで5行に収まります。
ひな形の「ここに学ぶ部分を追記する」を SQL に置き換えます。模範解答では 5 行を補います。確認用の SQL は用意してあります。
テーブル構造
CREATE TABLE customers (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE orders (
id integer PRIMARY KEY,
customer_id integer NOT NULL REFERENCES customers (id),
total integer NOT NULL
);
INSERT INTO customers VALUES (1, '田中あかり'), (2, '佐藤けんじ');
INSERT INTO orders VALUES (1, 1, 134400), (2, 2, 24000);
期待される出力
| table_name | column_count | rule_count |
|---|---|---|
| coupons | 4 | 2 |
| coupon_usages | 5 | 5 |
ヒント
まだ開いていません
付箋は 3 枚。下の段ほど答えに近づきます
ヒント 2
ヒント 3答えに近い