e-shop schema mssql server

This is a SQL script that creates the schema for an e-shop database. Let's break down the different tables and their relationships:

1. Categories: This table stores information about different product categories. It has a foreign key ParentId that references the Id column of the same table, allowing hierarchical categorization.

2. Brands: This table stores information about different product brands.

3. Tags: This table stores tags that can be associated with products.

4. Features: This table stores various features that products may have.

5. Attributes: This table stores attributes associated with product features. It has a foreign key FeatureId that references the Id column of the Features table.

6. CategoryFeatures: This table establishes a many-to-many relationship between categories and features. It associates features with the categories they belong to. It has foreign keys CategoryId and FeatureId that reference the Id columns of the Categories and Features tables, respectively.

7. Products: This table stores information about individual products. It has foreign keys BrandId and CategoryId that reference the Id column of the Brands and Categories tables, respectively.

8. ProductTags: This table establishes a many-to-many relationship between products and tags. It associates tags with the products they are related to. It has foreign keys ProductId and TagId that reference the Id columns of the Products and Tags tables, respectively.

9. ProductAttributes: This table establishes a many-to-many relationship between products and attributes. It associates attributes with the products they are related to. It has foreign keys ProductId and AttributeId that reference the Id columns of the Products and Attributes tables, respectively.

10. ProductMedia: This table stores media files associated with products. It has a foreign key ProductId that references the Id column of the Products table.

11. ShoppingCartItem: This table stores items added to the shopping cart. It has a foreign key ProductId that references the Id column of the Products table.

12. Orders: This table stores order information, such as customer details and total order amount.

13. OrdersDetail: This table stores details of individual products within an order. It has foreign keys OrdersId and ProductId that reference the Id column of the Orders and Products tables, respectively.

14. OrdersStatus: This table stores the status of orders. It has a foreign key OrdersId that references the Id column of the Orders table.

The script is well-structured and sets up relationships between various entities in the e-shop database. It allows you to create, manage, and query the database efficiently for an e-commerce application.

              
                /* e-shop  schema */
CREATE TABLE Categories (
    Id int IDENTITY(1,1) not null,
    Hero nvarchar(max) null,
    ParentId int null,
    [Order] int not null default 0,
    CreatedAt datetime not null DEFAULT GETDATE(),
    UpdatedAt datetime not null DEFAULT GETDATE(),
    Published bit not null default 0,
    Deleted bit not null default 0,
    Title nvarchar(max) NOT NULL,
    CONSTRAINT PK_Categories_Id PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_Categories_ParentId FOREIGN KEY (ParentId) REFERENCES  Categories(Id),
);

CREATE TABLE Brands (
    Id int IDENTITY(1,1) not null,
    [Order] int not null default 0,
    CreatedAt datetime not null DEFAULT GETDATE(),
    UpdatedAt datetime not null DEFAULT GETDATE(),
    Published bit not null default 0,
    Deleted bit not null default 0,
    Title nvarchar(max) NOT NULL,
    CONSTRAINT PK_Brands_Id PRIMARY KEY CLUSTERED (Id),
);

CREATE TABLE Tags (
    Id int IDENTITY(1,1) not null,
    [Order] int not null default 0,
    CreatedAt datetime not null DEFAULT GETDATE(),
    UpdatedAt datetime not null DEFAULT GETDATE(),
    Published bit not null default 0,
    Deleted bit not null default 0,
    Title nvarchar(max) NOT NULL,
    CONSTRAINT PK_Tags_Id PRIMARY KEY CLUSTERED (Id)
);

CREATE TABLE Features (
    Id int IDENTITY(1,1) not null,
    [Order] int not null default 0,
    CreatedAt datetime not null DEFAULT GETDATE(),
    UpdatedAt datetime not null DEFAULT GETDATE(),
    Published bit not null default 0,
    Deleted bit not null default 0,
    Title nvarchar(max) NOT NULL,
    CONSTRAINT PK_Features_Id PRIMARY KEY CLUSTERED (Id)
);

CREATE TABLE Attributes (
    Id int IDENTITY(1,1) not null,
    FeatureId int not null,
    [Order] int not null default 0,
    CreatedAt datetime not null DEFAULT GETDATE(),
    UpdatedAt datetime not null DEFAULT GETDATE(),
    Published bit not null default 0,
    Deleted bit not null default 0,
    Title nvarchar(max) NOT NULL,
    CONSTRAINT PK_Attributes_Id PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_Attributes_FeatureId FOREIGN KEY (FeatureId) REFERENCES  Features(Id),
);

CREATE TABLE CategoryFeatures (
    FeatureId int,
    CategoryId int,
    [Order] int not null  default 0,
    CreatedAt datetime not null DEFAULT GETDATE(),
    UpdatedAt datetime not null DEFAULT GETDATE(),
    CONSTRAINT PK_CategoryFeatures PRIMARY KEY NONCLUSTERED ([CategoryId],[FeatureId]),
    CONSTRAINT FK_CategoryFeatures_CategoryId FOREIGN KEY (CategoryId) REFERENCES  Categories(CategoryId),
    CONSTRAINT FK_CategoryFeatures_FeatureId FOREIGN KEY (FeatureId) REFERENCES  Features(FeatureId),
);


CREATE TABLE Products (
    Id int IDENTITY(1,1) not null,
    ParentId int null,
    BrandId int,
    CategoryId int,
    Hero nvarchar(max) null,
    Title nvarchar(max) NOT NULL,
    Description nvarchar(max) NOT NULL,
    Price decimal(19,10) default 0
    [Order] int not null default 0,
    CreatedAt datetime not null DEFAULT GETDATE(),
    UpdatedAt datetime not null DEFAULT GETDATE(),
    Published bit not null default 0,
    Deleted bit not null default 0,
    CONSTRAINT PK_Products_Id PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_Products_ParentId FOREIGN KEY (ParentId) REFERENCES  Products(Id),
    CONSTRAINT FK_Products_BrandId FOREIGN KEY (BrandId) REFERENCES  Brands(Id),
    CONSTRAINT FK_Products_CategoryId FOREIGN KEY (CategoryId) REFERENCES  Categories(Id),
);


CREATE TABLE ProductTags (
    ProductId int,
    TagId int,
    [Order] int not null default 0,
    CreatedAt datetime not null DEFAULT GETDATE(),
    UpdatedAt datetime not null DEFAULT GETDATE(),
    CONSTRAINT PK_ProductTags PRIMARY KEY NONCLUSTERED ([TagId],[ProductId]),
    CONSTRAINT FK_ProductTags_TagId FOREIGN KEY (TagId) REFERENCES  Tags(Id),
    CONSTRAINT FK_ProductTags_ProductId FOREIGN KEY (ProductId) REFERENCES  Products(Id),
);

CREATE TABLE ProductAttributes (
    ProductId int,
    AttributeId int,
    [Order] int not null default 0,
    CreatedAt datetime not null DEFAULT GETDATE(),
    UpdatedAt datetime not null DEFAULT GETDATE(),
    CONSTRAINT PK_ProductAttributes PRIMARY KEY NONCLUSTERED ([AttributeId],[ProductId]),
    CONSTRAINT FK_ProductAttributes_FeatureAttributeId FOREIGN KEY (AttributeId) REFERENCES  Attributes(Id),
    CONSTRAINT FK_ProductAttributes_ProductId FOREIGN KEY (ProductId) REFERENCES  Products(Id),
);


CREATE TABLE ProductMedia (
    Id int IDENTITY(1,1) not null,
    ProductId int not null,
    FilePath nvarchar(max) NOT NULL,
    [Order] int not null default 0,
    CreatedAt datetime not null DEFAULT GETDATE(),
    UpdatedAt datetime not null DEFAULT GETDATE(),
    Published bit not null default 0,
    Deleted bit not null default 0,
    CONSTRAINT PK_ProductMedia_Id PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_ProductMedia_ProductId FOREIGN KEY (ProductId) REFERENCES  Products(Id)
);


CREATE TABLE ShoppingCartItem (
   Id int IDENTITY(1,1) not null,
   ProductId int not null,
   Quantity int not null,
   SessionCartId nvarchar(Max) not null,
   CreatedAt datetime not null DEFAULT GETDATE(),
   UpdatedAt datetime not null DEFAULT GETDATE(),
   CONSTRAINT PK_ShoppingCartItem_Id PRIMARY KEY CLUSTERED (Id),
   CONSTRAINT FK_ShoppingCartItem_ProductId FOREIGN KEY (ProductId) REFERENCES  Products(Id)
);

CREATE TABLE Orders (
    Id int IDENTITY(1,1) not null,
    FirstName nvarchar(255) NULL,
    LastName nvarchar(255) NULL,
    AddressLine1 nvarchar(255) NULL,
    AddressLine2 nvarchar(255) NULL,
    ZipCode nvarchar(255) NULL,
    City nvarchar(255) NULL,
    Email nvarchar(255) NULL,
    Country nvarchar(255) NULL,
    State nvarchar(255) NULL,
    PhoneNumber nvarchar(255) NULL,
    OrderTotal decimal(19,10) default 0,
    OrderPlaced datetime DEFAULT GETDATE(),
    CreatedAt datetime not null DEFAULT GETDATE(),
    UpdatedAt datetime not null DEFAULT GETDATE(),
    CONSTRAINT PK_Orders_Id PRIMARY KEY CLUSTERED (Id)
);

CREATE TABLE OrdersDetail (
    Id int IDENTITY(1,1) not null,
    OrdersId int not null,
    ProductId int not null,
    Quantity int not null,
    Price decimal(19,10) not null,
    CreatedAt datetime not null DEFAULT GETDATE(),
    UpdatedAt datetime not null DEFAULT GETDATE(),
    CONSTRAINT PK_Id PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_OrdersDetail_ProductId FOREIGN KEY (ProductId) REFERENCES  Products(Id),
    CONSTRAINT FK_OrdersDetail_OrdersId FOREIGN KEY (OrdersId) REFERENCES  Orders(Id)
);

CREATE TABLE OrdersStatus (
    Id int IDENTITY(1,1) not null,
    OrdersId int not null,
    status int not null,
    CreatedAt datetime not null DEFAULT GETDATE(),
    UpdatedAt datetime not null DEFAULT GETDATE(),
    CONSTRAINT PK_Id PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_OrdersStatus_OrdersId FOREIGN KEY (OrdersId) REFERENCES  Orders(Id)
);