Glaze BOM SQL

Ivan Shamin · GitHub · LinkedIn · MIT licence

These are problems from my work as the only data person at a plant that makes ceramic frits and glazes: multi-level recipes, glaze chemistry, raw-material prices and supplier changes. The SQL is written for this repository and runs on a small synthetic data set, so it can be public. It is a companion to melt-shop-sql, which is about the furnace. This one is about what goes into it.

The repository answers one question from purchasing: a supplier offers a cheaper feldspar, so how much would we save? To answer it, the SQL has to know where the material is used at every level of the bill of materials, what a swap does to the chemistry of every product it reaches, and what the material costs now. That takes five files.

Dialect: MySQL 8.0.17+ · Data: synthetic, plain SQL inserts, 320 rows, no generator · Runs in: about 2 seconds

Contents

#	File	Question it answers	Techniques
01	01_schema.sql	What does the data look like?	one item master for all BOM levels, self-referencing BOM, CHECK constraints, validity periods, rules as data
02	02_seed_data.sql	17 raw materials, 5 frits, 8 glazes, prices 2023–2026	small enough to read, four problems planted on purpose
03	03_master_data_checks.sql	Is the master data complete?	EXISTS / NOT EXISTS, HAVING, the silent INNER JOIN
04	04_bom_explosion.sql	How much of each raw material is in a tonne of product, through every level?	WITH RECURSIVE, yield inside the recursion, cycle guard, dependent demand, ROLLUP
05	05_chemistry.sql	What is each product made of, in oxides?	matrix multiplication as JOIN + GROUP BY, window normalisation, pivot, piecewise coefficients
06	06_prices_and_costs.sql	What does a tonne cost, why did it move, where does the money go?	validity periods (SCD type 2 style), LEAD, as-of join, month-end price grid, cost decomposition, ABC analysis
07	07_substitution_what_if.sql	Which offer saves money without changing the products?	scenarios as rows, where-used impact, baseline comparison without FULL OUTER JOIN, rules as data

Difficulty grows from 03 (plain joins and grouping) to 04 and 07 (recursion and scenario modelling). The files run in order: 04, 05 and 06 create the views that later files use.

flowchart LR
    L[recipe_lines<br/>+ scenario_swaps] --> SL[v_scenario_lines] --> X[v_explosion<br/>recursive]
    X --> C[v_composition] --> S[v_seger] --> P[v_properties]
    X --> COST[costs, requirements, ABC]
    PR[price_list + fx_rates] --> MP[v_monthly_price] --> COST
    P --> W[v_whatif_*]
    MP --> W

Loading

Run it

# MySQL 8.0 running locally
bash run_all.sh                          # default: mysql -uroot
MYSQL="mysql -u me -p" bash run_all.sh   # or with your own client command

run_all.sh drops and recreates the glaze_bom database, loads the data and runs every file in order. The results go to output/. The last run is committed there too, so every query can be read next to what it returns without running anything.

Data model

erDiagram
    items ||--o{ recipe_lines : "parent"
    items ||--o{ recipe_lines : "component"
    items ||--o{ raw_analysis : "analysed"
    oxides ||--o{ raw_analysis : "oxide"
    items ||--o{ price_list : "priced"
    suppliers ||--o{ price_list : "quotes"
    items ||--o| sales_forecast : "forecast"
    items ||--o{ supplier_offers : "offered / replaced"
    suppliers ||--o{ supplier_offers : "offers"
    items ||--o{ scenario_swaps : "what-if"
    supplier_offers ||--o{ scenario_swaps : "priced by"
    fx_rates {
        date month_start PK
        decimal czk_per_eur
    }
    substitution_rules {
        string rule_code PK
        string applies_to PK
        decimal ok_max
        decimal trial_max
    }

Loading

There is one items table for raw materials, frits and glazes, and one recipe_lines table that says which item contains which. Because of this, one recursive query can walk a bill of materials of any depth. GL-240 is a blend of two glazes, and each of them contains a frit, which gives the BOM three levels.

What the queries find

On the synthetic data:

• An INNER JOIN makes a recipe look cheaper. Talc has had no valid price since its contract ran out. The join drops the line, and the barium frit comes out at €577.5 per tonne of batch, with no error. The LEFT JOIN version gives no number for it and names talc as the missing price. (03, 06)
• A tonne of frit takes 1.12–1.21 t of raw materials. Carbonates, borates and clays lose weight in the furnace. With next year's forecast that is 2 048 t of CO₂ and water leaving the frit furnaces. (04)
• A join finds 12 users of feldspar; the recursive query finds 13. Every product contains feldspar. The semi-opaque blend contains it through four different paths. (03, 04)
• Raw basis or fired basis: 82 % vs 100 %. Per kilogram of batch, the borosilicate frit seems to be only 82 % oxides. The composition of a frit only makes sense per kilogram of glass. (05)
• Between March 2023 and September 2026, zircon cut the cost of the opaque white products by 4–5 %, and boron raised the borosilicate ones by about 4.5 %. Recipes did not change, so the change in cost splits exactly into one contribution per material. (06)
• Four materials are 77 % of the money. Quartz is the heaviest raw material and falls in class B. Zinc oxide is fifth by weight and first by spend. (06)
• The 19 % cheaper offer saves a tenth of what the 6 % one does: €3.7k vs €38.7k a year without a trial or a new recipe. Its headline figure of €188k needs seven recipes to be reformulated and three to be trialled. Soda feldspar replaces potash feldspar kilogram for kilogram, but it moves the fluxes of the Seger formula. The chemistry catches this, the price list does not. (07)
• Changes that pass alone can add up. Three of the nine switches that pass on their own reach the semi-opaque glaze GL-240. Together they raise its iron by 0.0175 wt % against a limit of 0.020, so the combined change is checked again as a whole. (07)

Principles the files follow

• A missing value is reported, not priced at zero. An INNER JOIN drops a line and SUM() skips NULLs. Both give a number that looks complete. Where a missing price could hide, the query counts its rows before it shows a total.
• One structure for every level. An item master, a self-referencing BOM and one recursive query handle any depth. A self-join handles only as many levels as it was written for.
• Chemistry on the right basis. A frit's composition is given per kilogram of glass, not per kilogram of batch. Each denominator is computed for the whole product, never per oxide.
• Scenarios and rules are rows, not code. A what-if is a set of rows in scenario_swaps. A limit is a row in substitution_rules. The same SQL handles the real recipes and every scenario.
• A change is judged on everything it reaches. A new frit recipe changes every glaze made from that frit.
• An estimate says how far to trust it. The thermal expansion estimate is good for comparing a recipe before and after a change. It does not replace a measurement.
• Self-tests with known answers. The glass balance of a frit must be exactly 1. Raw materials minus products must equal the furnace losses. A combined scenario must save the sum of its parts. These hold by construction: if one fails, the SQL is wrong, not the data.
• Checks on the data flag, they do not fix. An analysis without LOI or an overlapping price is reported and left in place until someone knows the right value.

Chemistry used

• Fired composition: the weight % of each oxide in the fired material, i.e. after the loss on ignition (LOI).
• Seger formula (unity molecular formula): moles of each oxide, scaled so that the fluxes (R₂O + RO) add up to 1.
• Thermal expansion: Appen's additive factors (A. A. Appen, Glass Chemistry, 2nd ed., Leningrad 1974), as tabulated by A. Fluegel, with the composition-dependent rules for SiO₂, B₂O₃, TiO₂ and K₂O. The result is in 10⁻⁷/K, 20–400 °C. For B₂O₃ with F < 0 the factor is held at 0, the edge of the tabulated range; that is a choice, and it only touches the engobe. The factors were fitted on glasses. A fired glaze is not only glass: zircon mostly stays undissolved, matte glazes crystallise on purpose, and an engobe never fully melts.
• Molar masses are calculated from IUPAC standard atomic weights.

Analyses of pure chemicals are close to their theoretical composition. The natural minerals have invented but plausible impurities. Prices, exchange rates, the forecast and the offers are invented.

MySQL 8 specifics worth knowing

Situation	In MySQL 8	Handled in
a recursive CTE meets a cycle	never stops by itself: error 3636 at cte_max_recursion_depth (1000), or error 1406 earlier if a growing path column is too narrow	04: the path is carried along and checked with FIND_IN_SET
aggregation or ORDER BY inside the recursive part	not allowed	04: the yield is taken from a separate view
FULL OUTER JOIN	does not exist	07: the oxides table drives the join, with LEFT JOINs to both sides
PIVOT	does not exist	05, 07: conditional aggregation
CHECK constraints	enforced only from 8.0.16; parsed and ignored before	01
CAST(... AS DOUBLE)	from 8.0.17	04
WITH ROLLUP + GROUPING()	GROUPING() from 8.0.1	04, 05, 07

Porting notes: PostgreSQL 14+ has a CYCLE clause for recursive queries and supports FULL OUTER JOIN and FILTER (WHERE ...) for pivots. SQL Server writes WITH without RECURSIVE, limits depth with OPTION (MAXRECURSION n), and uses STRING_AGG in place of GROUP_CONCAT. Two MySQL shortcuts in file 03 need rewriting elsewhere: SUM(EXISTS (...)) becomes SUM(CASE WHEN EXISTS (...) THEN 1 ELSE 0 END), and HAVING on a column alias becomes a WHERE on a derived table.

Licence

MIT. The data is synthetic and may be reused freely.