-- Create the e-commerce database
CREATE DATABASE ecommerce;
USE ecommerce;
-- Users Table
CREATE TABLE Users (
user_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
phone VARCHAR(20),
address TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- Categories Table
CREATE TABLE Categories (
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description TEXT,
parent_id INT DEFAULT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (parent_id) REFERENCES Categories(category_id) ON DELETE SET NULL
);
-- Products Table
CREATE TABLE Products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10, 2) NOT NULL,
stock_quantity INT DEFAULT 0,
category_id INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES Categories(category_id) ON DELETE SET NULL
);
-- Orders Table
CREATE TABLE Orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
status VARCHAR(50) DEFAULT 'Pending',
total_amount DECIMAL(10, 2) NOT NULL,
shipping_address TEXT,
payment_id INT DEFAULT NULL,
FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE
);
-- Order_Items Table
CREATE TABLE Order_Items (
order_item_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT NOT NULL,
price DECIMAL(10, 2) NOT NULL,
total_price DECIMAL(10, 2) GENERATED ALWAYS AS (quantity * price) STORED,
FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE CASCADE
);
-- Payments Table
CREATE TABLE Payments (
payment_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT,
payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
payment_method VARCHAR(50),
status VARCHAR(50) DEFAULT 'Completed',
amount DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE CASCADE
);
-- Product_Images Table
CREATE TABLE Product_Images (
image_id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT,
image_url VARCHAR(255) NOT NULL,
alt_text VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE CASCADE
);
-- Reviews Table
CREATE TABLE Reviews (
review_id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT,
user_id INT,
rating INT CHECK (rating BETWEEN 1 AND 5),
comment TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE
);
