E-commerce Database ER Diagram

An e-commerce database usually needs to manage customers, products, categories, orders, order items, payments and reviews. The schema below keeps order lines in a separate order_items table so an order can contain many products, and prices are snapshotted on each order line so later catalog changes never rewrite history.

The most important line in this schema is unit_price on order_items. Catalog prices change constantly, but an order is a financial record: when a product goes from 19.90 to 24.90, last month's invoices must still add up. Copying the price onto each line at purchase time is the standard fix, and orders.total mirrors it at header level. Note that order_items.product_id has no ON DELETE rule, so the database refuses to delete a product that appears in any order — retiring products means flipping a flag or zeroing stock, not removing the row.

Reading the diagram, most edges are ordinary one-to-many: a customer has many addresses and orders, an order has many items. Two edges deserve attention. categories.parent_id references its own table, which draws the category tree as a self-referencing edge — a category has one parent and many children. And payments.order_id is declared UNIQUE, which turns the usual one-to-many into one-to-one: exactly one payment row per order, with paid_at nullable so the row can exist before the processor confirms. Tags work differently: product_tags is a join table whose composite primary key (product_id, tag_id) is what makes it many-to-many.

The ON DELETE choices are deliberately uneven. Deleting a customer cascades to their addresses and reviews, which is harmless, but not to their orders — the plain REFERENCES customers(id) on orders blocks the delete, protecting transactional history from account cleanup. order_items cascades from orders, so removing a cancelled order removes its lines, and product_tags and reviews cascade from products. One gap: categories.parent_id has no rule either, so a category with children cannot be deleted until they are re-parented — annoying, but it beats orphaning a subtree silently.

The natural next tables are a cart (carts plus cart_items, mirroring order_items but without the price snapshot, since nothing is committed until checkout) and a discounts table referenced by a nullable order_items.discount_id, so each line remembers which promotion it benefited from. payments is one-to-one with orders, so anything like split payments or partial refunds would need a second table — either payments becoming one-to-many or a refunds table keyed on payment. Shipment tracking (a shipments table with one row per parcel) is the other common addition once the shop ships from more than one warehouse.

The diagram

Tables (10)

Relationships (10)

• 1:Ncustomers.id→addresses.customer_idON DELETE CASCADE
• 1:Ncategories.id→categories.parent_id
• 1:Ncategories.id→products.category_id
• 1:Ncustomers.id→orders.customer_id
• 1:Norders.id→order_items.order_idON DELETE CASCADE
• 1:Nproducts.id→order_items.product_id
• 1:1orders.id→payments.order_id
• 1:Nproducts.id→reviews.product_idON DELETE CASCADE
• 1:Ncustomers.id→reviews.customer_idON DELETE CASCADE
• N:Mproducts.id→tags.id

The SQL

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  full_name VARCHAR(120) NOT NULL,
  phone VARCHAR(30),
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE addresses (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  line1 VARCHAR(200) NOT NULL,
  line2 VARCHAR(200),
  city VARCHAR(80) NOT NULL,
  postal_code VARCHAR(20) NOT NULL,
  country VARCHAR(60) NOT NULL
);

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

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  category_id INTEGER REFERENCES categories(id),
  name VARCHAR(150) NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  stock INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  label VARCHAR(40) NOT NULL UNIQUE
);

CREATE TABLE product_tags (
  product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (product_id, tag_id)
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  total DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL DEFAULT 1,
  unit_price DECIMAL(10,2) NOT NULL
);

CREATE TABLE payments (
  id SERIAL PRIMARY KEY,
  order_id INTEGER NOT NULL UNIQUE REFERENCES orders(id),
  amount DECIMAL(10,2) NOT NULL,
  method VARCHAR(30) NOT NULL,
  paid_at TIMESTAMP
);

CREATE TABLE reviews (
  id SERIAL PRIMARY KEY,
  product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  rating INTEGER NOT NULL,
  comment TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

Keep learning

• What Is an ER Diagram?
• How to Create an ER Diagram
• SQL to ER Diagram: The Complete Guide
• Primary Key vs Foreign Key
• All guides