E-Commerce Database Schema Diagram with Code Examples

If you've ever tried to build an online store and realized your product data, customer info, and order history are a tangled mess, you already know why a well-structured database schema matters. A database schema diagram code example for e-commerce application gives you a real, working blueprint not abstract theory, but actual SQL you can adapt and use. Get the schema wrong, and you'll fight bugs, slow queries, and scaling headaches for months. Get it right, and your store runs on solid ground.

What Does a Database Schema Diagram Actually Show?

A database schema diagram is a visual map of your tables, columns, data types, and relationships. In an e-commerce context, it shows how products connect to categories, how orders link to customers, and how payments tie back to transactions. The "code example" part means you're not just looking at boxes and arrows you're getting the SQL statements that create those tables and define those relationships.

Think of it as the difference between an architect's sketch and the actual building plans with measurements. The diagram helps you understand the structure. The code lets you build it.

Why Do Developers Search for E-Commerce Schema Examples?

Most developers looking for this are in one of these situations:

Starting a new online store project and need a proven foundation to build on
Learning database design through a practical, real-world example
Refactoring an existing e-commerce database that has grown messy over time
Preparing for a technical interview where they need to design a schema on the spot
Evaluating an ORM or framework and want to see what the underlying tables should look like

Whatever the reason, the core need is the same: a reliable reference that covers the essential tables and relationships every online store needs.

What Tables Should an E-Commerce Database Schema Include?

A functional e-commerce schema typically needs at least these core entities:

Users / Customers accounts, contact info, shipping addresses
Products name, description, price, SKU, stock quantity
Categories product organization and hierarchy
Orders order status, date, total, linked customer
Order Items individual line items within an order (quantity, price at time of purchase)
Payments payment method, amount, transaction status
Reviews / Ratings customer feedback tied to products

Below is a working SQL example you can run on MySQL or PostgreSQL. This covers the essential tables with proper foreign keys and constraints.

Users Table


CREATE TABLE users (
 user_id INT PRIMARY KEY AUTO_INCREMENT,
 first_name VARCHAR(100) NOT NULL,
 last_name VARCHAR(100) NOT NULL,
 email VARCHAR(255) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 phone VARCHAR(20),
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Addresses Table


CREATE TABLE addresses (
 address_id INT PRIMARY KEY AUTO_INCREMENT,
 user_id INT NOT NULL,
 address_line1 VARCHAR(255) NOT NULL,
 address_line2 VARCHAR(255),
 city VARCHAR(100) NOT NULL,
 state VARCHAR(100),
 postal_code VARCHAR(20) NOT NULL,
 country VARCHAR(100) NOT NULL,
 is_default BOOLEAN DEFAULT FALSE,
 FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

Categories Table


CREATE TABLE categories (
 category_id INT PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(150) NOT NULL,
 slug VARCHAR(150) NOT NULL UNIQUE,
 parent_category_id INT DEFAULT NULL,
 FOREIGN KEY (parent_category_id) REFERENCES categories(category_id)
);

The parent_category_id column creates a self-referencing relationship, which lets you build nested category trees like Electronics → Laptops → Gaming Laptops.

Products Table


CREATE TABLE products (
 product_id INT PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(255) NOT NULL,
 slug VARCHAR(255) NOT NULL UNIQUE,
 description TEXT,
 price DECIMAL(10, 2) NOT NULL,
 sku VARCHAR(100) UNIQUE,
 stock_quantity INT NOT NULL DEFAULT 0,
 category_id INT,
 image_url VARCHAR(500),
 is_active BOOLEAN DEFAULT TRUE,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

Orders and Order Items Tables


CREATE TABLE orders (
 order_id INT PRIMARY KEY AUTO_INCREMENT,
 user_id INT NOT NULL,
 shipping_address_id INT NOT NULL,
 order_status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
 total_amount DECIMAL(10, 2) NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY (user_id) REFERENCES users(user_id),
 FOREIGN KEY (shipping_address_id) REFERENCES addresses(address_id)
);

CREATE TABLE order_items (
 order_item_id INT PRIMARY KEY AUTO_INCREMENT,
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL DEFAULT 1,
 unit_price DECIMAL(10, 2) NOT NULL,
 FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
 FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Notice that order_items stores unit_price separately from the product's current price. This is critical the product price might change later, but the order record needs to reflect what the customer actually paid.

Payments Table


CREATE TABLE payments (
 payment_id INT PRIMARY KEY AUTO_INCREMENT,
 order_id INT NOT NULL,
 payment_method ENUM('credit_card', 'debit_card', 'paypal', 'stripe', 'bank_transfer') NOT NULL,
 amount DECIMAL(10, 2) NOT NULL,
 status ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'pending',
 transaction_id VARCHAR(255),
 paid_at TIMESTAMP,
 FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

Reviews Table


CREATE TABLE reviews (
 review_id INT PRIMARY KEY AUTO_INCREMENT,
 product_id INT NOT NULL,
 user_id INT NOT NULL,
 rating TINYINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
 comment TEXT,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (product_id) REFERENCES products(product_id),
 FOREIGN KEY (user_id) REFERENCES users(user_id)
);

How Do These Tables Relate to Each Other?

The relationships follow standard patterns:

One-to-Many: One user has many orders. One order has many order items. One category has many products.
Many-to-One: Many products belong to one category. Many payments belong to one order.
Self-referencing: Categories can have parent categories, creating a tree hierarchy.
One-to-One (with constraints): Each order typically has one primary shipping address.

If you want to see how these relationships translate into visual diagrams and additional code patterns, our breakdown of database schema diagram code examples for e-commerce applications covers more variations and edge cases.

What Common Mistakes Show Up in E-Commerce Schemas?

After reviewing and building several e-commerce databases, here are the errors that come up most often:

Not storing the price at time of purchase. If you only reference the product table for price, historical orders become inaccurate when prices change.
Putting everything in one table. A single "orders" table that stores product info, customer info, and payment details creates redundancy and update anomalies.
Ignoring indexing on foreign keys and search columns. Queries on user_id, product_id, and order_status will be slow on large datasets without indexes.
Using soft deletes inconsistently. If you use is_active on products but hard-delete categories, your queries break.
Forgetting about product variants. If you sell t-shirts in multiple sizes and colors, a single products table won't cut it. You need a product_variants table with its own SKU, price, and stock.

Should You Use SQL Notation or a Visual Tool?

Both have their place. SQL notation (like the CREATE TABLE statements above) is what actually builds your database. Visual diagrams help you communicate the design to your team before writing code. Tools like dbdiagram.io and MySQL Workbench can generate diagrams from SQL, or generate SQL from diagrams.

If you need a deeper explanation of SQL notation used in schema diagrams, we cover that in our article on SQL notation in database schema diagrams.

How Does This Schema Hold Up at Scale?

The schema above works well for small to mid-size stores. Once you're handling hundreds of thousands of products or millions of orders, you'll need to think about:

Partitioning the orders and order_items tables by date
Adding read replicas for product catalog queries
Separating the product search layer into something like Elasticsearch
Denormalizing some data for frequently accessed views (like product listing pages with ratings)

For a detailed look at designing schemas that handle large traffic volumes, check out our guide on best practices for large-scale database systems.

What Should You Do After Setting Up the Schema?

Once you've created the tables, your next steps are:

Add indexes at minimum, index all foreign key columns and any column used in WHERE clauses (email, slug, order_status, sku).
Write seed data insert sample users, products, and orders to test your queries and application logic.
Set up migrations use a tool like Flyway, Alembic, or Laravel Migrations so schema changes are version-controlled.
Test with realistic data volume a schema that works with 10 products may collapse with 100,000. Load-test early.
Document your relationships keep your diagram updated as the schema evolves. A diagram from six months ago that doesn't match reality is worse than no diagram.

Quick Checklist Before You Ship

☑ Every table has a primary key
☑ All foreign keys have corresponding indexes
☑ Order items store the price at time of purchase, not just a product reference
☑ Timestamps (created_at, updated_at) exist on user-facing tables
☑ Enum values for status fields cover all expected states
☑ Product variants are handled if your catalog needs them
☑ The schema has been reviewed against at least one production-grade e-commerce example

Tip: Start with this schema, get your store running, and iterate. The worst database design mistake is over-engineering before you have real users and real data to guide your decisions. Build for what you need now, and refactor when the data tells you it's time.