Product Lifecycle Management (PLM) data model template

Tables in this data model

• products — 4 columns
• parts — 4 columns
• part_revisions — 5 columns
• product_structure — 3 columns
• ebom_lines — 5 columns
• cad_documents — 5 columns
• change_requests — 5 columns
• change_orders — 4 columns
• change_affected_items — 5 columns
• change_approvals — 5 columns
• suppliers — 2 columns
• approved_manufacturer_parts — 5 columns
• compliance_declarations — 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,
  name       VARCHAR(150) NOT NULL,
  family     VARCHAR(80),
  lifecycle_state VARCHAR(20) NOT NULL
);

CREATE TABLE parts (
  part_id     INTEGER      NOT NULL PRIMARY KEY,
  part_no     VARCHAR(40)  NOT NULL UNIQUE,
  description VARCHAR(200) NOT NULL,
  make_or_buy VARCHAR(4)   NOT NULL
);

CREATE TABLE part_revisions (
  revision_id INTEGER     NOT NULL PRIMARY KEY,
  part_id     INTEGER     NOT NULL REFERENCES parts(part_id),
  revision    VARCHAR(5)  NOT NULL,
  state       VARCHAR(20) NOT NULL,
  released_on DATE
);

CREATE TABLE product_structure (
  structure_id    INTEGER       NOT NULL PRIMARY KEY,
  product_id      INTEGER       NOT NULL REFERENCES products(product_id),
  top_revision_id INTEGER       NOT NULL REFERENCES part_revisions(revision_id)
);

CREATE TABLE ebom_lines (
  ebom_line_id   INTEGER       NOT NULL PRIMARY KEY,
  parent_rev_id  INTEGER       NOT NULL REFERENCES part_revisions(revision_id),
  child_rev_id   INTEGER       NOT NULL REFERENCES part_revisions(revision_id),
  quantity       DECIMAL(12,4) NOT NULL,
  find_no        INTEGER
);

CREATE TABLE cad_documents (
  document_id INTEGER      NOT NULL PRIMARY KEY,
  revision_id INTEGER      NOT NULL REFERENCES part_revisions(revision_id),
  file_type   VARCHAR(10)  NOT NULL,
  vault_path  VARCHAR(400) NOT NULL,
  checked_out_by VARCHAR(100)
);

CREATE TABLE change_requests (
  ecr_id      INTEGER      NOT NULL PRIMARY KEY,
  product_id  INTEGER      NOT NULL REFERENCES products(product_id),
  reason      VARCHAR(250) NOT NULL,
  priority    VARCHAR(10)  NOT NULL,
  status      VARCHAR(20)  NOT NULL
);

CREATE TABLE change_orders (
  eco_id       INTEGER     NOT NULL PRIMARY KEY,
  ecr_id       INTEGER     REFERENCES change_requests(ecr_id),
  status       VARCHAR(20) NOT NULL,
  effectivity  DATE
);

CREATE TABLE change_affected_items (
  affected_id    INTEGER     NOT NULL PRIMARY KEY,
  eco_id         INTEGER     NOT NULL REFERENCES change_orders(eco_id),
  old_revision_id INTEGER    REFERENCES part_revisions(revision_id),
  new_revision_id INTEGER    NOT NULL REFERENCES part_revisions(revision_id),
  disposition    VARCHAR(20)
);

CREATE TABLE change_approvals (
  approval_id INTEGER     NOT NULL PRIMARY KEY,
  eco_id      INTEGER     NOT NULL REFERENCES change_orders(eco_id),
  approver    VARCHAR(100) NOT NULL,
  decision    VARCHAR(10) NOT NULL,
  decided_at  TIMESTAMP
);

CREATE TABLE suppliers (
  supplier_id INTEGER      NOT NULL PRIMARY KEY,
  name        VARCHAR(150) NOT NULL
);

CREATE TABLE approved_manufacturer_parts (
  amp_id      INTEGER     NOT NULL PRIMARY KEY,
  part_id     INTEGER     NOT NULL REFERENCES parts(part_id),
  supplier_id INTEGER     NOT NULL REFERENCES suppliers(supplier_id),
  mfr_part_no VARCHAR(60) NOT NULL,
  preferred   BOOLEAN     NOT NULL
);

CREATE TABLE compliance_declarations (
  declaration_id INTEGER     NOT NULL PRIMARY KEY,
  revision_id    INTEGER     NOT NULL REFERENCES part_revisions(revision_id),
  regulation     VARCHAR(20) NOT NULL,
  compliant      BOOLEAN     NOT NULL
);

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