E-Commerce Order System

E-Commerce Order System

(Order & Payment Flow Project)

  1. User registers
  2. Products are added
  3. User adds items to cart
  4. Order is placed
  5. Payment is made
  6. Order summary is generated
  7. User can view order history

STEP 1: Create Database

				
					CREATE DATABASE ecommerce_db;

-- Verify database
SHOW DATABASES;

STEP 2: Use Database

				
					USE ecommerce_db;

-- Check current database
SELECT DATABASE();

STEP 3: Create Users Table

Stores customer details.

Fields:

  • user_id
  • user_name
  • email
  • phone
  • registration_date
				
					CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    user_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(15),
    registration_date DATE
);

-- Check structure
DESC users;

STEP 4: Insert Users

				
					INSERT INTO users (user_name, email, phone, registration_date) VALUES
('Aman', 'aman@gmail.com', '9876543210', '2026-03-01'),
('Priya', 'priya@gmail.com', '9876501234', '2026-03-02');

-- View users
SELECT * FROM users;

STEP 5: Create Products Table

Stores product details.

Fields:

  • product_id
  • product_name
  • price
  • stock_quantity
				
					CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100),
    price DECIMAL(10,2),
    stock_quantity INT
);

-- Check structure
DESC products;

STEP 6: Insert Products

				
					INSERT INTO products (product_name, price, stock_quantity) VALUES
('Laptop', 45000, 10),
('Mobile', 20000, 15),
('Headphones', 1500, 50);

-- View products
SELECT * FROM products;

STEP 7: Create Cart Table

Temporary cart before order is placed.

Fields:

  • cart_id
  • user_id
  • product_id
  • quantity
				
					CREATE TABLE cart (
    cart_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    product_id INT,
    quantity INT,

    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Check structure
DESC cart;

STEP 8: Add Items to Cart

User adds products to cart.

				
					INSERT INTO cart (user_id, product_id, quantity) VALUES
(1, 1, 1),
(1, 3, 2);

-- View cart items
SELECT * FROM cart;

STEP 9: Create Orders Table

Stores order summary.

Fields:

  • order_id
  • user_id
  • order_date
  • total_amount
				
					CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2),

    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- Check structure
DESC orders;

STEP 10: Place Order (Calculate Total)

Calculate total from cart and insert into orders table.

				
					INSERT INTO orders (user_id, order_date, total_amount)
SELECT 
    cart.user_id,
    '2026-03-10',
    SUM(cart.quantity * products.price)
FROM cart
JOIN products ON cart.product_id = products.product_id
WHERE cart.user_id = 1;

-- View orders
SELECT * FROM orders;

STEP 11: Update Stock After Order

Reduce stock based on cart quantity.

				
					UPDATE products
JOIN cart ON products.product_id = cart.product_id
SET products.stock_quantity = products.stock_quantity - cart.quantity
WHERE cart.user_id = 1;

-- Check updated stock
SELECT product_name, stock_quantity FROM products;

STEP 12: Create Payments Table

Stores payment details.

Fields:

  • payment_id
  • order_id
  • payment_date
  • payment_method
  • payment_status
				
					CREATE TABLE payments (
    payment_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    payment_date DATE,
    payment_method VARCHAR(50),
    payment_status VARCHAR(50),

    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- Check structure
DESC payments;

STEP 13: Record Payment

				
					INSERT INTO payments (order_id, payment_date, payment_method, payment_status)
VALUES (1, '2026-03-10', 'UPI', 'Completed');

-- View payments
SELECT * FROM payments;

STEP 14: Order Summary Report

Show full order summary including user, products, and payment.

				
					SELECT users.user_name,
       orders.order_id,
       orders.total_amount,
       payments.payment_method,
       payments.payment_status
FROM orders
JOIN users ON orders.user_id = users.user_id
JOIN payments ON orders.order_id = payments.order_id;

-- Order summary displayed

STEP 15: User Order History

Show all orders placed by a user.

				
					SELECT users.user_name,
       orders.order_id,
       orders.order_date,
       orders.total_amount
FROM orders
JOIN users ON orders.user_id = users.user_id
WHERE users.user_id = 1;

-- Order history displayed

🎯 Concepts Covered

  • Primary key
  • Foreign key
  • Order flow
  • Cart logic
  • Stock update
  • SUM
  • JOIN
  • Order summary
  • Payment system
  • Order history
  • Multi-table relationships