Supply Chain Planning (SCP) data model template

Tables in this data model

• locations — 4 columns
• products — 4 columns
• customers — 3 columns
• transport_lanes — 6 columns
• sourcing_rules — 5 columns
• planning_scenarios — 4 columns
• demand_plan — 8 columns
• inventory_policies — 6 columns
• inventory_positions — 6 columns
• resources — 4 columns
• supply_plan — 9 columns
• capacity_constraints — 5 columns
• plan_exceptions — 6 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 locations (
  location_id   INTEGER      NOT NULL PRIMARY KEY,
  code          VARCHAR(20)  NOT NULL UNIQUE,
  location_type VARCHAR(20)  NOT NULL,
  region        VARCHAR(40)
);

CREATE TABLE products (
  product_id INTEGER      NOT NULL PRIMARY KEY,
  sku        VARCHAR(30)  NOT NULL UNIQUE,
  name       VARCHAR(150) NOT NULL,
  abc_class  CHAR(1)
);

CREATE TABLE customers (
  customer_id INTEGER      NOT NULL PRIMARY KEY,
  name        VARCHAR(150) NOT NULL,
  channel     VARCHAR(30)
);

CREATE TABLE transport_lanes (
  lane_id        INTEGER       NOT NULL PRIMARY KEY,
  from_location  INTEGER       NOT NULL REFERENCES locations(location_id),
  to_location    INTEGER       NOT NULL REFERENCES locations(location_id),
  mode           VARCHAR(10)   NOT NULL,
  transit_days   INTEGER       NOT NULL,
  cost_per_unit  DECIMAL(10,4)
);

CREATE TABLE sourcing_rules (
  rule_id        INTEGER      NOT NULL PRIMARY KEY,
  product_id     INTEGER      NOT NULL REFERENCES products(product_id),
  location_id    INTEGER      NOT NULL REFERENCES locations(location_id),
  source_lane_id INTEGER      REFERENCES transport_lanes(lane_id),
  share_pct      DECIMAL(5,2) NOT NULL
);

CREATE TABLE planning_scenarios (
  scenario_id INTEGER      NOT NULL PRIMARY KEY,
  name        VARCHAR(80)  NOT NULL,
  is_baseline BOOLEAN      NOT NULL,
  created_by  VARCHAR(100)
);

CREATE TABLE demand_plan (
  demand_id   INTEGER       NOT NULL PRIMARY KEY,
  scenario_id INTEGER       NOT NULL REFERENCES planning_scenarios(scenario_id),
  product_id  INTEGER       NOT NULL REFERENCES products(product_id),
  customer_id INTEGER       REFERENCES customers(customer_id),
  location_id INTEGER       NOT NULL REFERENCES locations(location_id),
  week        DATE          NOT NULL,
  stat_forecast DECIMAL(12,2),
  consensus_qty DECIMAL(12,2) NOT NULL
);

CREATE TABLE inventory_policies (
  policy_id     INTEGER       NOT NULL PRIMARY KEY,
  product_id    INTEGER       NOT NULL REFERENCES products(product_id),
  location_id   INTEGER       NOT NULL REFERENCES locations(location_id),
  service_level DECIMAL(5,2)  NOT NULL,
  safety_stock  DECIMAL(12,2) NOT NULL,
  reorder_point DECIMAL(12,2)
);

CREATE TABLE inventory_positions (
  position_id INTEGER       NOT NULL PRIMARY KEY,
  product_id  INTEGER       NOT NULL REFERENCES products(product_id),
  location_id INTEGER       NOT NULL REFERENCES locations(location_id),
  on_hand     DECIMAL(12,2) NOT NULL,
  in_transit  DECIMAL(12,2) NOT NULL,
  as_of       DATE          NOT NULL
);

CREATE TABLE resources (
  resource_id INTEGER      NOT NULL PRIMARY KEY,
  location_id INTEGER      NOT NULL REFERENCES locations(location_id),
  name        VARCHAR(80)  NOT NULL,
  weekly_capacity DECIMAL(10,2) NOT NULL
);

CREATE TABLE supply_plan (
  supply_id   INTEGER       NOT NULL PRIMARY KEY,
  scenario_id INTEGER       NOT NULL REFERENCES planning_scenarios(scenario_id),
  product_id  INTEGER       NOT NULL REFERENCES products(product_id),
  location_id INTEGER       NOT NULL REFERENCES locations(location_id),
  lane_id     INTEGER       REFERENCES transport_lanes(lane_id),
  resource_id INTEGER       REFERENCES resources(resource_id),
  week        DATE          NOT NULL,
  planned_qty DECIMAL(12,2) NOT NULL,
  supply_type VARCHAR(10)   NOT NULL
);

CREATE TABLE capacity_constraints (
  constraint_id INTEGER       NOT NULL PRIMARY KEY,
  scenario_id   INTEGER       NOT NULL REFERENCES planning_scenarios(scenario_id),
  resource_id   INTEGER       NOT NULL REFERENCES resources(resource_id),
  week          DATE          NOT NULL,
  load_pct      DECIMAL(6,2)  NOT NULL
);

CREATE TABLE plan_exceptions (
  exception_id INTEGER      NOT NULL PRIMARY KEY,
  scenario_id  INTEGER      NOT NULL REFERENCES planning_scenarios(scenario_id),
  product_id   INTEGER      REFERENCES products(product_id),
  location_id  INTEGER      REFERENCES locations(location_id),
  kind         VARCHAR(30)  NOT NULL,
  severity     VARCHAR(10)  NOT NULL
);

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