-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy patheshop.sql
297 lines (255 loc) · 10.3 KB
/
eshop.sql
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
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
CREATE TABLE PressReleases (
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,
Description nvarchar(max) NOT NULL,
CONSTRAINT PK_PressReleases_Id PRIMARY KEY CLUSTERED (Id),
);
CREATE TABLE Events (
Id int IDENTITY(1,1) not null,
[Order] int not null default 0,
StartDate datetime not null DEFAULT GETDATE(),
EndDate datetime not null DEFAULT GETDATE(),
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,
Description nvarchar(max) NOT NULL,
CONSTRAINT PK_Events_Id PRIMARY KEY CLUSTERED (Id),
);
CREATE TABLE EventsMedia (
Id int IDENTITY(1,1) not null,
EventsId 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,
MediaPath nvarchar(max) NOT NULL,
CONSTRAINT PK_EventsMedia_Id PRIMARY KEY CLUSTERED (Id),
CONSTRAINT FK_EventssMedia_EventsId FOREIGN KEY (EventsId) REFERENCES Events(Id),
);
CREATE TABLE Banners (
Id int IDENTITY(1,1) not null,
[Order] int not null default 0,
Location int not null,
CreatedAt datetime not null DEFAULT GETDATE(),
UpdatedAt datetime not null DEFAULT GETDATE(),
Published bit not null default 0,
Deleted bit not null default 0,
BannerImagePath nvarchar(max) NULL,
BannerCode nvarchar(max) NULL,
CONSTRAINT PK_Banners_Id PRIMARY KEY CLUSTERED (Id),
);
CREATE TABLE BannersStatistics (
Id int IDENTITY(1,1) not null,
SessionId nvarchar(max) null,
BannersId int not null,
CreatedAt datetime not null DEFAULT GETDATE(),
CONSTRAINT PK_BannersStatistics_Id PRIMARY KEY CLUSTERED (Id),
CONSTRAINT FK_BannersStatistics_BannersId FOREIGN KEY (BannersId) REFERENCES Banners(Id),
);
CREATE TABLE Offers (
Id int IDENTITY(1,1) not null,
[Order] int not null default 0,
StartDate datetime not null DEFAULT GETDATE(),
EndDate datetime not null DEFAULT GETDATE(),
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,
Description nvarchar(max) NOT NULL,
CONSTRAINT PK_Offers_Id PRIMARY KEY CLUSTERED (Id),
);
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 FeatureAttributes (
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_FeatureAttributes_Id PRIMARY KEY CLUSTERED (Id),
CONSTRAINT FK_FeatureAttributes_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(Id),
CONSTRAINT FK_CategoryFeatures_FeatureId FOREIGN KEY (FeatureId) REFERENCES Features(Id),
);
CREATE TABLE Media (
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,
CONSTRAINT PK_Media_Id PRIMARY KEY CLUSTERED (Id),
MediaPath nvarchar(max) NOT NULL
);
CREATE TABLE Products (
Id int IDENTITY(1,1) not null,
ParentId int null,
BrandId int,
Hero nvarchar(max) 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,
Featured bit not null default 0,
CONSTRAINT PK_Products_Id PRIMARY KEY CLUSTERED (Id),
CONSTRAINT FK_Products_ParentId FOREIGN KEY (ParentId) REFERENCES Categories(Id),
CONSTRAINT FK_Products_BrandId FOREIGN KEY (BrandId) REFERENCES Brands(Id),
Title nvarchar(max) NOT NULL,
Price decimal(19,10) default 0
);
CREATE TABLE OffersDetail (
Id int IDENTITY(1,1) not null,
[Order] int not null default 0,
OffersId int not null,
ProductsId int not null,
Price decimal(19,10) 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_OffersDetail_Id PRIMARY KEY CLUSTERED (Id),
CONSTRAINT FK_OffersDetail_ProductsId FOREIGN KEY (ProductsId) REFERENCES Products(Id),
CONSTRAINT FK_OffersDetail_OffersId FOREIGN KEY (OffersId) REFERENCES Offers(Id),
);
CREATE TABLE ProductCategories (
ProductId int,
CategoryId int,
[Order] int not null default 0,
CreatedAt datetime not null DEFAULT GETDATE(),
UpdatedAt datetime not null DEFAULT GETDATE(),
CONSTRAINT PK_ProductCategories PRIMARY KEY NONCLUSTERED ([CategoryId],[ProductId]),
CONSTRAINT FK_ProductCategory_CategoryId FOREIGN KEY (CategoryId) REFERENCES Categories(Id),
CONSTRAINT FK_ProductCategory_ProductId FOREIGN KEY (ProductId) REFERENCES Products(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 ProductFeatureAttributes (
ProductId int,
FeatureAttributeId int,
[Order] int not null default 0,
CreatedAt datetime not null DEFAULT GETDATE(),
UpdatedAt datetime not null DEFAULT GETDATE(),
CONSTRAINT PK_ProductFeatureAttributes PRIMARY KEY NONCLUSTERED ([FeatureAttributeId],[ProductId]),
CONSTRAINT FK_ProductFeatureAttributes_FeatureAttributeId FOREIGN KEY (FeatureAttributeId) REFERENCES FeatureAttributes(Id),
CONSTRAINT FK_ProductFeatureAttributes_ProductId FOREIGN KEY (ProductId) REFERENCES Products(Id),
);
CREATE TABLE ProductMedia (
ProductId int,
MediaId int,
[Order] int not null default 0,
CreatedAt datetime not null DEFAULT GETDATE(),
UpdatedAt datetime not null DEFAULT GETDATE(),
CONSTRAINT PK_ProductMedia PRIMARY KEY NONCLUSTERED ([MediaId],[ProductId]),
CONSTRAINT FK_ProductMedia_MediaId FOREIGN KEY (MediaId) REFERENCES Media(Id),
CONSTRAINT FK_ProductMedia_ProductId FOREIGN KEY (ProductId) REFERENCES Products(Id),
);
CREATE TABLE ShoppingCartItems (
Id int IDENTITY(1,1) not null,
CONSTRAINT PK_ShoppingCartItems_Id PRIMARY KEY CLUSTERED (Id),
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 FK_ShoppingCartItems_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)
);