Why should you care?

Flipkart FC in Bangalore vs Delhi — same SKU, different qty; overselling happens if you only track global stock_qty.

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

CREATE TABLE warehouses (
  wh_id INT UNSIGNED PRIMARY KEY,
  city VARCHAR(60) NOT NULL
);
CREATE TABLE stock_levels (
  product_id INT UNSIGNED NOT NULL,
  wh_id INT UNSIGNED NOT NULL,
  qty_on_hand INT NOT NULL DEFAULT 0,
  PRIMARY KEY (product_id, wh_id)
);
UPDATE stock_levels
SET qty_on_hand = qty_on_hand - 2
WHERE product_id = 10 AND wh_id = 3 AND qty_on_hand >= 2;

What happened?

• Composite PK ties SKU to warehouse.
• UPDATE checks qty_on_hand >= 2 atomically — 0 rows means insufficient stock at that FC.
• Ship-from-nearest-warehouse query JOINs stock_levels to warehouses.

Practice next

1. Create warehouses and stock_levels.
2. Seed Pune and Mumbai qty for same SKU.
3. Run conditional UPDATE for pick-pack.
4. Add stock_movements INSERT trigger on UPDATE.
5. SELECT nearest warehouse with qty > 0 for pincode (app logic + SQL).

Remember

Stock per warehouse with composite PK. Conditional UPDATE prevents negative qty. Movements log for audit trail.

DataFlow WMS lab

Order routes to Mumbai WH because stock_levels shows qty there not Pune.

Outcome: Faster delivery and accurate pick lists.