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.

build_count
bom_expansion.sql
Replay: real traced execution (multi-file project)
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 (1)), 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 (2)), 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 (3)), 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;

  1. tables ← 1 row

    1CREATE TABLE bom (parent TEXT, child TEXT, qty INTEGER);2INSERT INTO bom VALUES ('kit', 'frame', 1), ('kit', 'wheel_set', 2), ('wheel_set', 'wheel', 2), ('wheel_set', 'bolt', 4);
    values this step1 rowtables

  2. bom ← 4 rows

    1CREATE TABLE bom (parent TEXT, child TEXT, qty INTEGER);2INSERT INTO bom VALUES ('kit', 'frame', 1), ('kit', 'wheel_set', 2), ('wheel_set', 'wheel', 2), ('wheel_set', 'bolt', 4);3WITH RECURSIVE params(build_count) AS (VALUES (1)), 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;
    values this step4 rowsbom

  3. result ← 4 rows

    2INSERT INTO bom VALUES ('kit', 'frame', 1), ('kit', 'wheel_set', 2), ('wheel_set', 'wheel', 2), ('wheel_set', 'bolt', 4);3WITH RECURSIVE params(build_count) AS (VALUES (1)), 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;
    values this step4 rowsresult

  1. tables ← 1 row

    1CREATE TABLE bom (parent TEXT, child TEXT, qty INTEGER);2INSERT INTO bom VALUES ('kit', 'frame', 1), ('kit', 'wheel_set', 2), ('wheel_set', 'wheel', 2), ('wheel_set', 'bolt', 4);
    values this step1 rowtables

  2. bom ← 4 rows

    1CREATE TABLE bom (parent TEXT, child TEXT, qty INTEGER);2INSERT INTO bom VALUES ('kit', 'frame', 1), ('kit', 'wheel_set', 2), ('wheel_set', 'wheel', 2), ('wheel_set', 'bolt', 4);3WITH RECURSIVE params(build_count) AS (VALUES (2)), 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;
    values this step4 rowsbom

  3. result ← 4 rows

    2INSERT INTO bom VALUES ('kit', 'frame', 1), ('kit', 'wheel_set', 2), ('wheel_set', 'wheel', 2), ('wheel_set', 'bolt', 4);3WITH RECURSIVE params(build_count) AS (VALUES (2)), 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;
    values this step4 rowsresult

  1. tables ← 1 row

    1CREATE TABLE bom (parent TEXT, child TEXT, qty INTEGER);2INSERT INTO bom VALUES ('kit', 'frame', 1), ('kit', 'wheel_set', 2), ('wheel_set', 'wheel', 2), ('wheel_set', 'bolt', 4);
    values this step1 rowtables

  2. bom ← 4 rows

    1CREATE TABLE bom (parent TEXT, child TEXT, qty INTEGER);2INSERT INTO bom VALUES ('kit', 'frame', 1), ('kit', 'wheel_set', 2), ('wheel_set', 'wheel', 2), ('wheel_set', 'bolt', 4);3WITH RECURSIVE params(build_count) AS (VALUES (3)), 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;
    values this step4 rowsbom

  3. result ← 4 rows

    2INSERT INTO bom VALUES ('kit', 'frame', 1), ('kit', 'wheel_set', 2), ('wheel_set', 'wheel', 2), ('wheel_set', 'bolt', 4);3WITH RECURSIVE params(build_count) AS (VALUES (3)), 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;
    values this step4 rowsresult
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.