Advanced Multi-Vendor E-Commerce Schema

Enterprise-grade schema supporting multi-vendor catalogs, product reviews, rating comments, snapshotted order ledgers, and index optimizations.

SQL DDL Script

schema.sql

-- 1. Vendors Table (Supports Multi-Vendor Marketplace)
CREATE TABLE vendors (
  id INT PRIMARY KEY AUTO_INCREMENT,
  shop_name VARCHAR(150) UNIQUE NOT NULL,
  contact_email VARCHAR(150) NOT NULL,
  rating DECIMAL(3,2) DEFAULT 5.00,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Categories Table
CREATE TABLE categories (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100) UNIQUE NOT NULL
);

-- 3. Products Table (Relates to Vendor and Category)
CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  vendor_id INT NOT NULL,
  category_id INT NOT NULL,
  name VARCHAR(200) NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  stock INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
);

-- 4. Users Table (Customer Credentials & Profiles)
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(150) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 5. Orders Table
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  order_status VARCHAR(50) DEFAULT 'Pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 6. Order Items Table (Critical: Price is snapshotted)
CREATE TABLE order_items (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  price DECIMAL(10,2) NOT NULL, -- Checkout-time snapshot price
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);

-- 7. Product Reviews Table (Ratings & Comments)
CREATE TABLE product_reviews (
  id INT PRIMARY KEY AUTO_INCREMENT,
  product_id INT NOT NULL,
  user_id INT NOT NULL,
  rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE(product_id, user_id) -- Prevents duplicate reviews from same user
);

-- Optimization Indexes
CREATE INDEX idx_products_vendor ON products(vendor_id);
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_reviews_product ON product_reviews(product_id);

Architectural Explanation

This schema implements a highly scalable multi-vendor e-commerce platform. It splits products between vendors and categories to maintain referential normalization (3NF). It captures reviews with check constraints to enforce integer bounds (1 to 5 stars), and implements composite uniqueness to prevent rating spam. Crucially, the order_items table snapshots the product's price at check-out time to guard against historical ledger corruption if the vendor updates catalog prices.

Relational Database Design Theory

A relational database stores data in tabular formats (relations) containing rows (tuples) and columns (attributes). Designing a multi-vendor ecosystem requires careful organization of these tables to prevent Data Anomalies:

Insertion Anomaly: Being unable to insert a vendor because they haven't listed a product yet.

Update Anomaly: If a vendor's shop name changes, having to update it across 10,000 product rows.

Deletion Anomaly: Deleting a product accidentally wipes out the vendor's profile from the database.

By segregating data into separate entities (vendors, products, categories) and establishing relationships, we satisfy Third Normal Form (3NF), ensuring each attribute depends *only* on the primary key, the whole primary key, and nothing but the primary key.

The Power of Foreign Keys

A Foreign Key (FK) is a column (or group of columns) in one table that points to the Primary Key (PK) of another table. It acts as a logical link that enforces Referential Integrity.

Why We Use Foreign Keys:

Prevent Garbage Data: A product cannot be created with a vendor_id of 999 if no vendor with ID 999 exists. The database engine rejects the insert.

Handle Cascading Actions:

ON DELETE CASCADE: If a vendor closes their shop and we delete their record, the database automatically deletes all products and reviews related to that vendor.

ON DELETE RESTRICT: If you try to delete a category that still has products assigned to it, the database blocks the deletion, protecting the product integrity.

ON DELETE SET NULL: Sets the child reference to NULL if the parent row is deleted.

Database Indexing Explained

An Index is a data structure (typically a B-Tree) that the database engine maintains to speed up row retrieval. Without an index, finding a product by category requires a Full Table Scan (scanning every row on disk).

Critical Rules for Indexing:

Index Foreign Keys: Relational queries use JOINs on FK columns (e.g. products.vendor_id = vendors.id). Indexing these columns speeds up search operations from $O(N)$ down to $O(\log N)$.

Index Search & Sort Fields: If you frequently search by email (WHERE email = ?) or sort by date (ORDER BY created_at), these columns need indexes.

The Indexing Trade-Off: While indexes make SELECT queries blisteringly fast, they slow down INSERT, UPDATE, and DELETE operations because the database has to update the B-Tree structure on every write.

Design Traps & Real-World Pitfalls

When modeling e-commerce relationships, engineers frequently fall into three standard traps:

1. The Historical Price Mutation Trap (Critical)

The Trap: Referencing products.price directly from orders without copying it.

The Impact: If a product price changes from $10 to $15 today, every historical order from last year will suddenly recalculate its total cost based on the new price.

The Fix: Always copy (snapshot) the product price into the order_items.price column during checkout.

2. The Fan Trap

The Trap: Attempting to connect a customer directly to both products and vendors in a way that creates a loop.

The Impact: Ambiguous paths make it impossible to determine which customer bought what product from which vendor.

The Fix: Model the flow lineally: Users -> Orders -> Order Items -> Products -> Vendors.

Common SQL Query Templates

Standard E-Commerce Queries (DQL)

Here are the different types of SELECT statements commonly executed against this database:

1. Aggregating Product Ratings (Calculating Averages)

Retrieve a list of products with their average rating and total review count, utilizing LEFT JOIN to include products that haven't been reviewed yet:

SELECT 
  p.id, 
  p.name, 
  ROUND(AVG(r.rating), 1) AS avg_rating, 
  COUNT(r.id) AS total_reviews
FROM products p
LEFT JOIN product_reviews r ON p.id = r.product_id
GROUP BY p.id;

2. Multi-Join Seller Dashboard

Show a vendor their total sales revenue, units sold, and the buyers' details:

SELECT 
  v.shop_name, 
  p.name AS product_name, 
  SUM(oi.quantity) AS total_units_sold, 
  SUM(oi.quantity * oi.price) AS total_revenue
FROM vendors v
JOIN products p ON v.id = p.vendor_id
JOIN order_items oi ON p.id = oi.product_id
GROUP BY v.id, p.id
ORDER BY total_revenue DESC;

3. Finding Unpopular Products (Subquery Select)

Find products that have zero sales, utilizing a NOT IN subquery:

SELECT id, name, price 
FROM products 
WHERE id NOT IN (
  SELECT DISTINCT product_id 
  FROM order_items
);

Design Best Practices

Always store currency as DECIMAL(10,2) rather than FLOAT or DOUBLE to prevent base-10 to base-2 binary rounding errors.
Index all Foreign Key columns to prevent table scans during queries containing nested JOINs.
Use ON DELETE RESTRICT for categories so a category with active products cannot be accidentally deleted.
Use ON DELETE CASCADE on reviews and order details where children records have no independent business meaning without their parent.

Test this Schema in the Playground

Copy the DDL above and execute it in our SQL console to practice writing queries against this layout.

Open SQL Playground