Why should you care?

A Flipkart order can have many coupons; one coupon applies to many orders over time — cannot store both IDs in a single column cleanly.

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

CREATE TABLE order_coupons (
  order_id  INT UNSIGNED NOT NULL,
  coupon_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (order_id, coupon_id),
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (coupon_id) REFERENCES coupons(coupon_id)
);

What happened?

• Composite primary key stops duplicate pairings.
• Each row is one link.
• Queries JOIN orders → order_coupons → coupons to list codes used.

Practice next

1. Create coupons table and order_coupons bridge.
2. Insert two orders sharing one coupon.
3. SELECT order_ref, coupon_code via double JOIN.
4. Add applied_at TIMESTAMP to bridge for audit.
5. COUNT coupons per order with GROUP BY.

Remember

Bridge table holds pairs of FKs. Composite PK enforces uniqueness. Query through two JOINs.

DataFlow festival coupons

Single DIWALI50 code tracked across thousands of orders via junction table.

Outcome: Finance reconciles discount spend accurately.