Inventory Management Database Schema database schema

What this schema models

A warehouse inventory management database schema built around one idea that most stock systems get wrong: stock_movements is an append-only ledger and it is the ONLY source of truth. 16 tables, 7 enums and 29 relationships covering suppliers, warehouses, bin-level locations, a product catalog, live stock levels, reorder rules, purchasing, receiving, inter-warehouse transfers and cycle counts. THE LEDGER PATTERN stock_items.quantity_on_hand is a cached running total kept for fast reads — it can always be rebuilt by summing the ledger. Every other table here (goods receipts, transfers, cycle counts) ultimately just writes movement rows with a signed quantity and a reference back to the document that caused them. That is the difference between stock you can audit and stock that is merely current. When the numbers disagree with the shelf, you can prove where they diverged. WHAT IT MODELS THAT SIMPLER EXAMPLES SKIP - Bin-level locations, not just warehouses. Stock is held at an aisle/rack/shelf location, so a pick list can name an exact bin, and locations carry a type (receiving, storage, picking, packing, quarantine, shipping). - Available to promise. quantity_on_hand minus quantity_reserved, both derived, never hand-edited. - Partial receiving. One purchase order can be received across several deliveries, which is why goods_receipts is its own table rather than a set of columns on the PO. Receipt lines separate quantity_accepted from quantity_rejected — only accepted stock writes a positive movement. - Stock in transit. A transfer writes the negative movement when it ships and the positive one when it lands. The gap between them is visible on purpose, and a shortfall against quantity_sent is shrinkage you can alert on. - Cycle counts as the only sanctioned way to change stock with no business event behind it. expected_quantity is frozen when the count sheet is generated; approving a variance writes an adjustment movement. - Purchase order lines copy sku and description rather than joining live, so renaming a product never rewrites what an old PO says was ordered. - Money in minor units as integers. Never floats. WHAT YOU GET Clean DBML that imports straight into DB Planner, plus a laid-out board with cards colour-coded by lifecycle stage — partners, facilities, catalog, stock state, purchasing, receiving, transfers and counts — with sticky notes explaining the reasoning behind each zone and every table carrying its own Note. Postgres-flavoured types that port cleanly to MySQL or SQL Server. Good starting point for developers building a WMS or inventory backend, students who need a realistic inventory management ER diagram, and ops teams outgrowing a spreadsheet.

Tables (16)

Relationships (29)

• goods_receipt_lines.goods_receipt_id references goods_receipts.id many to one
• goods_receipt_lines.location_id references locations.id many to one
• goods_receipt_lines.purchase_order_line_id references purchase_order_lines.id many to one
• goods_receipts.purchase_order_id references purchase_orders.id many to one
• goods_receipts.warehouse_id references warehouses.id many to one
• locations.warehouse_id references warehouses.id many to one
• product_categories.parent_id references product_categories.id many to one
• products.category_id references product_categories.id many to one
• products.preferred_supplier_id references suppliers.id many to one
• purchase_order_lines.product_id references products.id many to one
• purchase_order_lines.purchase_order_id references purchase_orders.id many to one
• purchase_orders.supplier_id references suppliers.id many to one
• purchase_orders.warehouse_id references warehouses.id many to one
• reorder_rules.product_id references products.id many to one
• reorder_rules.warehouse_id references warehouses.id many to one
• stock_count_lines.location_id references locations.id many to one
• stock_count_lines.product_id references products.id many to one
• stock_count_lines.stock_count_id references stock_counts.id many to one
• stock_counts.warehouse_id references warehouses.id many to one
• stock_items.location_id references locations.id many to one
• stock_items.product_id references products.id many to one
• stock_movements.location_id references locations.id many to one
• stock_movements.product_id references products.id many to one
• stock_transfer_lines.from_location_id references locations.id many to one
• stock_transfer_lines.product_id references products.id many to one
• stock_transfer_lines.stock_transfer_id references stock_transfers.id many to one
• stock_transfer_lines.to_location_id references locations.id many to one
• stock_transfers.from_warehouse_id references warehouses.id many to one
• stock_transfers.to_warehouse_id references warehouses.id many to one

How to use this template

1. Open the diagram to see the tables and how they relate.
2. Copy it into your workspace. You get your own editable copy.
3. Export it as PostgreSQL, MySQL, or MSSQL DDL, or as C# models.

Frequently asked questions

What is in the Inventory Management Database Schema schema?

16 tables and 29 relationships, across 126 columns. The table names and the full relationship list are shown on this page.

Can I export it to PostgreSQL or MySQL?

Yes. Every DB Planner template exports as DBML, PostgreSQL, MySQL, or MSSQL DDL, and as C# models.

Is this template free?

Yes. You can open it in the browser without an account, and copy it into your own workspace with a free DB Planner account.

Can I change it after copying it?

Yes. A copy is yours: you can rename tables, add columns, and export it. Changes the author makes later do not affect your copy.