Recursive Patterns in Practice

Bill of Materials

Multiply Requirements

Recursive expansion can calculate component quantities through nested assemblies.

Program

Play the script to choose how many kits are built and expand the required parts.

bom_expansion.sql 
CREATE TABLE bom (parent TEXT, child TEXT, qty INTEGER);
INSERT INTO bom VALUES ('kit', 'frame', 1), ('kit', 'wheel_set', 2), ('wheel_set', 'wheel', 2), ('wheel_set', 'bolt', 4);
WITH RECURSIVE params(build_count) AS (VALUES ()), parts(part, required_qty) AS (SELECT child, qty * (SELECT build_count FROM params) FROM bom WHERE parent = 'kit' UNION ALL SELECT b.child, parts.required_qty * b.qty FROM bom AS b JOIN parts ON b.parent = parts.part) SELECT part, SUM(required_qty) AS total_qty FROM parts GROUP BY part ORDER BY part;

CREATE TABLE bom (parent TEXT, child TEXT, qty INTEGER);
INSERT INTO bom VALUES ('kit', 'frame', 1), ('kit', 'wheel_set', 2), ('wheel_set', 'wheel', 2), ('wheel_set', 'bolt', 4);
WITH RECURSIVE params(build_count) AS (VALUES ()), parts(part, required_qty) AS (SELECT child, qty * (SELECT build_count FROM params) FROM bom WHERE parent = 'kit' UNION ALL SELECT b.child, parts.required_qty * b.qty FROM bom AS b JOIN parts ON b.parent = parts.part) SELECT part, SUM(required_qty) AS total_qty FROM parts GROUP BY part ORDER BY part;

CREATE TABLE bom (parent TEXT, child TEXT, qty INTEGER);
INSERT INTO bom VALUES ('kit', 'frame', 1), ('kit', 'wheel_set', 2), ('wheel_set', 'wheel', 2), ('wheel_set', 'bolt', 4);
WITH RECURSIVE params(build_count) AS (VALUES ()), parts(part, required_qty) AS (SELECT child, qty * (SELECT build_count FROM params) FROM bom WHERE parent = 'kit' UNION ALL SELECT b.child, parts.required_qty * b.qty FROM bom AS b JOIN parts ON b.parent = parts.part) SELECT part, SUM(required_qty) AS total_qty FROM parts GROUP BY part ORDER BY part;
bill of materials Each row says how many child parts one parent assembly needs.
quantity multiplication The recursive step multiplies nested quantities as it descends.
rollup `SUM(required_qty)` combines requirements for repeated parts.