A comprehensive warehouse inventory management database schema suitable for retail, manufacturing, or distribution businesses. Includes multi-warehouse support, product catalog with SKU tracking, real-time stock movement logging (inbound, outbound, transfer, adjustment), supplier management, and purchase order workflow with line items. Designed to maintain accurate stock levels and provide full traceability of inventory changes across facilities.
There are two answers to how much stock is there, and both are stored. stock_levels is the current quantity per product per warehouse, read on every screen. stock_movements is the append-only log of every change, with a movement_type and a reference back to whatever caused it. The first is fast and can be wrong; the second is slow and cannot, which is why inventory_audits exists to reconcile them.
Quantity is split into quantity and reserved_quantity on the same row. Reserved stock is physically present but already promised, so it must not be counted as available to sell and must not be treated as missing during a count. One number cannot carry both meanings without the application guessing which one it meant.
Receiving is modelled as three separate things because they fail separately. purchase_orders is what was ordered from a supplier, shipments is what is physically in transit with a carrier and tracking number, and purchase_order_items.quantity_received is what actually arrived. A short delivery is a normal state here, not an exception that breaks the order.
Storing only stock_movements is the pure answer — the quantity is a sum — but it makes every product listing a running total over a table that grows forever. Storing only stock_levels makes the screens fast and leaves no way to explain how a number got there. Keeping both means a write touches two places in one transaction, and inventory_audits is the mechanism that catches the moment they disagree.
A reservations table would record who holds what and until when, which is the richer model. One column instead keeps the availability calculation to a single subtraction on the row already being read. The cost is that the schema cannot say who reserved it — if allocations need to be released by owner or expired individually, this column is the thing that has to grow into a table.
Movements are caused by purchases, sales, transfers and corrections, each living in a different table. Columns for each would leave most of them null on every row, and new causes would mean new columns. reference_type with reference_id keeps one shape at the price of no foreign key — nothing stops a dangling reference, so the application owns that integrity.
inventory_audits keeps expected_quantity, actual_quantity and the discrepancy rather than silently correcting stock_levels. The discrepancy is the useful figure: it is how shrinkage, miscounts and receiving errors are found. A correction with no record of what it corrected removes the only evidence that something is going wrong in a zone.