Setting Up DB Schema
E-Commerce Schema
This walkthrough builds a relational schema for a fictional marketplace so you can practice creating tables, defining constraints, and loading reference data before running analytical queries. The model illustrates common patterns such as product categories, seller ownership, customer accounts, and the many-to-many relationship between orders and products.
Entity Relationship Diagram
The diagram highlights a few important design calls:
- Categories organize products and can also represent a customer preference.
- Products belong to a category and (optionally) to a seller.
- Orders belong to customers and are broken into order items to model the many-to-many relationship between orders and products.
- Transactions track payment attempts and outcomes for orders.
Create Tables (MySQL)
Run the DDL from top to bottom so foreign key targets exist before dependencies. Make sure you're using the ecommerce_store database:
USE ecommerce_store;
Drop existing tables safely
create_tables.sql
DROP TABLE IF EXISTS order_item;
DROP TABLE IF EXISTS transactions;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS product;
DROP TABLE IF EXISTS customer;
DROP TABLE IF EXISTS seller;
DROP TABLE IF EXISTS category;
1. Category
Each category stores a unique name and groups related products.
create_tables.sql
CREATE TABLE category (
category_id INT AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(80) NOT NULL UNIQUE
) ENGINE=InnoDB;
2. Seller
Sellers represent stores or brands listing products in the marketplace.
create_tables.sql
CREATE TABLE seller (
seller_id INT AUTO_INCREMENT PRIMARY KEY,
store_name VARCHAR(120) NOT NULL,
first_name VARCHAR(80) NOT NULL,
last_name VARCHAR(80) NOT NULL,
email VARCHAR(190) UNIQUE,
joined_at DATE NOT NULL,
rating DECIMAL(2,1) CHECK (rating BETWEEN 1 AND 5)
) ENGINE=InnoDB;
3. Customer
Customers capture contact details and optional preferences. The preferred_category_id foreign key is nullable so you can represent shoppers without a stated interest.
create_tables.sql
CREATE TABLE customer (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(80) NOT NULL,
last_name VARCHAR(80) NOT NULL,
email VARCHAR(190) UNIQUE,
preferred_category_id INT,
signup_year INT CHECK (signup_year >= 2000),
city VARCHAR(80),
FOREIGN KEY (preferred_category_id) REFERENCES category(category_id)
) ENGINE=InnoDB;
4. Product
Products belong to a category and, when assigned, link to a seller. Stock quantity and price include basic checks to keep values valid.
create_tables.sql
CREATE TABLE product (
product_id INT AUTO_INCREMENT PRIMARY KEY,
sku VARCHAR(30) NOT NULL UNIQUE,
product_name VARCHAR(150) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price > 0),
stock_qty INT NOT NULL DEFAULT 0 CHECK (stock_qty >= 0),
category_id INT NOT NULL,
seller_id INT,
FOREIGN KEY (category_id) REFERENCES category(category_id),
FOREIGN KEY (seller_id) REFERENCES seller(seller_id)
) ENGINE=InnoDB;
5. Orders
Orders track each purchase attempt, its status, and delivery details.
create_tables.sql
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
status VARCHAR(30) NOT NULL,
order_total DECIMAL(10,2) NOT NULL CHECK (order_total >= 0),
shipping_city VARCHAR(80),
delivered_at DATE,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
) ENGINE=InnoDB;
6. Order Item
Order items connect products to a specific order and store the quantity and unit price at checkout.
create_tables.sql
CREATE TABLE order_item (
order_item_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
discount DECIMAL(10,2) DEFAULT 0 CHECK (discount >= 0),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES product(product_id),
UNIQUE (order_id, product_id)
) ENGINE=InnoDB;
7. Transactions
Transactions log payment attempts for orders, including failed or refunded payments.
create_tables.sql
CREATE TABLE transactions (
transaction_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
transaction_time DATETIME NOT NULL,
payment_method VARCHAR(40) NOT NULL,
amount DECIMAL(10,2) NOT NULL CHECK (amount >= 0),
status VARCHAR(30) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(order_id)
) ENGINE=InnoDB;
note
MySQL enforces CHECK constraints in version 8.0.16 and later. If you're on an older MySQL version, keep the constraints for documentation but enforce rules in your application.
Sample Data
Use the following inserts to seed a richer dataset. The rows are ordered so surrogate keys line up with the relationships illustrated above. Wrap the statements in a transaction if you want an easy rollback while experimenting.
Categories
seed_data.sql
INSERT INTO category (category_name) VALUES
('Electronics'),
('Home & Kitchen'),
('Fashion'),
('Beauty & Personal Care'),
('Sports & Outdoors'),
('Books & Stationery');
Sellers
seed_data.sql
INSERT INTO seller (store_name, first_name, last_name, email, joined_at, rating) VALUES
('Voltify', 'Rakesh', 'Mehta', 'rakesh@voltify.store', '2024-02-10', 4.7),
('HearthHome', 'Sonia', 'Kapoor', 'sonia@hearthhome.store', '2023-09-18', 4.5),
('CloudWear', 'Imran', 'Khan', 'imran@cloudwear.store', '2024-05-03', 4.2),
('GlowCart', 'Meera', 'Nair', 'meera@glowcart.store', '2023-11-22', 4.6),
('TrailWorks', 'Vivek', 'Rao', 'vivek@trailworks.store', '2024-01-15', 4.4),
('PageTurner', 'Anjali', 'Gupta', 'anjali@pageturner.store', '2022-07-08', 4.8),
('GadgetNest', 'Farah', 'Siddiqui', 'farah@gadgetnest.store', '2024-03-12', 4.1),
('EverydayBasics', 'Neeraj', 'Batra', 'neeraj@everydaybasics.store', '2023-05-27', 4.0);
Customers
seed_data.sql
INSERT INTO customer (first_name, last_name, email, preferred_category_id, signup_year, city) VALUES
('Aman', 'Sharma', 'aman.sharma@shop.local', 1, 2024, 'Bengaluru'),
('Priya', 'Singh', 'priya.singh@shop.local', 3, 2023, 'Mumbai'),
('Rohit', 'Verma', 'rohit.verma@shop.local', 2, 2024, 'Delhi'),
('Kiran', 'Patel', 'kiran.patel@shop.local', 4, 2022, 'Ahmedabad'),
('Sana', 'Ali', 'sana.ali@shop.local', NULL, 2025, 'Hyderabad'),
('Deepak', 'Joshi', 'deepak.joshi@shop.local', 5, 2023, 'Pune'),
('Alisha', 'Fernandez', 'alisha.fernandez@shop.local', 3, 2022, 'Goa'),
('Tarun', 'Iyer', 'tarun.iyer@shop.local', 1, 2024, 'Chennai'),
('Nisha', 'Desai', 'nisha.desai@shop.local', 6, 2025, 'Jaipur'),
('Harish', 'Kulkarni', 'harish.kulkarni@shop.local', NULL, 2023, 'Nagpur'),
('Gauri', 'Menon', 'gauri.menon@shop.local', 2, 2024, 'Kochi'),
('Kabir', 'Sethi', 'kabir.sethi@shop.local', 5, 2022, 'Indore');
Products
seed_data.sql
INSERT INTO product (sku, product_name, price, stock_qty, category_id, seller_id) VALUES
('EL-1001', 'Wireless Earbuds', 59.99, 120, 1, 1),
('EL-1002', 'Smart LED Bulb', 19.99, 200, 1, 7),
('HK-2001', 'Nonstick Cookware Set', 89.00, 60, 2, 2),
('FA-3001', 'Cotton Hoodie', 39.50, 150, 3, 3),
('BE-4001', 'Vitamin C Serum', 24.00, 80, 4, 4),
('SP-5001', 'Yoga Mat Pro', 32.00, 90, 5, 5),
('BK-6001', 'Productivity Journal', 15.00, 140, 6, 6),
('EL-1003', 'Portable Charger', 29.99, 110, 1, 1),
('HK-2002', 'Aroma Diffuser', 45.00, 75, 2, 2),
('FA-3002', 'Running Sneakers', 79.00, 50, 3, 3),
('SP-5002', 'Stainless Water Bottle', 22.00, 160, 5, NULL);
Orders
seed_data.sql
INSERT INTO orders (customer_id, order_date, status, order_total, shipping_city, delivered_at) VALUES
(1, '2025-03-02', 'Delivered', 89.98, 'Bengaluru', '2025-03-06'),
(2, '2025-03-05', 'Shipped', 39.50, 'Mumbai', NULL),
(3, '2025-03-08', 'Delivered', 89.00, 'Delhi', '2025-03-12'),
(4, '2025-02-25', 'Delivered', 24.00, 'Ahmedabad', '2025-02-28'),
(5, '2025-03-15', 'Pending', 59.99, 'Hyderabad', NULL),
(6, '2025-01-18', 'Cancelled', 32.00, 'Pune', NULL),
(7, '2025-03-20', 'Delivered', 94.00, 'Goa', '2025-03-25'),
(8, '2025-03-22', 'Paid', 29.99, 'Chennai', NULL),
(9, '2025-02-10', 'Delivered', 15.00, 'Jaipur', '2025-02-14'),
(10, '2025-03-02', 'Delivered', 90.00, 'Nagpur', '2025-03-07'),
(11, '2025-03-28', 'Shipped', 79.00, 'Kochi', NULL),
(12, '2025-01-30', 'Delivered', 22.00, 'Indore', '2025-02-03');
Order Items
seed_data.sql
INSERT INTO order_item (order_id, product_id, quantity, unit_price, discount) VALUES
(1, 1, 1, 59.99, 0),
(1, 8, 1, 29.99, 0),
(2, 4, 1, 39.50, 0),
(3, 3, 1, 89.00, 0),
(4, 5, 1, 24.00, 0),
(5, 1, 1, 59.99, 0),
(6, 6, 1, 32.00, 0),
(7, 10, 1, 79.00, 0),
(7, 7, 1, 15.00, 0),
(8, 8, 1, 29.99, 0),
(9, 7, 1, 15.00, 0),
(10, 9, 2, 45.00, 0),
(11, 10, 1, 79.00, 0),
(12, 11, 1, 22.00, 0);
Transactions
seed_data.sql
INSERT INTO transactions (order_id, transaction_time, payment_method, amount, status) VALUES
(1, '2025-03-02 10:15:00', 'Card', 89.98, 'Captured'),
(2, '2025-03-05 12:40:00', 'UPI', 39.50, 'Captured'),
(3, '2025-03-08 09:22:00', 'Card', 89.00, 'Captured'),
(4, '2025-02-25 18:05:00', 'Card', 24.00, 'Captured'),
(5, '2025-03-15 11:03:00', 'NetBanking', 59.99, 'Failed'),
(6, '2025-01-18 08:55:00', 'Card', 32.00, 'Refunded'),
(7, '2025-03-20 14:30:00', 'UPI', 94.00, 'Captured'),
(8, '2025-03-22 16:10:00', 'Wallet', 29.99, 'Captured'),
(9, '2025-02-10 10:00:00', 'Card', 15.00, 'Captured'),
(10, '2025-03-02 19:45:00', 'Card', 90.00, 'Captured'),
(11, '2025-03-28 09:20:00', 'Card', 79.00, 'Captured'),
(12, '2025-01-30 13:15:00', 'UPI', 22.00, 'Captured');
Once the seed data is in place, try joining customer, orders, order_item, and product to confirm the relationships, or group orders by status to practice aggregate queries.