01_schema.sql

-- =============================================================================
-- 01_schema.sql
--
-- Schema for the recipe side of a frit and glaze plant: raw materials with
-- their chemical analyses, multi-level recipes (a glaze contains frits, a
-- frit is melted from raw materials), purchase prices with validity periods,
-- a sales forecast, and the supplier offers evaluated in file 07.
--
-- Skills shown
--   * one item master for every level of the bill of materials
--   * a self-referencing BOM table (parent -> component)
--   * CHECK constraints, including one that forbids the one-step cycle
--   * validity periods (valid_from / valid_to, SCD type 2 style) instead of
--     overwriting prices
--   * business rules stored as data (substitution_rules)
--   * NULL used on purpose: "not reported" is not the same as zero
--
-- Dialect: MySQL 8.0.17+ (CHECK constraints from 8.0.16, CAST AS DOUBLE from
-- 8.0.17). All data is synthetic.
-- =============================================================================

DROP DATABASE IF EXISTS glaze_bom;
CREATE DATABASE glaze_bom CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE glaze_bom;

-- ── Oxides: the vocabulary of glaze chemistry ───────────────────────────────
-- Every analysis and every result is expressed in oxides. The Seger group
-- decides where an oxide sits in the Seger formula (file 05): R2O + RO are
-- the bases (fluxes), R2O3 the amphoteric oxides (B2O3 goes here by
-- convention), RO2 the acidic ones. The Appen factor is the oxide's share of
-- thermal expansion per mole fraction.
CREATE TABLE oxides (
  oxide_code    VARCHAR(8)   NOT NULL,
  molar_mass    DECIMAL(8,3) NOT NULL COMMENT 'g/mol',
  seger_group   ENUM('R2O','RO','R2O3','RO2') NOT NULL,
  appen_factor  SMALLINT     NULL
                COMMENT '10^-7/K per mole fraction; NULL = depends on the composition (SiO2, B2O3, TiO2, K2O)',
  PRIMARY KEY (oxide_code)
) ENGINE=InnoDB COMMENT='oxides used in analyses and results';

-- ── Items: raw materials, frits and glazes in one table ─────────────────────
-- One master table for all levels is what lets a single recursive query walk
-- the whole bill of materials (file 04). A glaze can contain frits, raw
-- materials and even other glazes; the table does not care.
CREATE TABLE items (
  item_code   VARCHAR(10) NOT NULL,
  item_name   VARCHAR(60) NOT NULL,
  item_type   ENUM('raw','frit','glaze') NOT NULL
              COMMENT 'raw = bought; frit = melted here; glaze = mixed here',
  family      ENUM('transparent','opaque','matte','engobe') NULL
              COMMENT 'frits and glazes only; some quality rules depend on it',
  loi_pct     DECIMAL(5,2) NULL
              COMMENT 'loss on ignition, raw materials only; NULL = not reported by the supplier',
  PRIMARY KEY (item_code),
  CONSTRAINT ck_items_loi_raw_only CHECK (item_type = 'raw' OR loi_pct IS NULL),
  CONSTRAINT ck_items_family       CHECK ((item_type = 'raw') = (family IS NULL)),
  -- file 04 joins codes with commas into a BOM path; a comma inside a code
  -- would break the cycle test there
  CONSTRAINT ck_items_code         CHECK (item_code NOT LIKE '%,%')
) ENGINE=InnoDB COMMENT='item master: every level of the BOM';

-- ── Chemical analyses of raw materials ──────────────────────────────────────
-- Long format: one row per material and oxide. Oxides a certificate does not
-- list are simply absent — which is why file 03 checks that each analysis,
-- together with its LOI, still adds up to about 100 %.
CREATE TABLE raw_analysis (
  item_code   VARCHAR(10)  NOT NULL,
  oxide_code  VARCHAR(8)   NOT NULL,
  wt_pct      DECIMAL(6,3) NOT NULL,
  PRIMARY KEY (item_code, oxide_code),
  KEY ix_analysis_oxide (oxide_code),
  CONSTRAINT fk_analysis_item  FOREIGN KEY (item_code)  REFERENCES items (item_code),
  CONSTRAINT fk_analysis_oxide FOREIGN KEY (oxide_code) REFERENCES oxides (oxide_code),
  CONSTRAINT ck_analysis_pct   CHECK (wt_pct > 0 AND wt_pct <= 100)
) ENGINE=InnoDB COMMENT='oxide analysis of each raw material, weight %';

-- ── Recipes: the bill of materials ──────────────────────────────────────────
-- parent contains component in "parts" by weight. Parts do not have to add up
-- to 100; every calculation divides by the recipe total.
-- The CHECK stops a recipe from containing itself directly. Longer cycles
-- (A contains B, B contains A) cannot be expressed as a constraint; the
-- recursive query in file 04 carries its own guard for them.
CREATE TABLE recipe_lines (
  parent_code     VARCHAR(10)  NOT NULL,
  component_code  VARCHAR(10)  NOT NULL,
  parts           DECIMAL(7,3) NOT NULL,
  PRIMARY KEY (parent_code, component_code),
  KEY ix_lines_component (component_code),          -- "where is this used?"
  CONSTRAINT fk_lines_parent    FOREIGN KEY (parent_code)    REFERENCES items (item_code),
  CONSTRAINT fk_lines_component FOREIGN KEY (component_code) REFERENCES items (item_code),
  CONSTRAINT ck_lines_parts     CHECK (parts > 0),
  CONSTRAINT ck_lines_not_self  CHECK (parent_code <> component_code)
) ENGINE=InnoDB COMMENT='BOM: parent contains component, parts by weight';

-- ── Suppliers and prices ────────────────────────────────────────────────────
CREATE TABLE suppliers (
  supplier_code  VARCHAR(8)  NOT NULL,
  supplier_name  VARCHAR(40) NOT NULL,
  PRIMARY KEY (supplier_code)
) ENGINE=InnoDB;

-- A price is never overwritten: a new row starts, the old one gets a
-- valid_to (the SCD type 2 idea, applied to a price list). valid_to NULL =
-- open-ended, i.e. the current price. Two rows of the same material must not
-- overlap — a rule the table cannot enforce on its own, so file 06 checks it.
CREATE TABLE price_list (
  item_code      VARCHAR(10)   NOT NULL,
  supplier_code  VARCHAR(8)    NOT NULL,
  valid_from     DATE          NOT NULL,
  valid_to       DATE          NULL COMMENT 'inclusive; NULL = still valid',
  price_per_t    DECIMAL(10,2) NOT NULL,
  currency       CHAR(3)       NOT NULL,
  PRIMARY KEY (item_code, valid_from),
  CONSTRAINT fk_price_item     FOREIGN KEY (item_code)     REFERENCES items (item_code),
  CONSTRAINT fk_price_supplier FOREIGN KEY (supplier_code) REFERENCES suppliers (supplier_code),
  CONSTRAINT ck_price_positive CHECK (price_per_t > 0),
  CONSTRAINT ck_price_period   CHECK (valid_to IS NULL OR valid_to >= valid_from),
  CONSTRAINT ck_price_currency CHECK (currency IN ('EUR','CZK'))
) ENGINE=InnoDB COMMENT='purchase prices with validity periods';

CREATE TABLE fx_rates (
  month_start  DATE         NOT NULL,
  czk_per_eur  DECIMAL(6,3) NOT NULL,
  PRIMARY KEY (month_start),
  CONSTRAINT ck_fx_first_day CHECK (DAY(month_start) = 1)
) ENGINE=InnoDB COMMENT='monthly exchange rate, illustrative values';

-- ── Demand ──────────────────────────────────────────────────────────────────
-- Tonnes per year the plant expects to SELL. Frits appear here only with
-- what is sold as frit; what goes into our own glazes is derived (file 04).
CREATE TABLE sales_forecast (
  item_code        VARCHAR(10)  NOT NULL,
  tonnes_per_year  DECIMAL(9,1) NOT NULL,
  PRIMARY KEY (item_code),
  CONSTRAINT fk_forecast_item FOREIGN KEY (item_code) REFERENCES items (item_code),
  CONSTRAINT ck_forecast_pos  CHECK (tonnes_per_year > 0)
) ENGINE=InnoDB;

-- ── Supplier offers and the rules to judge them (file 07) ──────────────────
CREATE TABLE supplier_offers (
  offer_code     VARCHAR(10)   NOT NULL,
  item_code      VARCHAR(10)   NOT NULL COMMENT 'the offered material',
  replaces_code  VARCHAR(10)   NOT NULL COMMENT 'the material it would replace, 1:1 by weight',
  supplier_code  VARCHAR(8)    NOT NULL,
  price_per_t    DECIMAL(10,2) NOT NULL,
  currency       CHAR(3)       NOT NULL,
  PRIMARY KEY (offer_code),
  CONSTRAINT fk_offer_item     FOREIGN KEY (item_code)     REFERENCES items (item_code),
  CONSTRAINT fk_offer_replaces FOREIGN KEY (replaces_code) REFERENCES items (item_code),
  CONSTRAINT fk_offer_supplier FOREIGN KEY (supplier_code) REFERENCES suppliers (supplier_code),
  CONSTRAINT ck_offer_currency CHECK (currency IN ('EUR','CZK'))
) ENGINE=InnoDB;

-- How far a product may move before a change needs a trial or a new recipe.
-- The limits are data, not code: the technologist changes a number here,
-- nobody edits a query. applies_to = 'all' or one product family; a family
-- rule is checked IN ADDITION to the 'all' rules, it does not replace them.
-- rule_code is limited to the measures file 07 computes, so a typo cannot
-- silently turn into a verdict.
CREATE TABLE substitution_rules (
  rule_code   VARCHAR(20)  NOT NULL,
  applies_to  VARCHAR(20)  NOT NULL DEFAULT 'all',
  ok_max      DECIMAL(6,3) NOT NULL COMMENT 'up to this change: switch',
  trial_max   DECIMAL(6,3) NOT NULL COMMENT 'up to this change: switch after a trial; above: reformulate',
  note        VARCHAR(120) NULL,
  PRIMARY KEY (rule_code, applies_to),
  CONSTRAINT ck_rules_order   CHECK (trial_max >= ok_max),
  CONSTRAINT ck_rules_code    CHECK (rule_code IN ('flux_shift', 'cte_shift', 'fe2o3_rise')),
  CONSTRAINT ck_rules_applies CHECK (applies_to IN ('all', 'transparent', 'opaque', 'matte', 'engobe'))
) ENGINE=InnoDB;

-- ── Scenarios for what-if analysis ──────────────────────────────────────────
-- Empty until file 07. A scenario is a set of swaps "in recipe P, use TO
-- instead of FROM". The views built in file 04 read the recipes through this
-- table, so any scenario gets exploded and analysed by the same SQL as the
-- real recipes. offer_code says which offer a swap would buy from; a purely
-- technical what-if leaves it NULL and has no saving.
CREATE TABLE scenario_swaps (
  scenario_id  VARCHAR(40) NOT NULL,
  parent_code  VARCHAR(10) NOT NULL,
  from_code    VARCHAR(10) NOT NULL,
  to_code      VARCHAR(10) NOT NULL,
  offer_code   VARCHAR(10) NULL,
  PRIMARY KEY (scenario_id, parent_code, from_code),
  CONSTRAINT fk_swap_parent FOREIGN KEY (parent_code) REFERENCES items (item_code),
  CONSTRAINT fk_swap_from   FOREIGN KEY (from_code)   REFERENCES items (item_code),
  CONSTRAINT fk_swap_to     FOREIGN KEY (to_code)     REFERENCES items (item_code),
  CONSTRAINT fk_swap_offer  FOREIGN KEY (offer_code)  REFERENCES supplier_offers (offer_code)
) ENGINE=InnoDB COMMENT='what-if swaps, filled by file 07';