中級

中間テーブルで多対多を表現する

← 前へ

問題 1

orders・order_products・productsを結合して、注文ID・商品名・数量・単価を取得してください

読み込み中...

📖 解説参考資料

多対多の関係とは？

1つの注文には複数の商品が含まれ、1つの商品は複数の注文に含まれます。このような多対多（N:M）の関係は、2つのテーブルを直接結びつけることができません。

▼ 中間テーブルで多対多を表現する

orders

注文 (1)

1:N

──

order_products

中間テーブル (N)

order_id / product_id

N:1

──

products

商品 (1)

1つの注文に複数商品／ 1つの商品が複数注文に含まれる

order_products の構造

中間テーブルは「どの注文のどの商品を何個いくらで買ったか」を記録します。

order_products
  id          — 明細ID（主キー）
  order_id    — どの注文か（orders.id への外部キー）
  product_id  — どの商品か（products.id への外部キー）
  quantity    — 数量
  price       — 購入時の単価

💡 なぜ購入時の単価を持つのか？
products.price は後から変わる可能性があります。「注文時点の価格」を記録しておかないと、過去の注文金額が変わってしまいます。これは実務でよくある設計パターンです。

3テーブルを結合して読み解く

SELECT
  u.name AS ユーザー,
  p.name AS 商品,
  op.quantity AS 数量,
  op.price * op.quantity AS 小計
FROM orders o
JOIN users u          ON o.user_id = u.id
JOIN order_products op ON o.id = op.order_id
JOIN products p       ON op.product_id = p.id;

集計への応用

SELECT u.name, SUM(op.price * op.quantity) AS 合計金額
FROM orders o
JOIN users u          ON o.user_id = u.id
JOIN order_products op ON o.id = op.order_id
GROUP BY u.name
ORDER BY 合計金額 DESC;

⚠️ よくある設計ミス：多対多を直接持とうとする
ordersテーブルに product_ids = "1,3,5" のようにカンマ区切りで持とうとするのはアンチパターンです。集計や検索が困難になります。中間テーブルで正規化するのが正しい設計です。

💼 実務での使いどころ
ECサイトの注文詳細・タグシステム（記事とタグ）・権限管理（ユーザーとロール）など、多対多の関係はどんなシステムにも登場します。中間テーブルの仕組みを理解することでDBの設計力が大きく向上します。

← サブクエリ