Plan-to-Produce data model template

Tables in this data model

• plants — 3 columns
• materials — 5 columns
• boms — 4 columns
• bom_items — 5 columns
• work_centers — 4 columns
• routings — 3 columns
• routing_operations — 6 columns
• demand_forecasts — 5 columns
• mps_entries — 5 columns
• planned_orders — 6 columns
• production_orders — 6 columns
• confirmations — 6 columns
• component_issues — 4 columns
• quality_inspections — 4 columns

How to use this ER diagram template

1. Click Open in the editor. The ER diagram opens on a new page in FlowDiagram — nothing is uploaded and there is no sign-up.
2. Rename, add or delete shapes and drag them where you want; connectors stay attached as things move.
3. Drop in vendor logos from the library — 1,160 of them, including every AWS, Azure and Google Cloud service icon — or restyle it with the colour and theme controls.
4. Prefer text? Edit the SQL DDL below and paste it in through ☰ → Import / export SQL; the finished model exports back to DDL.
5. Export to PNG, SVG or PDF, or press Ctrl+L for a link that carries the whole diagram.

SQL DDL source

The whole template is the text below. Paste it into FlowDiagram, keep it in Git next to the code it describes, or hand it to an AI assistant to extend.

CREATE TABLE plants (
  plant_id INTEGER     NOT NULL PRIMARY KEY,
  code     VARCHAR(10) NOT NULL,
  name     VARCHAR(100) NOT NULL
);

CREATE TABLE materials (
  material_id   INTEGER      NOT NULL PRIMARY KEY,
  material_no   VARCHAR(30)  NOT NULL UNIQUE,
  material_type VARCHAR(10)  NOT NULL,
  description   VARCHAR(200) NOT NULL,
  lead_time_days INTEGER
);

CREATE TABLE boms (
  bom_id      INTEGER     NOT NULL PRIMARY KEY,
  material_id INTEGER     NOT NULL REFERENCES materials(material_id),
  plant_id    INTEGER     NOT NULL REFERENCES plants(plant_id),
  version     VARCHAR(10) NOT NULL
);

CREATE TABLE bom_items (
  bom_item_id  INTEGER       NOT NULL PRIMARY KEY,
  bom_id       INTEGER       NOT NULL REFERENCES boms(bom_id),
  component_id INTEGER       NOT NULL REFERENCES materials(material_id),
  quantity     DECIMAL(12,4) NOT NULL,
  scrap_pct    DECIMAL(5,2)
);

CREATE TABLE work_centers (
  work_center_id INTEGER     NOT NULL PRIMARY KEY,
  plant_id       INTEGER     NOT NULL REFERENCES plants(plant_id),
  name           VARCHAR(60) NOT NULL,
  capacity_hours DECIMAL(8,2) NOT NULL
);

CREATE TABLE routings (
  routing_id  INTEGER NOT NULL PRIMARY KEY,
  material_id INTEGER NOT NULL REFERENCES materials(material_id),
  plant_id    INTEGER NOT NULL REFERENCES plants(plant_id)
);

CREATE TABLE routing_operations (
  operation_id   INTEGER      NOT NULL PRIMARY KEY,
  routing_id     INTEGER      NOT NULL REFERENCES routings(routing_id),
  work_center_id INTEGER      NOT NULL REFERENCES work_centers(work_center_id),
  sequence       INTEGER      NOT NULL,
  setup_min      DECIMAL(8,2),
  run_min_per_unit DECIMAL(8,3)
);

CREATE TABLE demand_forecasts (
  forecast_id INTEGER       NOT NULL PRIMARY KEY,
  material_id INTEGER       NOT NULL REFERENCES materials(material_id),
  plant_id    INTEGER       NOT NULL REFERENCES plants(plant_id),
  period      DATE          NOT NULL,
  quantity    DECIMAL(12,3) NOT NULL
);

CREATE TABLE mps_entries (
  mps_id      INTEGER       NOT NULL PRIMARY KEY,
  material_id INTEGER       NOT NULL REFERENCES materials(material_id),
  plant_id    INTEGER       NOT NULL REFERENCES plants(plant_id),
  period      DATE          NOT NULL,
  planned_qty DECIMAL(12,3) NOT NULL
);

CREATE TABLE planned_orders (
  planned_order_id INTEGER       NOT NULL PRIMARY KEY,
  mps_id           INTEGER       REFERENCES mps_entries(mps_id),
  material_id      INTEGER       NOT NULL REFERENCES materials(material_id),
  quantity         DECIMAL(12,3) NOT NULL,
  procurement_type VARCHAR(10)   NOT NULL,
  need_date        DATE          NOT NULL
);

CREATE TABLE production_orders (
  prod_order_id    INTEGER       NOT NULL PRIMARY KEY,
  planned_order_id INTEGER       REFERENCES planned_orders(planned_order_id),
  material_id      INTEGER       NOT NULL REFERENCES materials(material_id),
  routing_id       INTEGER       NOT NULL REFERENCES routings(routing_id),
  quantity         DECIMAL(12,3) NOT NULL,
  status           VARCHAR(20)   NOT NULL
);

CREATE TABLE confirmations (
  confirmation_id INTEGER       NOT NULL PRIMARY KEY,
  prod_order_id   INTEGER       NOT NULL REFERENCES production_orders(prod_order_id),
  operation_id    INTEGER       NOT NULL REFERENCES routing_operations(operation_id),
  yield_qty       DECIMAL(12,3) NOT NULL,
  scrap_qty       DECIMAL(12,3),
  confirmed_at    TIMESTAMP     NOT NULL
);

CREATE TABLE component_issues (
  issue_id      INTEGER       NOT NULL PRIMARY KEY,
  prod_order_id INTEGER       NOT NULL REFERENCES production_orders(prod_order_id),
  material_id   INTEGER       NOT NULL REFERENCES materials(material_id),
  quantity      DECIMAL(12,3) NOT NULL
);

CREATE TABLE quality_inspections (
  inspection_id INTEGER     NOT NULL PRIMARY KEY,
  prod_order_id INTEGER     NOT NULL REFERENCES production_orders(prod_order_id),
  result        VARCHAR(10) NOT NULL,
  usage_decision VARCHAR(20)
);

Related: Database schema diagram tool · ER diagram from SQL · All database & er models templates · All 521 templates