Skip to content

E-Commerce Database Design

Medium ~60 min

What you'll build

A full e-commerce schema with users, products, categories, carts, orders, payments, and reviews — normalized to 3NF with appropriate constraints and indexes.

Goals

  • Design a normalized schema (3NF) for e-commerce
  • Model one-to-many and many-to-many relationships
  • Apply constraints: CHECK, UNIQUE, NOT NULL, FK
  • Plan an indexing strategy for common queries

Prerequisites

  • CREATE TABLE and foreign keys
  • Normalization concepts (1NF, 2NF, 3NF)
  • Many-to-many junction tables

Steps

  1. 1

    Design the user and address tables

    Create users and addresses tables. A user can have multiple addresses (shipping, billing). Acceptance: users table has email unique constraint; addresses has a user_id FK and an address_type CHECK constraint.

  2. 2

    Design products and categories

    Create categories (with self-reference for subcategories) and products. Products belong to a category and have price, stock, SKU. Acceptance: category has parent_id self-FK; products has a unique SKU.

  3. 3

    Design the cart and order tables

    Create carts and cart_items for the shopping cart, and orders and order_items for placed orders. An order is a snapshot — it copies the price at order time. Acceptance: order_items has a unit_price column independent of the current product price.

  4. 4

    Design payments and reviews

    Create a payments table linked to orders (with status and method) and a reviews table linked to products and users. Acceptance: payments has a CHECK on status; reviews has a 1-5 rating CHECK constraint and a unique (user_id, product_id) constraint.

  5. 5

    Plan the indexing strategy

    Write the CREATE INDEX statements for the most common queries: product search by category, order lookup by user, review lookup by product. Acceptance: each index targets a column used in a WHERE or JOIN clause in the common queries.

Try it yourself

What you learned

You designed a complete normalized e-commerce schema — the same process behind every online store's database. You modeled relationships, applied constraints for data integrity, and planned indexes for query performance.

Related Resources

Related Lessons

Related Glossary

Related Glossary

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors

← Back to topic