Skip to content

E-commerce Order System

Medium ~25 min

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. 1

    Create customers and products

    Create the customers and products tables with basic fields.

    Code
    CREATE 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. 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.

    Code
    CREATE 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. 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.

    Code
    INSERT 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.99

    Expected result

    A complete order with two line items is created.

  4. 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.

    Code
    SELECT 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. 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

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