E-commerce Order System
What you'll build
A full e-commerce schema with four tables: customers, products, orders, and order_items (a junction table with extra data like quantity and price).
Goals
- Design a many-to-many relationship with a junction table
- Use foreign keys to enforce referential integrity
- Store historical prices in order_items (price at time of order)
- Write queries to retrieve complete order details
Prerequisites
- Foreign keys and relationships
- JOIN queries
- Understanding of one-to-many and many-to-many
Steps
- 1
Create customers and products
Create the customers and products tables with basic fields.
CodeCREATE TABLE customers ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE NOT NULL ); CREATE TABLE products ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, price NUMERIC(10,2) NOT NULL CHECK (price > 0) );Expected result
Customers and products tables are created.
- 2
Create orders and order_items
Create the orders table (header) and order_items table (line items). order_items is a junction table that also stores the price at the time of purchase.
CodeCREATE TABLE orders ( id SERIAL PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), order_date TIMESTAMP DEFAULT NOW() ); CREATE TABLE order_items ( id SERIAL PRIMARY KEY, order_id INTEGER REFERENCES orders(id), product_id INTEGER REFERENCES products(id), quantity INTEGER NOT NULL CHECK (quantity > 0), unit_price NUMERIC(10,2) NOT NULL -- price at time of order );Expected result
Orders and order_items tables are created with proper foreign keys.
- 3
Insert an order with items
Insert a customer, two products, an order, and two order items. Store the current product price in unit_price.
CodeINSERT INTO customers (name, email) VALUES ('Ada', '[email protected]'); INSERT INTO products (name, price) VALUES ('Widget', 9.99), ('Gadget', 19.99); INSERT INTO orders (customer_id) VALUES (1); INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (1, 1, 3, 9.99), -- 3 Widgets at $9.99 (1, 2, 1, 19.99); -- 1 Gadget at $19.99Expected result
A complete order with two line items is created.
- 4
Query the full order
Write a query that joins all four tables to show the complete order: customer name, order date, product names, quantities, and line totals.
CodeSELECT c.name AS customer, o.order_date, p.name AS product, oi.quantity, oi.unit_price, (oi.quantity * oi.unit_price) AS line_total FROM orders o INNER JOIN customers c ON o.customer_id = c.id INNER JOIN order_items oi ON oi.order_id = o.id INNER JOIN products p ON oi.product_id = p.id WHERE o.id = 1;Expected result
A complete order report with customer, products, quantities, and totals.
- 5
Add indexes to optimize queries
Create indexes on frequently queried columns to improve query performance. Indexes are the primary means of database performance optimization, but be mindful of write overhead.
Code-- Create indexes on frequently filtered columns CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_orders_user_id ON orders(user_id); CREATE INDEX idx_orders_created_at ON orders(created_at DESC); -- Verify the index is used EXPLAIN SELECT * FROM orders WHERE user_id = 42;Expected result
EXPLAIN output shows an index scan using idx_orders_user_id instead of a full table scan.
Try it yourself
What you learned
You designed a real e-commerce schema with a many-to-many relationship via a junction table, stored historical data (prices at order time), and wrote a four-table join query.
Related Resources
Related Lessons
- Relationships
Learn about database relationships.
- Joins
Learn multi-table joins.
Related Glossary
- Foreign Key
Table relationships.
- JOIN
Combining tables.
Related Glossary
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
- Foreign Key
Table relationships.
- JOIN
Combining tables.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.