USE storedb;
CREATE TABLE Categories (
categoryID int PRIMARY KEY AUTO_INCREMENT,
categoryName varchar(255) not null,
description text
);
CREATE TABLE Suppliers (
supplierID int PRIMARY KEY AUTO_INCREMENT,
supplierName varchar(255) not null,
phone varchar(20) not null,
address text not null
);
CREATE TABLE Products (
productID int PRIMARY KEY AUTO_INCREMENT,
categoryID int,
supplierID int,
productName varchar(255) not null,
price DECIMAL(20, 2) not null,
description text,
FOREIGN KEY (categoryID) REFERENCES Categories(categoryID),
FOREIGN KEY (supplierID) REFERENCES Suppliers(supplierID)
);
CREATE TABLE ImageProduct (
imageID int PRIMARY KEY AUTO_INCREMENT,
productID int,
imageUrl text,
FOREIGN KEY (productID) REFERENCES Products(productID)
);
CREATE TABLE Customers (
customerID int PRIMARY KEY AUTO_INCREMENT,
name varchar(150) NOT NULL,
phone varchar(10) NOT NULL,
email varchar(150) NOT NULL,
birthday datetime NOT NULL
);
CREATE TABLE Accounts (
accountID int PRIMARY KEY AUTO_INCREMENT,
customerID int unique,
username VARCHAR(255) NOT NULL,
hashed_password VARCHAR(255) NOT NULL,
salt VARCHAR(255) NOT NULL,
FOREIGN KEY (customerID) REFERENCES Customers(customerID)
);
CREATE TABLE InfoDelivery (
infoDeliveryID int PRIMARY KEY AUTO_INCREMENT,
accountID int,
name varchar(50) not null,
phone varchar(10) not null,
city varchar(100) not null,
district varchar(100) not null,
ward varchar(100) not null,
specificAddress varchar(100) not null,
description text,
dateCreate datetime not null,
FOREIGN KEY (accountID) REFERENCES Accounts(accountID)
);
CREATE TABLE Orders (
orderID int PRIMARY KEY AUTO_INCREMENT,
customerID int,
dateOrder datetime not null,
dateShipping datetime,
infoDeliveryID int,
FOREIGN KEY (customerID) REFERENCES Customers(customerID),
FOREIGN KEY (infoDeliveryID) REFERENCES InfoDelivery(infoDeliveryID)
);
CREATE TABLE OrderDetails (
orderID int PRIMARY KEY,
productID int PRIMARY KEY,
price DECIMAL(20, 2) not null,
quantity int NOT NULL,
FOREIGN KEY (orderID) REFERENCES Orders(orderID),
FOREIGN KEY (productID) REFERENCES Products(productID)
);
CREATE TABLE CustomerCarts (
cartID int PRIMARY KEY AUTO_INCREMENT,
accountID int unique,
FOREIGN KEY (accountID) REFERENCES Accounts(accountID)
);
CREATE TABLE CartDetails (
cartID int,
productID int,
quantity int not null,
FOREIGN KEY (cartID) REFERENCES CustomerCarts(cartID),
FOREIGN KEY (productID) REFERENCES Products(productID)
);
