ERP
Manufacturing in ERP: BOM and MRP Module Design
July 202611 min read
Photo by Robert Scoble on flickr
A single-level bill of materials lists only the direct components of one parent item, one level deep. A multi-level, or indented, BOM recursively expands every sub-assembly down to raw materials, multiplying quantities through each level so you see the full production tree. Both views are generated from the same underlying BOM line table; the difference is purely in how the query walks the hierarchy.
If a work order references the live BOM header and engineering revises the recipe while production is in progress, the change silently rewrites what a half-finished run is supposed to consume. Snapshotting the exploded BOM lines into a work-order-specific table at release time preserves the exact recipe that order was built against, which keeps cost variance reporting and audit trails accurate months later.
MRP starts from independent demand, meaning sales orders and forecasts for finished goods, and explodes it level by level through the bill of materials to generate dependent demand for every component. At each level it nets gross requirements against on-hand and on-order stock, then offsets the resulting planned order backward in time by that item's lead time, producing a time-phased plan of purchase requisitions and work orders.
Technically yes, but it is a poor design choice. A well-built MRP module generates planned orders as advisory suggestions rather than firm commitments, letting a planner review, group, and firm them into actual purchase requisitions or work orders. Fully automatic ordering tends to over-order the moment a forecast turns out to be wrong, since there is no human checkpoint to catch it.
Backflushing automatically calculates and posts component consumption when a work order reports a completed quantity, based on the BOM quantity per multiplied by the amount completed, instead of recording each component issue as it happens. It is the right tradeoff for high-velocity, repetitive manufacturing where issuing every component manually would bottleneck the shop floor, but it should still support a manual override for scrap and rework, and it is a poorer fit for long-cycle, high-value assemblies where close work-in-process visibility matters more than transaction volume.
Photo by Robert Scoble on flickr
Most ERP modules I have built are transactional: an invoice gets approved, a leave request gets routed, a payment gets recorded. Manufacturing is different. It is recursive by nature, because a finished good is built from sub-assemblies, which are built from other sub-assemblies, which are built from raw materials. Getting the data model wrong at the bill of materials layer does not just cost you a bad report later, it corrupts every planning run, every cost roll-up, and every work order downstream for as long as the module stays in production.
This post walks through how I designed the bill of materials and material requirements planning modules for a mid-size manufacturer, covering the schema for multi-level BOMs, how a work order consumes and produces stock, how an MRP run turns a sales forecast into a purchase and production plan, and why backflushing on completion is usually the right tradeoff over transaction-by-transaction issuing.
A bill of materials is a recursive parent-child structure: an item record for the parent, and a set of BOM line records pointing at component items, each with a quantity per unit of parent produced. The trap most first-time ERP builders fall into is trying to flatten this into a single table with fixed columns for component one, component two, and so on. That collapses the moment a product has more than a handful of ingredients, and it makes multi-level explosion effectively impossible to query.
Keeping BOM header and BOM line separate from the item master, and versioning the header by effective date, means you can answer both plan-level questions such as, what does this product cost to build today, and audit-level questions such as, what did this product actually consume when the work order closed six months ago.
Store quantity per as a decimal with at least four places of precision. Rounding a component ratio like 0.0625 kilograms per unit to two decimal places compounds into a real inventory variance once you multiply it across a production run of ten thousand units.
A single-level BOM lists only the direct children of one parent item. An indented, or multi-level, BOM recursively expands every sub-assembly down to raw materials, showing the full tree with quantities multiplied through each level. Both views come from the same underlying BOM line table; the difference is purely in the query.
| View | What it shows | Primary use case |
|---|---|---|
| Single-level BOM | Direct components of one parent, one level deep | Purchasing a specific sub-assembly, quick cost checks |
| Indented multi-level BOM | Full recursive tree, quantities multiplied through each level | Engineering review, full where-used analysis |
| Where-used (reverse BOM) | Every parent that consumes a given component, walking upward | Impact analysis before discontinuing or changing a part |
In PostgreSQL, a recursive common table expression is the natural way to walk the tree in either direction. Going downward from a finished good to raw materials, you join BOM line to itself on parent item equals component item of the previous row; going upward for a where-used report, you flip the join direction. Cap the recursion depth defensively, because a data entry mistake that makes an item its own indirect component will otherwise loop forever.
A work order is the record that ties a BOM and routing to an actual production run: build this quantity of this item, by this date, consuming these components. It moves through a small state machine as production proceeds.
Every work order should carry a snapshot of the BOM lines it was built against, not a live pointer to the current BOM. If engineering revises the recipe next month, work orders already in progress must keep consuming the recipe they were released under, otherwise your cost variance reporting becomes meaningless.
Never let a work order's component list point directly at the live BOM header. Snapshot the exploded BOM lines into a work-order-specific table the moment the order is released. Otherwise a mid-month engineering change silently rewrites what a half-finished production run is supposed to consume.
MRP takes independent demand, sales orders and forecasts for finished goods, and converts it into dependent demand for every component underneath, offset backward in time by lead time. The classic algorithm, formalized by Joseph Orlicky in the 1970s, runs level by level: for each item, calculate gross requirements, subtract what is already on hand or already on order to get net requirements, and if net requirements are positive, generate a planned order due by the required date and offset earlier by that item's lead time. Then push that planned order's quantity down as gross requirements for its own components, and repeat.
function explodeMrp(item, requiredQty, dueDate, onHand) {
const gross = requiredQty
const available = onHand.get(item.sku) ?? 0
const net = Math.max(gross - available, 0)
if (net === 0) return []
const orderDate = subtractLeadTime(dueDate, item.leadTimeDays)
const workOrder = { sku: item.sku, qty: net, dueDate, orderDate }
const children = []
for (const line of item.bomLines) {
const childQty = net * line.qtyPer * (1 + line.scrapFactor)
children.push(
...explodeMrp(line.component, childQty, orderDate, onHand)
)
}
return [workOrder, ...children]
}The recursive function below sketches the core of the explosion. In a real implementation you would also need to account for lot sizing rules, since ordering exactly the net requirement is rarely practical when a supplier has a minimum order quantity or you want to batch production in fixed lot sizes, and you would separate make items, which generate work orders, from buy items, which generate purchase requisitions.
The output of an MRP run is a set of planned orders, some of which become purchase requisitions for buy items and some of which become work orders for make items. A well-designed MRP module never writes directly to a firm purchase order or work order; it generates a planned order that a planner reviews and firms up, because a fully automatic system that fires purchase orders without human review tends to over-order the moment a forecast is wrong.
This human-in-the-loop pattern is what separates a usable MRP module from a spreadsheet that technically does the same math. Planners trust a system that surfaces exceptions and lets them firm up commitments, far more than one that silently commits capital on their behalf.
Treat every MRP run as advisory output, not a transaction. Re-running MRP overnight should be safe to do every single day without side effects, because it only regenerates planned orders and never touches firmed purchase orders or released work orders.
There are two ways to record component consumption against a work order. Forward flushing issues each component to the work order as it is physically pulled from the stockroom, giving real-time visibility into shop floor inventory but requiring a transaction for every pick. Backflushing instead waits until the work order reports a completed quantity, then automatically calculates and posts the component consumption based on the BOM quantity per, multiplied by the quantity completed.
The pattern that holds all of this together is treating the BOM as the single source of truth for what should be consumed, the work order as the record of what was actually consumed, and MRP as the planning layer that turns forecasted demand into a time-phased plan against both. Get those three roles cleanly separated in your schema, and the reporting, costing, and planning features you build on top of them stay coherent even as the product catalog grows into thousands of parts across dozens of levels.