226 lines
8.6 KiB
SQL
226 lines
8.6 KiB
SQL
CREATE DATABASE ecommerce_test;
|
|
USE ecommerce_test;
|
|
|
|
CREATE TABLE users (
|
|
user_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
user_name VARCHAR(50),
|
|
email VARCHAR(100) NOT NULL UNIQUE,
|
|
phone VARCHAR(20),
|
|
registration_date DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
user_type ENUM('regular', 'vip', 'admin') DEFAULT 'regular',
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE products (
|
|
product_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
product_name VARCHAR(200) NOT NULL,
|
|
prod_name VARCHAR(200),
|
|
sku VARCHAR(50) UNIQUE,
|
|
product_code VARCHAR(50) UNIQUE,
|
|
description TEXT,
|
|
prod_desc TEXT,
|
|
price DECIMAL(10,2) NOT NULL,
|
|
cost_price DECIMAL(10,2),
|
|
original_price DECIMAL(10,2),
|
|
brand VARCHAR(100),
|
|
weight DECIMAL(8,3),
|
|
size VARCHAR(50),
|
|
color VARCHAR(50),
|
|
status ENUM('active', 'inactive', 'discontinued') DEFAULT 'active',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE categories (
|
|
category_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
category_name VARCHAR(100) NOT NULL,
|
|
cat_name VARCHAR(100),
|
|
parent_category_id INT,
|
|
category_level TINYINT DEFAULT 1,
|
|
category_code VARCHAR(50),
|
|
description TEXT,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
sort_order INT DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (parent_category_id) REFERENCES categories(category_id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE TABLE product_category_map (
|
|
map_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
product_id INT NOT NULL,
|
|
category_id INT NOT NULL,
|
|
is_primary BOOLEAN DEFAULT FALSE,
|
|
assigned_date DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
|
|
FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE CASCADE,
|
|
UNIQUE KEY unique_product_category (product_id, category_id)
|
|
);
|
|
|
|
CREATE TABLE inventory (
|
|
inventory_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
product_id INT NOT NULL,
|
|
warehouse_location VARCHAR(100),
|
|
stock_quantity INT DEFAULT 0,
|
|
stock_num INT DEFAULT 0,
|
|
stock_count INT DEFAULT 0,
|
|
reserved_quantity INT DEFAULT 0,
|
|
available_quantity INT DEFAULT 0,
|
|
min_stock_level INT DEFAULT 0,
|
|
max_stock_level INT DEFAULT 1000,
|
|
last_restock_date DATETIME,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
|
|
UNIQUE KEY unique_product_warehouse (product_id, warehouse_location)
|
|
);
|
|
|
|
CREATE TABLE orders (
|
|
order_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
order_number VARCHAR(50) NOT NULL UNIQUE,
|
|
order_no VARCHAR(50) UNIQUE,
|
|
order_code VARCHAR(50),
|
|
user_id INT NOT NULL,
|
|
order_status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled', 'refunded') DEFAULT 'pending',
|
|
total_amount DECIMAL(10,2) NOT NULL,
|
|
discount_amount DECIMAL(10,2) DEFAULT 0,
|
|
final_amount DECIMAL(10,2) NOT NULL,
|
|
shipping_address TEXT,
|
|
billing_address TEXT,
|
|
payment_method VARCHAR(50),
|
|
payment_status ENUM('unpaid', 'paid', 'refunded') DEFAULT 'unpaid',
|
|
order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
shipped_date DATETIME,
|
|
delivered_date DATETIME,
|
|
notes TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE RESTRICT
|
|
);
|
|
|
|
CREATE TABLE order_items (
|
|
item_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
order_id INT NOT NULL,
|
|
product_id INT NOT NULL,
|
|
quantity INT NOT NULL,
|
|
unit_price DECIMAL(10,2) NOT NULL,
|
|
total_price DECIMAL(10,2) NOT NULL,
|
|
discount_rate DECIMAL(5,2) DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
|
|
FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT
|
|
);
|
|
|
|
CREATE TABLE shopping_cart (
|
|
cart_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
user_id INT NOT NULL,
|
|
product_id INT NOT NULL,
|
|
quantity INT NOT NULL DEFAULT 1,
|
|
added_date DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
|
|
FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
|
|
UNIQUE KEY unique_user_product (user_id, product_id)
|
|
);
|
|
|
|
CREATE TABLE user_addresses (
|
|
address_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
user_id INT NOT NULL,
|
|
address_type ENUM('home', 'work', 'other') DEFAULT 'home',
|
|
recipient_name VARCHAR(100) NOT NULL,
|
|
recipient_phone VARCHAR(20) NOT NULL,
|
|
province VARCHAR(50) NOT NULL,
|
|
city VARCHAR(50) NOT NULL,
|
|
district VARCHAR(50),
|
|
street_address TEXT NOT NULL,
|
|
postal_code VARCHAR(10),
|
|
is_default BOOLEAN DEFAULT FALSE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE promotions (
|
|
promotion_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
promotion_name VARCHAR(200) NOT NULL,
|
|
promo_name VARCHAR(200),
|
|
promotion_type ENUM('percentage', 'fixed_amount', 'buy_x_get_y') NOT NULL,
|
|
promo_value DECIMAL(10,2) NOT NULL,
|
|
start_date DATETIME NOT NULL,
|
|
end_date DATETIME NOT NULL,
|
|
min_order_amount DECIMAL(10,2) DEFAULT 0,
|
|
max_discount_amount DECIMAL(10,2),
|
|
usage_limit INT,
|
|
used_count INT DEFAULT 0,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE order_promotions (
|
|
order_promo_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
order_id INT NOT NULL,
|
|
promotion_id INT NOT NULL,
|
|
discount_amount DECIMAL(10,2) NOT NULL,
|
|
applied_date DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
|
|
FOREIGN KEY (promotion_id) REFERENCES promotions(promotion_id) ON DELETE RESTRICT,
|
|
UNIQUE KEY unique_order_promotion (order_id, promotion_id)
|
|
);
|
|
|
|
CREATE TABLE product_reviews (
|
|
review_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
product_id INT NOT NULL,
|
|
user_id INT NOT NULL,
|
|
order_id INT,
|
|
rating TINYINT NOT NULL CHECK (rating >= 1 AND rating <= 5),
|
|
review_title VARCHAR(200),
|
|
review_content TEXT,
|
|
helpful_count INT DEFAULT 0,
|
|
is_verified_purchase BOOLEAN DEFAULT FALSE,
|
|
review_date DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
|
|
FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE SET NULL,
|
|
UNIQUE KEY unique_user_product_review (user_id, product_id, order_id)
|
|
);
|
|
|
|
CREATE TABLE suppliers (
|
|
supplier_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
supplier_name VARCHAR(200) NOT NULL,
|
|
vendor_name VARCHAR(200),
|
|
contact_person VARCHAR(100),
|
|
contact_phone VARCHAR(20),
|
|
contact_email VARCHAR(100),
|
|
supplier_address TEXT,
|
|
payment_terms VARCHAR(100),
|
|
credit_limit DECIMAL(12,2),
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE product_suppliers (
|
|
product_supplier_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
product_id INT NOT NULL,
|
|
supplier_id INT NOT NULL,
|
|
supplier_price DECIMAL(10,2) NOT NULL,
|
|
lead_time_days INT DEFAULT 7,
|
|
is_primary_supplier BOOLEAN DEFAULT FALSE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
|
|
FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id) ON DELETE CASCADE,
|
|
UNIQUE KEY unique_product_supplier (product_id, supplier_id)
|
|
);
|
|
|
|
CREATE INDEX idx_products_name ON products(product_name);
|
|
CREATE INDEX idx_products_brand ON products(brand);
|
|
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);
|
|
CREATE INDEX idx_orders_status ON orders(order_status);
|
|
CREATE INDEX idx_inventory_product ON inventory(product_id);
|
|
CREATE INDEX idx_cart_user ON shopping_cart(user_id);
|
|
CREATE INDEX idx_reviews_product_rating ON product_reviews(product_id, rating);
|