Dive into Code

Discover code snippets, tutorials, and programming insights

MySQL Server

Appointments sql schema

<p>The SQL script provided creates four tables: <code>AppointmentCategories</code>, <code>Tags</code>, <code>Appointments</code>, and <code>AppointmentFiles</code>. These tables are designed to manage appointments and their related information. Below is a brief explanation of each …

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
/* Appointment  schema */

CREATE TABLE AppointmentCategories (
    Id int IDENTITY(1,1) not null,
    Title 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_AppointmentCategories_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 Appointments (
    Id int IDENTITY(1,1) not null,
    AppointmentCategoryId int not null,
    Title nvarchar(max) NOT NULL,
    Description nvarchar(max) NOT NULL,
    StartDate datetime not null DEFAULT GETDATE(),
    [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_Appointments_Id PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_Appointments_AppointmentCategoryId FOREIGN KEY (AppointmentCategoryId) REFERENCES  AppointmentCategories(Id)
);

CREATE TABLE AppointmentTags (
    AppointmentId int,
    TagId int,
    [Order] int not null  default 0,
    CreatedAt datetime not null DEFAULT GETDATE(),
    UpdatedAt datetime not null DEFAULT GETDATE(),
    CONSTRAINT PK_AppointmentTags PRIMARY KEY NONCLUSTERED ([ApppointmentId],[TagId]),
    CONSTRAINT FK_AppointmentTags_AppointmentId FOREIGN KEY (AppointmentId) REFERENCES  Appointments(Id),
    CONSTRAINT FK_AppointmentTags_TagId FOREIGN KEY (TagId) REFERENCES Tags(Id),
);


CREATE TABLE AppointmentFiles (
    Id int IDENTITY(1,1) not null,
    AppointmentId 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_AppointmentFiles_Id PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_AppointmentFiles_AppointmentId FOREIGN KEY (AppointmentId) REFERENCES  Appointments(Id)
);

/*--------------------------------------------------------------------------------------------------------------  */
Feb 20, 2021 python
MySQL Server

events sql schema

<p>The provided SQL script creates a schema for managing events, their categories, tags, and associated media. Let&#39;s go through each table&#39;s purpose and its columns:</p> <ol> <li> <p><code>EventCategories</code> table:</p> <ul> …

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
/* Event schema */


CREATE TABLE EventCategories (
    Id int IDENTITY(1,1) not null,
    Title 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_EventCategories_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 Events (
    Id int IDENTITY(1,1) not null,
    EventCategoryId int not null,
    Title nvarchar(max) NOT NULL,
    Description nvarchar(max) NOT NULL,
    StartDate datetime not null DEFAULT GETDATE(),
    EndDate datetime not null DEFAULT GETDATE(),
    [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_Events_Id PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_Events_EventCategoryId FOREIGN KEY (EventCategoryId) REFERENCES  EventCategories(Id)
);

CREATE TABLE EventTags (
    EventId int,
    TagId int,
    [Order] int not null  default 0,
    CreatedAt datetime not null DEFAULT GETDATE(),
    UpdatedAt datetime not null DEFAULT GETDATE(),
    CONSTRAINT PK_EventTags PRIMARY KEY NONCLUSTERED ([EventId],[TagId]),
    CONSTRAINT FK_EventTags_EventId FOREIGN KEY (EventId) REFERENCES  Events(Id),
    CONSTRAINT FK_EventTags_TagId FOREIGN KEY (TagId) REFERENCES  Tags(Id),
);

CREATE TABLE EventMedia (
    Id int IDENTITY(1,1) not null,
    EventId 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_EventMedia_Id PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_EventMedia_EventId FOREIGN KEY (EventId) REFERENCES  Events(Id)
);


/*------------------------------------------------------------------------------------------------ */
Feb 20, 2021 python
MySQL Server

blog sql schema

<p>This SQL script defines a database schema for a blogging application. It creates several tables to store information related to blog posts, categories, tags, and media associated with the posts. …

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
/*------------------------------------------------------------------------------------------------ */

/* Blog schemna */

CREATE TABLE PostCategories (
    Id int IDENTITY(1,1) not null,
    Title 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_PostCategories_Id PRIMARY KEY CLUSTERED (Id)
);


CREATE TABLE Posts (
    Id int IDENTITY(1,1) not null,
    PostCategoryId int not null,
    Title nvarchar(max) NOT NULL,
    Description 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_Posts_Id PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_Posts_PostCategoryId FOREIGN KEY (PostCategoryId) REFERENCES  PostCategories(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 PostTags (
    PostId int,
    TagId int,
    [Order] int not null  default 0,
    CreatedAt datetime not null DEFAULT GETDATE(),
    UpdatedAt datetime not null DEFAULT GETDATE(),
    CONSTRAINT PK_PostTags PRIMARY KEY NONCLUSTERED ([PostId],[TagId]),
    CONSTRAINT FK_PostTags_PostId FOREIGN KEY (PostId) REFERENCES  Posts(Id),
    CONSTRAINT FK_PostTags_TagId FOREIGN KEY (TagId) REFERENCES  Tags(Id),
);


CREATE TABLE PostMedia (
    Id int IDENTITY(1,1) not null,
    PostId 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_PostMedia_Id PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_PostMedia_PostId FOREIGN KEY (PostId) REFERENCES  Posts(Id)
);

/* -----------------------------------------------------------------------------------  */
Feb 20, 2021 python
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