Guide / data model
Inventory management system ER diagram
Eight entities and one invariant cover a real inventory system: products, locations, movements, orders and their lines, suppliers and purchase orders. Each relationship below, with the cardinalities that decide whether the database can tell the truth.
The key facts
- The core entities: Product, ProductVariant, Location, StockMovement, Customer, Order, OrderLine, Supplier, PurchaseOrder, PurchaseOrderLine.
- The one invariant: quantities are derived from StockMovement — no "current stock" column exists anywhere in the diagram.
- The relationships to get right: Movement belongs to exactly one Product and one Location; an Order has many Lines; a Line references one Variant at its order-time price.
- The extension path: batches, reorder levels and reservations attach to this skeleton without restructuring it — the full schema reasoning walks the build.
- The full picture: how the inventory record works is the hub page for everything above.
- 01The key facts
- 02The entities, one by one
- 03The relationships and cardinalities that matter
- 04Extending the diagram without breaking it
The entities, one by one
Product (1)—(many) ProductVariant: the sellable item and its variations — size, pack, colour — each variant carrying its own SKU and price. Ordering happens at variant level; nobody orders "hoodie", they order a hoodie in a size.
Location — where stock physically sits: warehouse, van, showroom. Even a single-site business keeps it as an entity; the first second site should not require a migration.
StockMovement — the centre of the diagram: id, variant reference, location reference, signed quantity, timestamp, actor, reason, and a polymorphic reference to what caused it (order, purchase, adjustment, transfer). It is the only table that changes stock, and it is append-only — corrections are new movements referencing the error.
Customer (1)—(many) Order (1)—(many) OrderLine — each line referencing exactly one ProductVariant, with quantity and the price copied at order time (a later price change must not rewrite history).
Supplier (1)—(many) PurchaseOrder (1)—(many) PurchaseOrderLine — the mirror of the order side; goods receipt against a PO writes inbound movements and closes the loop.
The relationships and cardinalities that matter
The diagram's truth lives in its cardinalities. Movement — Product: many-to-one. Every movement names exactly one variant; a "movement" spanning several products is really several movements. Movement — Location: many-to-one, same rule. Order — OrderLine: one-to-many, and the order's total is derived from its lines, never stored independently — the same derivation discipline as stock.
OrderLine — ProductVariant: many-to-one at a frozen price. StockMovement — Order: many-to-one, optional — a movement may belong to an order (the deduction at invoicing) or to nothing (an adjustment), which is why the reference is nullable rather than split into two tables.
Two derived relationships deserve explicit boxes even though they have no table of their own: current stock is the sum of movements per variant per location, and on-order is the sum of open purchase-order lines. Drawing them as views — not entities — is what keeps the diagram honest; the worked example traces one order through every box.
Extending the diagram without breaking it
Batches: a Batch entity (1)—(many) Movements carry batch references where traceability matters — expiry dates ride on the batch, not the product. ReorderLevel: a small table keyed by variant (and optionally location) joined against derived stock to produce the purchasing signal. Reservations: either a movement type with a reserved flag or a separate table (1)—(many) against Order — the design choice that answers the cancel-after-reserve path. CustomerPrice: a price-list table between Customer and ProductVariant, many-to-many through it, so per-customer pricing needs no product surgery.
The test of the diagram is that none of these extensions rewrites the core: movements stay append-only, quantities stay derived, history stays intact. That is also the test to apply to any system you buy — the LLD companion turns this diagram into code-level design, and BSimple implements the same movement-based model in production, with the trial open to the same inspection.
Frequently Asked Questions
What are the main entities in an inventory management system ER diagram?
Product and ProductVariant, Location, StockMovement, Customer, Order and OrderLine, Supplier, PurchaseOrder and PurchaseOrderLine. StockMovement is the pivot: every quantity in the system derives from it, and it is the only entity that changes stock.
Should current stock be an entity with its own table?
No — draw it as a derived view: the sum of signed movements per variant per location. A stored "current stock" table invites direct edits, loses the audit trail, and becomes the diagram's original sin. Rebuildable projections are the production-grade version of the same rule.
What is the relationship between orders and stock movements?
One-to-many and optional: an order's invoicing writes one (or more, on partial fulfilment) movements per line, but movements also exist for purchases, adjustments and transfers that owe nothing to any order. A nullable polymorphic reference models this cleanly.
How do batches and expiry dates fit the diagram?
As a Batch entity referenced by movements: each batch belongs to a variant, carries its own expiry, and traceability runs through the movement links — from a finished batch back to its inputs. Quantity-only systems skip this entity and lose that answer.
Where can I see this design implemented rather than drawn?
BSimple runs it in production: movement-based stock across locations, orders with deliberate invoice approval, per-customer pricing — the trial opens the real system to the same scrutiny the diagram gets, and the design guide builds it step by step.
BSimple