Scribd
Upload
29 views2 pages

New Text Document - CSV

The document outlines the SQL schema for an e-commerce database, including tables for Users, Categories, Products, Orders, OrderDetails, Payments, Reviews, Chats, Expenses, and Advertisements. Each table is defined with its respective fields, data types, and relationships, including primary and foreign keys. The schema supports functionalities such as user management, product categorization, order processing, payment handling, and user interactions.

Uploaded by

oitroioi23
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
29 views2 pages

New Text Document - CSV

The document outlines the SQL schema for an e-commerce database, including tables for Users, Categories, Products, Orders, OrderDetails, Payments, Reviews, Chats, Expenses, and Advertisements. Each table is defined with its respective fields, data types, and relationships, including primary and foreign keys. The schema supports functionalities such as user management, product categorization, order processing, payment handling, and user interactions.

Uploaded by

oitroioi23
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

CREATE TABLE Users (

user_id INT PRIMARY KEY IDENTITY(1,1),


name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
role VARCHAR(50) NOT NULL,
phone VARCHAR(20),
address TEXT
);

CREATE TABLE Categories (


category_id INT PRIMARY KEY IDENTITY(1,1),
name VARCHAR(255) NOT NULL,
description TEXT
);

CREATE TABLE Products (


product_id INT PRIMARY KEY IDENTITY(1,1),
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
stock_quantity INT NOT NULL,
category_id INT FOREIGN KEY REFERENCES Categories(category_id) ON DELETE
CASCADE
);

CREATE TABLE Orders (


order_id INT PRIMARY KEY IDENTITY(1,1),
user_id INT FOREIGN KEY REFERENCES Users(user_id) ON DELETE CASCADE,
order_date DATETIME DEFAULT GETDATE(),
total_price DECIMAL(10,2) NOT NULL,
status VARCHAR(50) NOT NULL
);

CREATE TABLE OrderDetails (


order_detail_id INT PRIMARY KEY IDENTITY(1,1),
order_id INT FOREIGN KEY REFERENCES Orders(order_id) ON DELETE CASCADE,
product_id INT FOREIGN KEY REFERENCES Products(product_id) ON DELETE CASCADE,
quantity INT NOT NULL,
price DECIMAL(10,2) NOT NULL
);

CREATE TABLE Payments (


payment_id INT PRIMARY KEY IDENTITY(1,1),
order_id INT FOREIGN KEY REFERENCES Orders(order_id) ON DELETE CASCADE,
payment_method VARCHAR(100) NOT NULL,
payment_status VARCHAR(50) NOT NULL,
transaction_date DATETIME DEFAULT GETDATE()
);

CREATE TABLE Reviews (


review_id INT PRIMARY KEY IDENTITY(1,1),
user_id INT FOREIGN KEY REFERENCES Users(user_id) ON DELETE CASCADE,
product_id INT FOREIGN KEY REFERENCES Products(product_id) ON DELETE CASCADE,
rating INT CHECK (rating BETWEEN 1 AND 5),
comment TEXT,
created_at DATETIME DEFAULT GETDATE()
);
CREATE TABLE Chats (
chat_id INT PRIMARY KEY IDENTITY(1,1),
sender_id INT FOREIGN KEY REFERENCES Users(user_id) ON DELETE CASCADE,
receiver_id INT FOREIGN KEY REFERENCES Users(user_id) ON DELETE CASCADE,
message TEXT NOT NULL,
sent_at DATETIME DEFAULT GETDATE()
);

CREATE TABLE Expenses (


expense_id INT PRIMARY KEY IDENTITY(1,1),
name VARCHAR(255) NOT NULL,
amount DECIMAL(10,2) NOT NULL,
category VARCHAR(255),
created_at DATETIME DEFAULT GETDATE()
);

CREATE TABLE Advertisements (


ad_id INT PRIMARY KEY IDENTITY(1,1),
title VARCHAR(255) NOT NULL,
description TEXT,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
budget DECIMAL(10,2) NOT NULL,
product_id INT FOREIGN KEY REFERENCES Products(product_id) ON DELETE CASCADE,
category_id INT FOREIGN KEY REFERENCES Categories(category_id) ON DELETE
CASCADE,
created_by INT FOREIGN KEY REFERENCES Users(user_id) ON DELETE SET NULL
);

You might also like