e-shop schema mssql server

MySQL Server February 20, 2021 python

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.

 

python 
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
/* 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)
);

Related Posts

sql cte query with closure table
sql cte query with closure table
Asp core default connection for sql server
Asp core default connection for sql server
blog sql schema
blog sql schema
events sql schema
events sql schema
Appointments sql schema
Appointments sql schema
Microsoft sql table events
Microsoft sql table events