一気通貫設計
新しい要件が来た
最後の章では、このコースで身に付けたものを全部使います。助言は最小限にします。
受け取った要件は次のとおりです。
クーポンを配って、注文のときに使えるようにしたい。クーポンにはコードと割引額と有効期限がある。同じクーポンを複数の顧客が使えるが、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本足します。
手を動かす
要件から2つのテーブルを設計し、制約まで含めて作ります。
テーブル構造
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 |
|---|---|
| coupons | 4 |
| coupon_usages | 5 |