Adding a second warehouse changes every query you have

Single-location inventory has one question: how many. Every screen, every API, every report assumes it. Then a second warehouse opens and the honest answer to “how many do we have” becomes “of what use is that number to you”.

Availability is per-location, per-purpose

The same SKU can simultaneously be: 40 in the main warehouse, 6 in a retail location, 12 in transit between them, and 9 reserved against unpicked orders. A single count is a sum that is wrong for every caller.

stock_movements
  sku, location_id, delta, reason, reference, occurred_at

Location on the movement, not on the product. Then “how many” is always qualified — by location, by set of locations, or globally — and the caller has to say which they mean. That is a feature.

Transfers are two movements and a state

A transfer is not an update to a location column. It is stock leaving one place and arriving at another, with a gap in between where it is in neither:

transfer_out   location A   -12   reference: transfer 88
transfer_in    location B   +12   reference: transfer 88

The second row is written on receipt, not on dispatch. Between the two, twelve units exist on the transfer record and in no location — which is exactly true, and exactly what you need when a pallet goes missing between sites.

Writing both at dispatch is the common shortcut. It makes the warehouse that has not received anything yet show stock it cannot pick.

Reservations are not deductions

An order allocated but not yet picked must reduce what is sellable without changing what is physically present. These are different numbers and conflating them means either overselling or a warehouse count that disagrees with the shelf.

Keep allocations in their own table keyed by order and location. Sellable is on-hand minus allocated. On-hand is the movement sum, and it stays true to the physical world.

The cost of retrofitting

Adding location_id later means revisiting every stock query, every report, and the picking logic — and doing it while the data has no history of where anything was. Adding it at the start costs one column and a discipline about always qualifying the question.