MySQL Server

MySQL Server

Explore mysql server code snippets and tutorials

MySQL Server

e-shop schema mssql server

<p>This is a SQL script that creates the schema for an e-shop database. Let&#39;s break down the different tables and their relationships:</p> <ol> <li> <p><code>Categories</code>: This table stores information about …

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)
);
Feb 20, 2021 python
MySQL Server

Microsoft sql banners table

Microsoft SQL banners table

python 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
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_Banners_Id PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_BannersStatistics_BannersId FOREIGN KEY (BannersId) REFERENCES  Banners(Id),
);
Oct 24, 2020 python
MySQL Server

Microsoft Sql table press release

Microsoft SQL table press release create

python 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
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),
);
Oct 24, 2020 python
MySQL Server

Microsoft sql table events

Microsft SQL table events create

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
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),
);
Oct 24, 2020 python