Lectures List
capscom technology computer course almora

E commerce website database schema

-- users
CREATE TABLE IF NOT EXISTS users(
id INT(10) AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(120) NOT NULL,
email VARCHAR(120) NOT NULL,
password VARCHAR(120) NOT NULL,
user_type ENUM("user","admin") DEFAULT "user",
profile VARCHAR(255) DEFAULT 'profile.jpg'
);


-- user_details
CREATE TABLE IF NOT EXISTS user_details(
id INT(10) AUTO_INCREMENT PRIMARY KEY,
user_id INT(10) NOT NULL,
country VARCHAR(120) DEFAULT NULL,
state VARCHAR(120) DEFAULT NULL,
district VARCHAR(120) DEFAULT NULL,
pincode VARCHAR(120) DEFAULT NULL,
mobile VARCHAR(15) DEFAULT NULL,
local_address VARCHAR(255) DEFAULT NULL,
permanent_address VARCHAR(255) DEFAULT NULL,
FOREIGN KEY(user_id) REFERENCES users(id)
);

-- product_categorys
CREATE TABLE IF NOT EXISTS product_categorys(
id INT(10) AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
image VARCHAR(255) NOT NULL,
status ENUM("not_active","active") DEFAULT "not_active"
);


-- products
CREATE TABLE IF NOT EXISTS products(
id INT(10) AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DOUBLE(8,2) NOT NULL,
sale_price DOUBLE(8,2) NOT NULL,
qty DOUBLE(8,2) NOT NULL,
details TEXT DEFAULT NULL,
image VARCHAR(120) NOT NULL
);

-- product_ratings
CREATE TABLE IF NOT EXISTS product_ratings(
id INT(10) AUTO_INCREMENT PRIMARY KEY,
user_id INT(10) NOT NULL,
product_id INT(10) NOT NULL,
rate ENUM(1,2,3,4,5) DEFAULT 1,
FOREIGN KEY(user_id) REFERENCES users(id),
FOREIGN KEY(product_id) REFERENCES products(id)
);

-- product_reviews
CREATE TABLE IF NOT EXISTS product_reviews(
id INT(10) AUTO_INCREMENT PRIMARY KEY,
user_id INT(10) NOT NULL,
product_id INT(10) NOT NULL,
review VARCHAR(255) NOT NULL,
FOREIGN KEY(user_id) REFERENCES users(id),
FOREIGN KEY(product_id) REFERENCES products(id)
);


-- product_carts
CREATE TABLE IF NOT EXISTS product_carts(
id INT(10) AUTO_INCREMENT PRIMARY KEY,
product_id INT(10) NOT NULL,
user_id INT(10) NOT NULL,
qty DOUBLE(8,2) NOT NULL
FOREIGN KEY(product_id) REFERENCES products(id),
FOREIGN KEY(user_id) REFERENCES users(id)
);

-- payment_request_details
CREATE TABLE IF NOT EXISTS payment_request_details(
id INT(10) AUTO_INCREMENT PRIMARY KEY,
phone VARCHAR(15) NOT NULL,
buyer_name VARCHAR(120) NOT NULL,
amount DOUBLE(8,2) NOT NULL,
purpose VARCHAR(120) NOT NULL,
payment_request_status VARCHAR(120) NOT NULL,
payment_id VARCHAR(120) DEFAULT NULL,
payment_status VARCHAR(120) DEFAULT NULL
);

-- order_payments
CREATE TABLE IF NOT EXISTS order_payments(
id INT(10) AUTO_INCREMENT PRIMARY KEY,
payment_request_detail_id INT(10) NOT NULL,
FOREIGN KEY(payment_request_detail_id) REFERENCES payment_request_details(id),
);

-- product_orders
CREATE TABLE IF NOT EXISTS product_orders(
id INT(10) AUTO_INCREMENT PRIMARY KEY,
payment_id VARCHAR(255) NOT NULL,
FOREIGN KEY(payment_id) REFERENCES order_payments(id)
);

-- product_order_details
CREATE TABLE IF NOT EXISTS product_order_details(
id INT(10) AUTO_INCREMENT PRIMARY KEY,
order_id INT(10) NOT NULL,
product_id INT(10) NOT NULL,
user_id INT(10) NOT NULL,
qty DOUBLE(8,2) NOT NULL,
FOREIGN KEY(order_id) REFERENCES product_orders(id)
);


Author/Written by: Mr Hemant Singh (Founder)
Terms & Conditions CAPSCOM TECHNOLOGY Pvt. Ltd. का बेहतर रूप से संचालन करने के लिए यहॉ कुछ नियमें और शर्ते लागू है अगर आप कैप्सकॉम टैक्नोलॉजी में पढ़ रहे है तो यह आप पर लागू होता है।