Manufacturing execution (MES) schema template

Tables in this data model

• products — 3 columns
• bom_lines — 4 columns
• work_centers — 3 columns
• work_orders — 5 columns
• operations — 6 columns
• lots — 4 columns
• inspections — 4 columns
• downtime_events — 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 products (
  product_id INTEGER      NOT NULL PRIMARY KEY,
  part_no    VARCHAR(30)  NOT NULL UNIQUE,
  name       VARCHAR(150) NOT NULL
);

CREATE TABLE bom_lines (
  bom_line_id  INTEGER       NOT NULL PRIMARY KEY,
  parent_id    INTEGER       NOT NULL REFERENCES products(product_id),
  component_id INTEGER       NOT NULL REFERENCES products(product_id),
  quantity     DECIMAL(10,3) NOT NULL
);

CREATE TABLE work_centers (
  work_center_id INTEGER     NOT NULL PRIMARY KEY,
  name           VARCHAR(80) NOT NULL,
  capacity_per_hour INTEGER
);

CREATE TABLE work_orders (
  work_order_id INTEGER     NOT NULL PRIMARY KEY,
  product_id    INTEGER     NOT NULL REFERENCES products(product_id),
  quantity      INTEGER     NOT NULL,
  due_on        DATE        NOT NULL,
  status        VARCHAR(20) NOT NULL
);

CREATE TABLE operations (
  operation_id   INTEGER NOT NULL PRIMARY KEY,
  work_order_id  INTEGER NOT NULL REFERENCES work_orders(work_order_id),
  work_center_id INTEGER NOT NULL REFERENCES work_centers(work_center_id),
  sequence       INTEGER NOT NULL,
  started_at     TIMESTAMP,
  finished_at    TIMESTAMP
);

CREATE TABLE lots (
  lot_id        INTEGER     NOT NULL PRIMARY KEY,
  work_order_id INTEGER     NOT NULL REFERENCES work_orders(work_order_id),
  lot_no        VARCHAR(30) NOT NULL UNIQUE,
  quantity      INTEGER     NOT NULL
);

CREATE TABLE inspections (
  inspection_id INTEGER     NOT NULL PRIMARY KEY,
  lot_id        INTEGER     NOT NULL REFERENCES lots(lot_id),
  result        VARCHAR(10) NOT NULL,
  inspected_at  TIMESTAMP   NOT NULL
);

CREATE TABLE downtime_events (
  downtime_id    INTEGER     NOT NULL PRIMARY KEY,
  work_center_id INTEGER     NOT NULL REFERENCES work_centers(work_center_id),
  reason         VARCHAR(60) NOT NULL,
  minutes        INTEGER     NOT NULL
);

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