多対多と中間テーブル

多対多はそのままでは作れない

注文と商品の関係を両方向から聞いてみます。

• 1件の注文に、商品はいくつ含まれるか → 1つ以上
• 1つの商品は、いくつの注文に含まれるか → 0件以上

両方が「多」なので多対多です。ところが、この関係はそのままではテーブルに置けません。

前回のとおり、外部キーは多の側に置きます。しかし両方が多なので、置き場所が決まりません。orders に product_id を置けば1注文1商品になってしまうし、products に order_id を置けば1商品1注文になってしまいます。

2つの1対多に分解する

解決策は、あいだにテーブルを1つ挟むことです。これを中間テーブルと呼びます。

order_items は「どの注文に、どの商品が、何個」を1行で表します。多対多が、2つの1対多に分解されました。外部キーはどちらも多の側、つまり order_items に置かれます。

多対多を見つけたら中間テーブルを作る。これは考えるまでもなく機械的に決まります。

中間テーブルは、ただの繋ぎではない

ここが大事なところです。order_items には quantity と unit_price が入ります。

「この注文でこの商品を3個買った」という個数は、注文だけの性質でも商品だけの性質でもありません。組み合わせに対して初めて意味を持つ値です。こういう値の置き場所は中間テーブルしかありません。

unit_price も同じです。商品の現在価格は products.price にありますが、注文したときの価格は別物です。あとで値上げしたときに過去の注文金額が変わってしまっては困るので、注文の時点の価格を order_items に写し取ります。

一見すると同じ値を2か所に書いているように見えますが、これは冗長ではありません。「いまの定価」と「そのとき売った値段」は、別の事実だからです。

主キーは組み合わせ

同じ注文に同じ商品が2行あると、どちらが本当の個数か分からなくなります。PRIMARY KEY (order_id, product_id) と複合主キーにして防ぎます。

手を動かす

order_items を作って、注文明細を入れ、注文ごとの合計金額を出します。