Untitled

CREATE DATABASE MyDatabase;
GO

USE MyDatabase;
GO

-- User table
CREATE TABLE [user] (
    userID BIGINT PRIMARY KEY,
    username NVARCHAR(255),
    profileName NVARCHAR(255),
    email NVARCHAR(255) UNIQUE,
    password NVARCHAR(255),
    urlImage NVARCHAR(255),
    phone NVARCHAR(20),
    lastname NVARCHAR(100),
    firstname NVARCHAR(100),
    address NVARCHAR(255),
    birthday DATE,
    gender NVARCHAR(10),
    role NVARCHAR(50),
    isConfirmed BIT,
    createdDate DATETIME,
    updatedDate DATETIME
);

-- Product table
CREATE TABLE Product (
    productID BIGINT PRIMARY KEY,
    productName NVARCHAR(255),
    description NVARCHAR(MAX),
    productType NVARCHAR(100),
    productReference NVARCHAR(100),
    price FLOAT,
    createDate DATETIME,
    updateDate DATETIME,
    salePrice FLOAT,
    quantity BIGINT
);

-- Order table
CREATE TABLE [order] (
    orderNumber NVARCHAR(50) PRIMARY KEY,
    createdDate DATETIME,
    status NVARCHAR(50),
    userID BIGINT,
    paymentMethodID BIGINT,
    totalAmount FLOAT,
    address NVARCHAR(255),
    FOREIGN KEY (userID) REFERENCES [user](userID),
    FOREIGN KEY (paymentMethodID) REFERENCES paymentMethod(paymentMethodID)
);

-- PaymentMethod table
CREATE TABLE paymentMethod (
    paymentMethodID BIGINT PRIMARY KEY,
    paymentName NVARCHAR(255),
    userID BIGINT,
    FOREIGN KEY (userID) REFERENCES [user](userID)
);

-- CommentList table
CREATE TABLE commentList (
    postID BIGINT,
    userID BIGINT,
    commentText NVARCHAR(MAX),
    createDate DATETIME,
    PRIMARY KEY (postID, userID),
    FOREIGN KEY (userID) REFERENCES [user](userID)
);

-- Followers table
CREATE TABLE Followers (
    userID BIGINT,
    followingUserID BIGINT,
    followDate DATETIME,
    PRIMARY KEY (userID, followingUserID),
    FOREIGN KEY (userID) REFERENCES [user](userID),
    FOREIGN KEY (followingUserID) REFERENCES [user](userID)
);

-- ProductImage table
CREATE TABLE ProductImage (
    imageID BIGINT PRIMARY KEY,
    productID BIGINT,
    imageUrl NVARCHAR(255),
    altText NVARCHAR(255),
    isFeatured BIT,
    FOREIGN KEY (productID) REFERENCES Product(productID)
);

-- Cart table
CREATE TABLE Cart (
    cartID BIGINT PRIMARY KEY,
    userID BIGINT,
    FOREIGN KEY (userID) REFERENCES [user](userID)
);

-- CartItem table
CREATE TABLE CartItem (
    cartItemID BIGINT PRIMARY KEY,
    cartID BIGINT,
    productID BIGINT,
    quantity INT,
    price FLOAT,
    FOREIGN KEY (cartID) REFERENCES Cart(cartID),
    FOREIGN KEY (productID) REFERENCES Product(productID)
);

-- ProductTags table
CREATE TABLE ProductTags (
    productTagID BIGINT PRIMARY KEY,
    productID BIGINT,
    tagName NVARCHAR(100),
    FOREIGN KEY (productID) REFERENCES Product(productID)
);

-- LikeList table
CREATE TABLE LikeList (
    userID BIGINT,
    postID BIGINT,
    PRIMARY KEY (userID, postID),
    FOREIGN KEY (userID) REFERENCES [user](userID)
);

-- Role table
CREATE TABLE Role (
    roleID BIGINT PRIMARY KEY,
    roleName NVARCHAR(100)
);

-- Admin table
CREATE TABLE Admin (
    adminID BIGINT PRIMARY KEY,
    fullName NVARCHAR(255),
    email NVARCHAR(255),
    phone NVARCHAR(20)
);

-- Collection table
CREATE TABLE Collection (
    collectionID BIGINT PRIMARY KEY,
    title NVARCHAR(255),
    description NVARCHAR(MAX),
    createDate DATETIME,
    updateDate DATETIME
);

-- ProductCollections table
CREATE TABLE ProductCollections (
    productID BIGINT,
    collectionID BIGINT,
    PRIMARY KEY (productID, collectionID),
    FOREIGN KEY (productID) REFERENCES Product(productID),
    FOREIGN KEY (collectionID) REFERENCES Collection(collectionID)
);