E-Commerce Database ERD Design

Designing an E-Commerce Schema

An e-commerce database is one of the most commonly designed schemas. It involves products, customers, orders, payments, and their interconnections. This ERD covers the essential entities for a functioning online store.

Core Entities

Products and Categories (M:N)

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  parent_id INT,
  FOREIGN KEY (parent_id) REFERENCES categories(id)
);

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  sku VARCHAR(100) UNIQUE,
  is_active BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE product_categories (
  product_id INT NOT NULL,
  category_id INT NOT NULL,
  PRIMARY KEY (product_id, category_id),
  FOREIGN KEY (product_id) REFERENCES products(id),
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

Customers and Orders (1:N)

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  email VARCHAR(320) NOT NULL UNIQUE,
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'pending',
  total DECIMAL(12,2) NOT NULL,
  shipping_address TEXT NOT NULL,
  ordered_at TIMESTAMP NOT NULL DEFAULT NOW(),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Order Items (junction between orders and products)

CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

Payments (1:1 with orders)

CREATE TABLE payments (
  id SERIAL PRIMARY KEY,
  order_id INT NOT NULL UNIQUE,
  method VARCHAR(50) NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'pending',
  paid_at TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id)
);

Relationship Map

Relationship	Type	Implementation
customers → orders	1:N	orders.customer_id
orders → order_items	1:N	order_items.order_id
products → order_items	1:N	order_items.product_id
products ↔ categories	M:N	product_categories junction
categories → categories	Self-ref	categories.parent_id
orders → payments	1:1	payments.order_id UNIQUE

Design Decisions

• Price in order_items: Store the price at time of purchase (not a reference to current product price)
• Self-referencing categories: Allows unlimited nesting (Electronics → Laptops → Gaming Laptops)
• Separate payments table: Allows multiple payment attempts and different payment methods