E-commerce store with reviews & coupons schema template

Tables in this data model

• customers — 4 columns
• categories — 3 columns
• products — 6 columns
• coupons — 5 columns
• orders — 6 columns
• order_items — 5 columns
• coupon_redemptions — 4 columns
• reviews — 7 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 customers (
  customer_id  INTEGER      NOT NULL PRIMARY KEY,
  email        VARCHAR(255) NOT NULL,
  full_name    VARCHAR(150) NOT NULL,
  created_at   TIMESTAMP    NOT NULL
);

CREATE TABLE categories (
  category_id INTEGER     NOT NULL PRIMARY KEY,
  parent_id   INTEGER     REFERENCES categories(category_id),
  name        VARCHAR(80) NOT NULL
);

CREATE TABLE products (
  product_id  INTEGER       NOT NULL PRIMARY KEY,
  category_id INTEGER       NOT NULL REFERENCES categories(category_id),
  sku         VARCHAR(40)   NOT NULL,
  name        VARCHAR(200)  NOT NULL,
  price       DECIMAL(10,2) NOT NULL,
  is_active   BOOLEAN       NOT NULL
);

CREATE TABLE coupons (
  coupon_id   INTEGER       NOT NULL PRIMARY KEY,
  code        VARCHAR(30)   NOT NULL,
  percent_off DECIMAL(5,2),
  amount_off  DECIMAL(10,2),
  valid_until DATE          NOT NULL
);

CREATE TABLE orders (
  order_id    INTEGER       NOT NULL PRIMARY KEY,
  customer_id INTEGER       NOT NULL REFERENCES customers(customer_id),
  status      VARCHAR(20)   NOT NULL,
  subtotal    DECIMAL(12,2) NOT NULL,
  discount    DECIMAL(12,2) NOT NULL,
  placed_at   TIMESTAMP     NOT NULL
);

CREATE TABLE order_items (
  order_item_id INTEGER       NOT NULL PRIMARY KEY,
  order_id      INTEGER       NOT NULL REFERENCES orders(order_id),
  product_id    INTEGER       NOT NULL REFERENCES products(product_id),
  quantity      INTEGER       NOT NULL,
  unit_price    DECIMAL(10,2) NOT NULL
);

CREATE TABLE coupon_redemptions (
  redemption_id INTEGER   NOT NULL PRIMARY KEY,
  coupon_id     INTEGER   NOT NULL REFERENCES coupons(coupon_id),
  order_id      INTEGER   NOT NULL REFERENCES orders(order_id),
  redeemed_at   TIMESTAMP NOT NULL
);

CREATE TABLE reviews (
  review_id   INTEGER      NOT NULL PRIMARY KEY,
  product_id  INTEGER      NOT NULL REFERENCES products(product_id),
  customer_id INTEGER      NOT NULL REFERENCES customers(customer_id),
  rating      INTEGER      NOT NULL,
  body        VARCHAR(2000),
  verified_purchase BOOLEAN NOT NULL,
  created_at  TIMESTAMP    NOT NULL
);

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