MySQL E-Commerce Database Project

A learning-grade relational database schema for a realistic e-commerce application, built entirely in MySQL. The project models the seven core tables that almost every online store needs — users, products, carts, orders, inventory, warehouses, and wishlists — with proper primary keys, foreign keys, generated columns, ENUMs, indexes, and seed data.

It is delivered as a set of MySQL Shell Notebooks (.mysql-notebook files), one per table, so you can open each one in MySQL Workbench / MySQL Shell for VS Code and execute the cells block-by-block to see the schema come to life.

What is in this project

MySql Ecommerce Project/
├── UsersTable.mysql-notebook        # users
├── ProductTable.mysql-notebook      # products (FK -> users as vendors)
├── CartTable.mysql-notebook         # carts + cart_items
├── OrderTable.mysql-notebook        # orders + order_items
├── WarehouseTable.mysql-notebook    # warehouses (placeholder for warehouse DDL)
├── InventoryTable.mysql-notebook    # inventory (M:N bridge: products x warehouses)
└── WishlistTable.mysql-notebook     # wishlists (M:N bridge: users x products)

Every notebook contains, in order:

A CREATE TABLE statement for the table(s) it owns
DESC / SHOW TABLES checks so you can verify the schema
A START TRANSACTION; block followed by bulk INSERT statements with realistic seed data (hundreds to thousands of rows)
Sanity-check SELECT queries

Each table uses CHAR(36) UUIDs as primary keys, soft-delete columns where appropriate, ENUMs for fixed-value status fields, and tight foreign-key constraints with explicit ON DELETE behavior.

Prerequisites

Tool	Version	Why
MySQL Server	8.0+	Schema uses `GENERATED ALWAYS AS ... STORED`, `CHECK` constraints, `JSON` columns, and `DEFAULT (UUID())` — all of which require 8.0+
MySQL Shell or MySQL Workbench	latest	Required to open `.mysql-notebook` files. Plain `mysql` CLI cannot open notebooks.
(optional) VS Code + MySQL Shell for VS Code extension	latest	Lets you open the notebooks directly inside VS Code

Why MySQL 8 specifically: tables use JSON (orders.shipping_address), stored generated columns (cart_items.line_total, inventory.quantity_available, carts.is_active_cart), and CHECK constraints — none of which work reliably on MySQL 5.7 or earlier.

Setup — from zero to a running database

1. Install MySQL 8

Windows: download the MySQL Installer from https://dev.mysql.com/downloads/installer/ and pick "Server + Workbench + Shell". Set a root password you will remember.
macOS: brew install mysql then brew services start mysql.
Linux: sudo apt install mysql-server (Debian/Ubuntu) or equivalent.

Verify the server is up:

mysql --version
mysql -u root -p -e "SELECT VERSION();"

You should see 8.0.x or 8.4.x.

2. Create the database

The first notebook (UsersTable.mysql-notebook) does this for you with:

CREATE DATABASE Ecommerce;
USE ecommerce;

If you would rather pre-create it from the CLI:

CREATE DATABASE IF NOT EXISTS ecommerce
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_0900_ai_ci;

3. Open the project folder

In MySQL Workbench: File → Open SQL Script… does not work for notebooks. Instead use File → Open MySQL Shell Notebook… and pick a .mysql-notebook file.

In VS Code with the MySQL Shell for VS Code extension installed: just open the project folder — the notebooks render as interactive cells.

4. Connect to your MySQL server

Inside Workbench / VS Code, create a connection:

Host: 127.0.0.1
Port: 3306
User: root (or a dedicated user you created)
Default schema: ecommerce

How to run the notebooks

Each .mysql-notebook file is a sequence of SQL cells. Inside MySQL Shell / Workbench:

Click the first cell.
Press Ctrl + Enter (or the ▶ button) to execute that cell.
Move to the next cell and repeat.

You can also select Run All to execute every cell in the notebook in order.

Recommended load order

Foreign keys force a strict order. Run the notebooks in this sequence, top to bottom, the first time you set the database up:

#	Notebook	Creates	Depends on
1	`UsersTable.mysql-notebook`	`users`	—
2	`ProductTable.mysql-notebook`	`products`	`users` (vendor FK)
3	`WarehouseTable.mysql-notebook`	`warehouses`	—
4	`InventoryTable.mysql-notebook`	`inventory`	`products`, `warehouses`
5	`CartTable.mysql-notebook`	`carts`, `cart_items`	`users`, `products`
6	`OrderTable.mysql-notebook`	`orders`, `order_items`	`users`, `products`, `carts`
7	`WishlistTable.mysql-notebook`	`wishlists`	`users`, `products`

The very last cell in CartTable.mysql-notebook adds an ALTER TABLE that links carts.converted_order_id back to orders.id. Run it after the orders notebook so the referenced table exists.

The WarehouseTable.mysql-notebook is currently a placeholder (just \about). If you do not have a warehouses table yet, create a minimal one before running the inventory notebook — see Troubleshooting.

The schema in detail

`users`

The identity table for everyone in the system — customers, admins, vendors, and support staff.

Column	Type	Notes
`id`	`CHAR(36)` PK	UUID, default `(UUID())`
`email`, `phone_number`	`VARCHAR`	Unique
`password_hash`	`VARCHAR(255)`	Bcrypt-shaped placeholder
`role`	`ENUM('customer','admin','vendor','support')`	Drives authorization
`status`	`ENUM('active','inactive','suspended','banned')`	Account lifecycle
`is_email_verified`, `is_phone_verified`, `two_factor_enabled`	`TINYINT(1)`	Booleans
`email_verification_token`, `password_reset_token`, `password_reset_expires`	—	Auth flow state
`loyalty_points`, `marketing_opt_in`, `preferred_locale`, `currency`, `avatar_url`, `date_of_birth`	—	Commerce / preferences
`created_at`, `updated_at`, `deleted_at`	`DATETIME`	Soft-delete pattern

Indexes: unique on email and phone_number; secondary on status, role, deleted_at.

`products`

The product catalog. Each product can be owned by a vendor (a row in users with role = 'vendor').

Notable columns: sku and slug (both unique), price, compare_at_price (the "was" price for sale displays), cost_price (margin tracking), stock_quantity + low_stock_threshold (denormalized stock summary; the real per-warehouse stock lives in inventory), status ENUM (draft/active/inactive/out_of_stock/archived), and a soft-delete deleted_at.

FK: vendor_id → users(id) ON DELETE SET NULL.

`carts` and `cart_items`

A shopping cart with line items.

carts.status is active / converted / abandoned / merged.
A user can only have one active cart at a time. This is enforced elegantly with a generated column:
```
is_active_cart TINYINT GENERATED ALWAYS AS
    (CASE WHEN status = 'active' THEN 1 ELSE NULL END) STORED,
UNIQUE KEY uq_user_active_cart (user_id, is_active_cart)
```
Because non-active carts have is_active_cart = NULL, they are exempt from the unique constraint — only the active ones are constrained.
cart_items has a unique (cart_id, product_id) pair, so re-adding the same product bumps quantity instead of duplicating rows.
cart_items.line_total is a stored generated column = unit_price * quantity.

`orders` and `order_items`

Orders are immutable snapshots — once placed, prices and addresses are captured so changes to products or user addresses do not retroactively rewrite history.

order_number is a human-facing identifier (e.g. ORD-2026-000123) generated by your app.
status ENUM covers the full lifecycle: pending / confirmed / processing / shipped / delivered / cancelled / refunded / returned.
payment_status is tracked separately from order status so payment state (authorized, paid, failed, refunded, etc.) can evolve independently.
Money is split into subtotal / discount_amount / tax_amount / shipping_amount / total_amount, each DECIMAL(12,2), all guarded by a CHECK constraint that none can go negative.
shipping_address is a JSON column — the full address is snapshotted into the order row.
order_items.line_total is a stored generated column = unit_price * quantity - discount_amount + tax_amount.
order_items.product_id uses ON DELETE SET NULL so the order history survives a product deletion. The product_name / product_sku snapshot columns keep the line readable even after the FK goes null.

`warehouses`

Physical (or virtual) stock locations. The notebook ships as a placeholder — add columns that match your business (name, code, city, country, address, is_active, etc.). At minimum you need:

CREATE TABLE warehouses (
    id   CHAR(36) NOT NULL DEFAULT (UUID()),
    name VARCHAR(255) NOT NULL,
    code VARCHAR(50)  NOT NULL UNIQUE,
    PRIMARY KEY (id)
);

…before InventoryTable.mysql-notebook will run.

`inventory`

The many-to-many bridge between products and warehouses that carries the stock numbers.

One row per (warehouse_id, product_id) — enforced by a unique key.
quantity_on_hand and quantity_reserved are the inputs.
quantity_available is a stored generated column = quantity_on_hand - quantity_reserved.
reorder_point and reorder_quantity drive automated restocking logic.
bin_location is a free-form aisle/shelf code (e.g. A-04-2).
FKs cascade-delete: removing a warehouse or product wipes the corresponding inventory rows.

`wishlists`

The many-to-many bridge between users and products for "save for later".

Unique on (user_id, product_id) — a user can't add the same product twice.
priority ENUM (low / medium / high), optional note, and two notification flags: notify_on_restock, notify_on_price_drop.
Both FKs cascade-delete.

Entity relationships

The diagram below is laid out in landscape orientation — users on the left, the order/cart flow through the middle, and the warehouse/inventory cluster on the right.

%%{init: {'theme':'neutral', 'er':{'layoutDirection':'LR'}}}%%
erDiagram
    USERS      ||--o{ CARTS       : "places"
    USERS      ||--o{ ORDERS      : "places"
    USERS      ||--o{ WISHLISTS   : "saves"
    USERS      ||--o{ PRODUCTS    : "vendors"

    CARTS      ||--o{ CART_ITEMS  : "contains"
    CARTS      |o--o| ORDERS      : "converts to"

    ORDERS     ||--o{ ORDER_ITEMS : "contains"

    PRODUCTS   ||--o{ CART_ITEMS  : "added as"
    PRODUCTS   ||--o{ ORDER_ITEMS : "sold as"
    PRODUCTS   ||--o{ WISHLISTS   : "wished as"
    PRODUCTS   ||--o{ INVENTORY   : "stocked as"

    WAREHOUSES ||--o{ INVENTORY   : "stores"

    USERS {
        CHAR36   id PK
        VARCHAR  email UK
        VARCHAR  phone_number UK
        ENUM     role
        ENUM     status
        DATETIME deleted_at
    }
    PRODUCTS {
        CHAR36   id PK
        VARCHAR  sku UK
        VARCHAR  slug UK
        DECIMAL  price
        INT      stock_quantity
        ENUM     status
        CHAR36   vendor_id FK
    }
    CARTS {
        CHAR36   id PK
        CHAR36   user_id FK
        ENUM     status
        CHAR36   converted_order_id FK
        TINYINT  is_active_cart "generated"
    }
    CART_ITEMS {
        CHAR36   id PK
        CHAR36   cart_id FK
        CHAR36   product_id FK
        INT      quantity
        DECIMAL  unit_price
        DECIMAL  line_total "generated"
    }
    ORDERS {
        CHAR36   id PK
        VARCHAR  order_number UK
        CHAR36   user_id FK
        CHAR36   cart_id FK
        ENUM     status
        ENUM     payment_status
        DECIMAL  total_amount
        JSON     shipping_address
    }
    ORDER_ITEMS {
        CHAR36   id PK
        CHAR36   order_id FK
        CHAR36   product_id FK
        INT      quantity
        DECIMAL  unit_price
        DECIMAL  line_total "generated"
    }
    WISHLISTS {
        CHAR36   id PK
        CHAR36   user_id FK
        CHAR36   product_id FK
        ENUM     priority
        TINYINT  notify_on_restock
        TINYINT  notify_on_price_drop
    }
    WAREHOUSES {
        CHAR36   id PK
        VARCHAR  name
        VARCHAR  code UK
    }
    INVENTORY {
        CHAR36   id PK
        CHAR36   warehouse_id FK
        CHAR36   product_id FK
        INT      quantity_on_hand
        INT      quantity_reserved
        INT      quantity_available "generated"
        INT      reorder_point
    }

Crow's-foot notation reminder: ||--o{ means "one-to-many", |o--o| means "zero-or-one to zero-or-one" (the cart ↔ order link is optional on both sides until checkout creates the order).

ASCII fallback (landscape)

If the Mermaid block above doesn't render in your viewer, here is the same diagram in plain ASCII — flowing left → right across the page. The arrow points from the child (foreign-key side) to the parent (primary-key side).

   +---------+      +---------+      +--------------+
   |         | 1:N  |         | 1:N  |              |  N:1 (product_id)
   |         |----->|  CARTS  |----->|  CART_ITEMS  |---------+
   |         |      |         |      |              |         |
   |         |      +----+----+      +--------------+         |
   |         |           | 0..1                               |
   |         |           | (cart <--> order link)             |
   |         |           v                                    |
   |         |      +---------+      +--------------+         |   +----------+      +-----------+      +-------------+
   |         | 1:N  |         | 1:N  |              |  N:1    |   |          | 1:N  |           | 1:N  |             |
   |  USERS  |----->| ORDERS  |----->| ORDER_ITEMS  |---------+-->| PRODUCTS |<-----| INVENTORY |<-----| WAREHOUSES  |
   |         |      |         |      |              |             |          |      |           |      |             |
   |         |      +---------+      +--------------+             +----------+      +-----------+      +-------------+
   |         |                                                       ^    ^
   |         | 1:N  +-----------+                                    |    |
   |         |----->| WISHLISTS |------------- N:1 (product_id) -----+    |
   |         |      |           |                                         |
   |         |      +-----------+                                         |
   |         |                                                            |
   |         |--------------- 1:N (vendor_id) ----------------------------+
   +---------+

How to read it (left → right):

Column 1 — USERS. The single source of identity. Every arrow leaving this box is users.id being referenced as a foreign key somewhere else.
Column 2 — what a user creates. Three parallel rows: their CARTS (top), their ORDERS (middle), their WISHLISTS (bottom).
Column 3 — line tables. CART_ITEMS lives under a cart; ORDER_ITEMS lives under an order. Both are bridges that also point rightward to a product.
Column 4 — PRODUCTS. The catalog. Five arrows converge here: vendor (from users), cart_items, order_items, wishlists, and inventory.
Columns 5 & 6 — stock side. INVENTORY is the bridge that carries per-warehouse stock numbers; WAREHOUSES sits at the far right.
The vertical link between CARTS and ORDERS is the 0..1 ↔ 0..1 cart-to-order conversion (carts.converted_order_id / orders.cart_id) — created at checkout.

Cardinalities at a glance

Parent → Child	Type	FK column	On delete
`users` → `products`	1 : N	`products.vendor_id`	`SET NULL`
`users` → `carts`	1 : N (active = 1)	`carts.user_id`	`CASCADE`
`users` → `orders`	1 : N	`orders.user_id`	`RESTRICT`
`users` → `wishlists`	1 : N	`wishlists.user_id`	`CASCADE`
`carts` → `cart_items`	1 : N	`cart_items.cart_id`	`CASCADE`
`carts` ↔ `orders`	0..1 : 0..1	`carts.converted_order_id` / `orders.cart_id`	`SET NULL`
`orders` → `order_items`	1 : N	`order_items.order_id`	`CASCADE`
`products` → `cart_items`	1 : N	`cart_items.product_id`	`CASCADE`
`products` → `order_items`	1 : N	`order_items.product_id`	`SET NULL`
`products` → `wishlists`	1 : N	`wishlists.product_id`	`CASCADE`
`products` → `inventory`	1 : N	`inventory.product_id`	`CASCADE`
`warehouses` → `inventory`	1 : N	`inventory.warehouse_id`	`CASCADE`

Implied many-to-many relationships (via the bridge tables above):

users M : N products via wishlists
users M : N products via cart_items (carts in between)
users M : N products via order_items (orders in between)
products M : N warehouses via inventory (carries stock)

Sample queries you can try

Once everything is loaded, paste these into a query window:

Top 10 customers by lifetime spend

SELECT u.id, u.email,
       SUM(o.total_amount) AS lifetime_spend,
       COUNT(*)            AS orders_count
FROM   users  u
JOIN   orders o ON o.user_id = u.id
WHERE  o.status IN ('delivered','shipped','processing','confirmed')
GROUP  BY u.id, u.email
ORDER  BY lifetime_spend DESC
LIMIT  10;

Products that are out of stock everywhere

SELECT p.id, p.sku, p.name
FROM   products p
LEFT JOIN inventory i
       ON i.product_id = p.id AND i.quantity_available > 0
WHERE  i.id IS NULL;

Active carts that have been sitting for more than 7 days (abandoned cart candidates)

SELECT c.id, c.user_id, c.updated_at,
       COUNT(ci.id) AS items, SUM(ci.line_total) AS cart_value
FROM   carts c
JOIN   cart_items ci ON ci.cart_id = c.id
WHERE  c.status = 'active'
   AND c.updated_at < (NOW() - INTERVAL 7 DAY)
GROUP  BY c.id, c.user_id, c.updated_at
ORDER  BY cart_value DESC;

Inventory items at or below their reorder point

SELECT w.id   AS warehouse_id,
       p.sku, p.name,
       i.quantity_available, i.reorder_point, i.reorder_quantity
FROM   inventory  i
JOIN   products   p ON p.id = i.product_id
JOIN   warehouses w ON w.id = i.warehouse_id
WHERE  i.quantity_available <= i.reorder_point
ORDER  BY i.quantity_available ASC;

Wishlist demand — which products are most-wished-for and how many of those wishers want a restock alert?

SELECT p.sku, p.name,
       COUNT(*)                                AS wished_by,
       SUM(w.notify_on_restock)                AS wants_restock_ping,
       SUM(w.notify_on_price_drop)             AS wants_price_drop_ping
FROM   wishlists w
JOIN   products  p ON p.id = w.product_id
GROUP  BY p.id, p.sku, p.name
ORDER  BY wished_by DESC
LIMIT  20;

Resetting the database

To start over from scratch:

DROP DATABASE ecommerce;
CREATE DATABASE ecommerce;
USE ecommerce;

Then re-run the notebooks in the recommended order.

If you only need to wipe the data but keep the schema:

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE order_items;
TRUNCATE TABLE orders;
TRUNCATE TABLE cart_items;
TRUNCATE TABLE carts;
TRUNCATE TABLE wishlists;
TRUNCATE TABLE inventory;
TRUNCATE TABLE warehouses;
TRUNCATE TABLE products;
TRUNCATE TABLE users;
SET FOREIGN_KEY_CHECKS = 1;

Troubleshooting

ERROR 1822: Failed to add the foreign key constraint. Missing index for constraint You ran the notebooks out of order — the referenced table does not exist yet. Follow the load order.

ERROR 3819: Check constraint is violated The seed INSERT includes a row that would make a monetary column negative. Either fix the row or temporarily disable the check (not recommended in production):

SET @@SESSION.check_constraint_checks = 0;

ERROR 1452: Cannot add or update a child row: a foreign key constraint fails A row references a user_id / product_id / warehouse_id that has not been inserted yet. Re-run the parent table's notebook first.

Table 'ecommerce.warehouses' doesn't exist while loading inventory The WarehouseTable.mysql-notebook is a placeholder. Run the minimal CREATE TABLE warehouses (...) snippet shown in the warehouses section, then re-run the inventory notebook.

MySQL Workbench shows Statement is unsafe because it uses a system function that may return a different value on the replication slave. Harmless warning on DEFAULT (UUID()) when binary logging is on with statement-based replication. Either ignore, or switch to row-based:

SET GLOBAL binlog_format = 'ROW';

ENUM values not accepted ENUM values are case-sensitive in storage but case-insensitive in comparison. Use the lowercase value spelled exactly as in the schema (e.g. 'customer', not 'Customer').

Cannot delete or update a parent row: a foreign key constraint fails Some FKs are ON DELETE RESTRICT on purpose (e.g. orders.user_id). Soft-delete the user by setting users.deleted_at = NOW() instead of DELETE-ing.

License

Educational project — the schema, seed data, and notebooks are free to copy, fork, and modify.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Repository files navigation

MySQL E-Commerce Database Project

Table of contents

What is in this project

Prerequisites

Setup — from zero to a running database

1. Install MySQL 8

2. Create the database

3. Open the project folder

4. Connect to your MySQL server

How to run the notebooks

Recommended load order

The schema in detail

`users`

`products`

`carts` and `cart_items`

`orders` and `order_items`

`warehouses`

`inventory`

`wishlists`

Entity relationships

ASCII fallback (landscape)

Cardinalities at a glance

Sample queries you can try

Resetting the database

Troubleshooting

License

About

Uh oh!

Releases

Packages

Uh oh!

Contributors

Uh oh!

Name		Name	Last commit message	Last commit date
Latest commit History 6 Commits
CartTable.mysql-notebook		CartTable.mysql-notebook
InventoryTable.mysql-notebook		InventoryTable.mysql-notebook
OrderTable.mysql-notebook		OrderTable.mysql-notebook
ProductTable.mysql-notebook		ProductTable.mysql-notebook
README.md		README.md
UsersTable.mysql-notebook		UsersTable.mysql-notebook
WarehouseTable.mysql-notebook		WarehouseTable.mysql-notebook
WishlistTable.mysql-notebook		WishlistTable.mysql-notebook
index.mysql-notebook		index.mysql-notebook

Folders and files

Latest commit

History

Repository files navigation

MySQL E-Commerce Database Project

Table of contents

What is in this project

Prerequisites

Setup — from zero to a running database

1. Install MySQL 8

2. Create the database

3. Open the project folder

4. Connect to your MySQL server

How to run the notebooks

Recommended load order

The schema in detail

users

products

carts and cart_items

orders and order_items

warehouses

inventory

wishlists

Entity relationships

ASCII fallback (landscape)

Cardinalities at a glance

Sample queries you can try

Resetting the database

Troubleshooting

License

About

Topics

Resources

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Uh oh!

Contributors

Uh oh!

`users`

`products`

`carts` and `cart_items`

`orders` and `order_items`

`warehouses`

`inventory`

`wishlists`

Packages