Finding all components that comprise a particular assembly’s bill of material is a common MRP question. Obtaining a complete BOM requires finding the immediate children of the assembly, followed by children of the children, and so forth. This is complicated by the fact that we generally do not know the depth of a particular BOM.

The solution is to recursively find children of a component until we’ve reached a component that has no children (a leaf node in tree parlance). Algorithmically, this is referred to as a breadth (BFS) or depth-first search (DFS), depending on the order in which results are reported.

Most database flavors implement this using a recursive common table expression (CTE). Using our Pen bill of materials example, the below SQL query is how a recursive CTE would be implemented in SQLite to find all components of the Upper Barrel subassembly.

-- A depth-first search of the Upper Barrel subassembly
WITH RECURSIVE
recursive_bom(Component, Level) AS (
    -- The root of the tree, i.e. the component whose BOM we want
    VALUES('Upper Barrel', 0)
    UNION ALL
    SELECT bom.Component, recursive_bom.level + 1
    FROM bom JOIN recursive_bom ON bom.Parent = recursive_bom.Component
    ORDER BY recursive_bom.level + 1 DESC
)
-- Add leading periods to help visualize bom depth
SELECT level, printf('%.*c', level * 3, '.') || Component AS Component  FROM recursive_bom

Another important question is how many of each component is required to build a complete assembly. For example, looking at the BOM graph, it can be seen that tubing is required in multiple subassemblies, namely the Inner, Outer, and Tube. To build a complete pen, we would want to know the total amount of tubing required across all components, regardless of bom depth.

Again a recursive query is used, with the added complexity of accumulating all quantities for identical components along the way. Accumulating component quantities is accomplished by the added sum() and corresponding GROUP BY clause in the sql query.

WITH RECURSIVE
recursive_bom(Component, Quantity) AS (
    -- The root of the tree, i.e. the component whose BOM we want
    VALUES('Pen', 1)
    UNION ALL
    SELECT bom.Component, bom.Quantity * recursive_bom.Quantity
    FROM bom JOIN recursive_bom ON bom.Parent = recursive_bom.Component
  )
SELECT Component, sum(Quantity) AS [Total Qty Required] FROM recursive_bom
GROUP BY Component

Thus, in order to build 1 Pen assembly, a total of 18 Tubing is required. To understand this, we can examine all components that require Tubing in their BOM.

SELECT * FROM bom WHERE Component IN ('Tubing')

Tracing the ancestry of each parent all the way to the top level Pen:

• Inner 5 x 2 x 1 = 10 Tubing
  • 5 Tubing is required for each Inner subassembly.
  • 2 Inners are required for each Lower Pen.
  • 1 Lower Pen is required for each Pen.
• Outer 3 x 2 x 1 = 6 Tubing)
  • 3 Tubing is required for each Outer subassembly.
  • 2 Outers are required for each Lower Pen.
  • 1 Lower Pen is required for each Pen.
• Tube 2 x 1 x 1 x 1 = 2 Tubing
  • 2 Tubing is required for each Tube.
  • 1 Tube is required for each Sleeve.
  • 1 Sleeve is required for each Upper Barrel.
  • 1 Upper Barrel is required for each Pen.
• Grand total of 18 Tubing is required per Pen assembly.