Untitled

-- Define tables
CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(255) NOT NULL,
    Password VARCHAR(255) NOT NULL,
    Email VARCHAR(255) NOT NULL,
    -- Add more user-related fields as needed
);

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Description TEXT,
    Price DECIMAL(10, 2) NOT NULL,
    StockQuantity INT NOT NULL,
    CategoryID INT, -- Foreign key to Categories table
    SellerID INT,   -- Foreign key to Users table
);

CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Description TEXT
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    UserID INT, -- Foreign key to Users table
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2) NOT NULL
);

CREATE TABLE OrderItems (
    OrderItemID INT PRIMARY KEY,
    OrderID INT,      -- Foreign key to Orders table
    ProductID INT,    -- Foreign key to Products table
    Quantity INT NOT NULL,
    Price DECIMAL(10, 2) NOT NULL
);

-- Define relationships
ALTER TABLE Products
ADD FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID);

ALTER TABLE Products
ADD FOREIGN KEY (SellerID) REFERENCES Users(UserID);

ALTER TABLE Orders
ADD FOREIGN KEY (UserID) REFERENCES Users(UserID);

ALTER TABLE OrderItems
ADD FOREIGN KEY (OrderID) REFERENCES Orders(OrderID);

ALTER TABLE OrderItems
ADD FOREIGN KEY (ProductID) REFERENCES Products(ProductID);