Simple-ecommerce-db.md

-- Create Categories table
CREATE TABLE Categories (
    Id INT AUTO_INCREMENT PRIMARY KEY,  -- Unique identifier for each category
    Slug VARCHAR(255) NOT NULL UNIQUE,  -- Slug for URL and uniqueness
    ParentCategoryId INT,  -- Optional self-referencing foreign key for hierarchical categories
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- Timestamp when the category is created
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  -- Automatically updates when record is modified
    FOREIGN KEY (ParentCategoryId) REFERENCES Categories(Id)  -- Self-referencing foreign key for nested categories
);

-- Create CategoryTranslations table for multi-language support
CREATE TABLE CategoryTranslations (
    CategoryId INT,  -- Foreign key to Categories table
    LanguageCode VARCHAR(10),  -- Language code for the translation (e.g., 'en', 'fr')
    Name VARCHAR(255),  -- Translated category name
    Description TEXT,  -- Translated category description
    PRIMARY KEY (CategoryId, LanguageCode),  -- Primary key on CategoryId and LanguageCode
    FOREIGN KEY (CategoryId) REFERENCES Categories(Id)  -- Foreign key referencing Categories table
);

-- Create Brands table
CREATE TABLE Brands (
    Id INT AUTO_INCREMENT PRIMARY KEY,  -- Unique identifier for each brand
    Slug VARCHAR(255) NOT NULL UNIQUE,  -- Slug for URL and uniqueness
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- Timestamp when the brand is created
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  -- Automatically updates when record is modified
);

-- Create BrandTranslations table for multi-language support
CREATE TABLE BrandTranslations (
    BrandId INT,  -- Foreign key to Brands table
    LanguageCode VARCHAR(10),  -- Language code for the translation (e.g., 'en', 'fr')
    Name VARCHAR(255),  -- Translated brand name
    PRIMARY KEY (BrandId, LanguageCode),  -- Primary key on BrandId and LanguageCode
    FOREIGN KEY (BrandId) REFERENCES Brands(Id)  -- Foreign key referencing Brands table
);

-- Create Products table
CREATE TABLE Products (
    Id INT AUTO_INCREMENT PRIMARY KEY,  -- Unique identifier for each product
    CategoryId INT,  -- Foreign key to Categories table
    BrandId INT,  -- Foreign key to Brands table
    Slug VARCHAR(255) NOT NULL UNIQUE,  -- Slug for URL and uniqueness
    Price DECIMAL(10, 2),  -- Price of the product
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- Timestamp when the product is created
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  -- Automatically updates when record is modified
    FOREIGN KEY (CategoryId) REFERENCES Categories(Id),  -- Foreign key referencing Categories table
    FOREIGN KEY (BrandId) REFERENCES Brands(Id)  -- Foreign key referencing Brands table
);

-- Create ProductTranslations table for multi-language support
CREATE TABLE ProductTranslations (
    ProductId INT,  -- Foreign key to Products table
    LanguageCode VARCHAR(10),  -- Language code for the translation (e.g., 'en', 'fr')
    Name VARCHAR(255),  -- Translated product name
    Description TEXT,  -- Translated product description
    PRIMARY KEY (ProductId, LanguageCode),  -- Primary key on ProductId and LanguageCode
    FOREIGN KEY (ProductId) REFERENCES Products(Id)  -- Foreign key referencing Products table
);

-- Create Attributes table for product attributes (e.g., size, color)
CREATE TABLE Attributes (
    Id INT AUTO_INCREMENT PRIMARY KEY,  -- Unique identifier for each attribute
    ProductId INT,  -- Foreign key to Products table
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- Timestamp when the attribute is created
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  -- Automatically updates when record is modified
    FOREIGN KEY (ProductId) REFERENCES Products(Id)  -- Foreign key referencing Products table
);

-- Create AttributeTranslations table for multi-language support for attribute keys and values
CREATE TABLE AttributeTranslations (
    AttributeId INT,  -- Foreign key to Attributes table
    LanguageCode VARCHAR(10),  -- Language code for the translation (e.g., 'en', 'fr')
    TheKey VARCHAR(255),  -- Translated attribute key (e.g., "color" -> "couleur")
    TheValue VARCHAR(255),  -- Translated attribute value (e.g., "red" -> "rouge")
    PRIMARY KEY (AttributeId, LanguageCode),  -- Primary key on AttributeId and LanguageCode
    FOREIGN KEY (AttributeId) REFERENCES Attributes(Id)  -- Foreign key referencing Attributes table
);

-- Create Variants table for product variants (e.g., different sizes or colors of a product)
CREATE TABLE Variants (
    Id INT AUTO_INCREMENT PRIMARY KEY,  -- Unique identifier for each variant
    ProductId INT,  -- Foreign key to Products table
    Price DECIMAL(10, 2),  -- Price for the specific variant
    StockQuantity INT,  -- Available stock quantity for the variant
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- Timestamp when the variant is created
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  -- Automatically updates when record is modified
    FOREIGN KEY (ProductId) REFERENCES Products(Id)  -- Foreign key referencing Products table
);

-- Create VariantAttributes table to link variants to specific attributes
CREATE TABLE VariantAttributes (
    VariantId INT,  -- Foreign key to Variants table
    AttributeId INT,  -- Foreign key to Attributes table
    PRIMARY KEY (VariantId, AttributeId),  -- Primary key on VariantId and AttributeId
    FOREIGN KEY (VariantId) REFERENCES Variants(Id),  -- Foreign key referencing Variants table
    FOREIGN KEY (AttributeId) REFERENCES Attributes(Id)  -- Foreign key referencing Attributes table
);

-- Create VariantTranslations table for multi-language support for variant names
CREATE TABLE VariantTranslations (
    VariantId INT,  -- Foreign key to Variants table
    LanguageCode VARCHAR(10),  -- Language code for the translation (e.g., 'en', 'fr')
    Name VARCHAR(255),  -- Translated variant name (e.g., "Red" -> "Rouge")
    PRIMARY KEY (VariantId, LanguageCode),  -- Primary key on VariantId and LanguageCode
    FOREIGN KEY (VariantId) REFERENCES Variants(Id)  -- Foreign key referencing Variants table
);

-- Create Images table to store product and variant images
CREATE TABLE Images (
    Id INT AUTO_INCREMENT PRIMARY KEY,  -- Unique identifier for each image
    ProductId INT,  -- Foreign key to Products table
    VariantId INT,  -- Optional foreign key to Variants table (nullable if image is for a product only)
    ImageUrl VARCHAR(255),  -- URL for the image
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- Timestamp when the image is uploaded
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  -- Automatically updates when record is modified
    FOREIGN KEY (ProductId) REFERENCES Products(Id),  -- Foreign key referencing Products table
    FOREIGN KEY (VariantId) REFERENCES Variants(Id)  -- Foreign key referencing Variants table
);

-- Create Orders table for storing customer orders
CREATE TABLE Orders (
    Id INT AUTO_INCREMENT PRIMARY KEY,  -- Unique identifier for each order
    CustomerId INT,  -- Foreign key to Customer table (not provided here)
    Total DECIMAL(10, 2),  -- Total price for the order
    Status VARCHAR(50),  -- Order status (e.g., 'Pending', 'Shipped')
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- Timestamp when the order is created
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  -- Automatically updates when record is modified
);

-- Create OrderItems table for storing products and variants in each order
CREATE TABLE OrderItems (
    Id INT AUTO_INCREMENT PRIMARY KEY,  -- Unique identifier for each order item
    OrderId INT,  -- Foreign key to Orders table
    ProductId INT,  -- Foreign key to Products table
    VariantId INT,  -- Optional foreign key to Variants table
    Quantity INT,  -- Quantity of the product/variant ordered
    Price DECIMAL(10, 2),  -- Price of the product/variant at the time of order
    FOREIGN KEY (OrderId) REFERENCES Orders(Id),  -- Foreign key referencing Orders table
    FOREIGN KEY (ProductId) REFERENCES Products(Id),  -- Foreign key referencing Products table
    FOREIGN KEY (VariantId) REFERENCES Variants(Id)  -- Foreign key referencing Variants table
);