E-Commerce Database Design
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
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.
Hint
Use a CHECK constraint: CHECK (address_type IN ('shipping', 'billing')).
- 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.
Hint
Self-referencing FK: parent_id INTEGER REFERENCES categories(id) allows a category tree.
- 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.
Hint
Snapshot pattern: order_items stores unit_price and quantity, not just a product_id reference, so historical prices are preserved.
- 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.
Hint
CHECK (rating BETWEEN 1 AND 5) and UNIQUE(user_id, product_id) prevents duplicate reviews.
- 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.
Hint
Index foreign keys (they're used in JOINs) and frequently filtered columns (e.g., products.category_id, orders.user_id).
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
- Normalization
Normal forms and design.
Related Glossary
- Normalization
Reduce redundancy.
- Foreign Key
Referential integrity.
Related Glossary
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
- Normalization
Reduce redundancy.
- Foreign Key
Referential integrity.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.