Manufacturing in ERP: BOM and MRP Module Design

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.

Modeling the Bill of Materials

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.

• Item master: one row per part number, whether it is a raw material, a manufactured sub-assembly, or a finished good, tagged with a make-or-buy flag.
• BOM header: ties a parent item to a BOM version, with an effective-from and effective-to date range so engineering changes do not retroactively rewrite history.
• BOM line: parent BOM header, component item, quantity per unit of parent, unit of measure, and an optional scrap factor for expected yield loss.
• Routing (optional but recommended): the sequence of operations and work centers required to build the parent, used later for capacity planning and labor costing.

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.

Single-Level vs Indented Multi-Level BOMs

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.

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.

Work Orders: The Unit of Production

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.

1. Planned: created by MRP or manually, no stock has moved yet, components are only reserved.
2. Released: materials are allocated, the shop floor has been authorized to start, component stock is soft-committed.
3. In progress: partial quantities may be reported complete, partial component consumption may be recorded.
4. Closed: final quantity completed, remaining component consumption posted, variances calculated and released back to inventory if under-consumed.

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.

Material Requirements Planning: Exploding Demand

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.

Purchase Requisitions and Planned Orders

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.

• Firm planned orders manually before the MRP run so the algorithm treats them as fixed supply and does not suggest duplicates.
• Group planned orders into requisitions by vendor and delivery window to reduce the number of purchase orders a buyer has to process.
• Flag exceptions such as orders that would need to have started in the past, so the planner sees a clear expedite list rather than a silently late plan.

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.

Backflushing: Consuming Stock on Completion

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.

• Backflushing dramatically reduces transaction volume on high-velocity production lines where issuing every component manually would bottleneck the shop floor.
• It trades real-time work-in-process visibility for simplicity, which is the right tradeoff for repetitive manufacturing but a poor one for long-cycle, high-value assemblies where you want to track work in process closely.
• Backflushing still needs a manual override path for scrap and rework, otherwise the automatically calculated consumption silently understates what the shop floor actually used.

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.