Database/SQL schema DevDates.sql at main · DevDatesDevelopers/Database · GitHub

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
CREATE TABLE Genders (
Id INT PRIMARY KEY,
DisplayName VARCHAR(255),
Created DATETIME,
Modified DATETIME,
ModifiedBy VARCHAR(255)
);

CREATE TABLE Users (
Id INT PRIMARY KEY,
Name VARCHAR(255),
DateOfBirth DATETIME,
GenderId INT,
Bio VARCHAR(255),
Email VARCHAR(255),
Created DATETIME,
Modified DATETIME,
ModifiedBy VARCHAR(255),
FOREIGN KEY (GenderId) REFERENCES Genders(Id)
);

CREATE TABLE ResourcesTypes (
Id INT PRIMARY KEY,
DisplayName VARCHAR(255),
Created DATETIME,
Modified DATETIME,
ModifiedBy VARCHAR(255)
);

CREATE TABLE Resources (
Id INT PRIMARY KEY,
ResourceURI VARCHAR(255),
ResourceTypeId INT,
FOREIGN KEY (ResourceTypeId) REFERENCES ResourcesTypes(Id)
);

CREATE TABLE Interests (
Id INT PRIMARY KEY,
DisplayName VARCHAR(255),
Created DATETIME,
Modified DATETIME,
ModifiedBy VARCHAR(255)
);

CREATE TABLE InterestsResources (
InterestId INT,
ResourceId INT,
PRIMARY KEY (InterestId, ResourceId),
FOREIGN KEY (InterestId) REFERENCES Interests(Id),
FOREIGN KEY (ResourceId) REFERENCES Resources(Id)
);

CREATE TABLE UsersResources (
UserId INT,
ResourceId INT,
PRIMARY KEY (UserId, ResourceId),
FOREIGN KEY (UserId) REFERENCES Users(Id),
FOREIGN KEY (ResourceId) REFERENCES Resources(Id)
);

CREATE TABLE GendersResources (
GenderId INT,
ResourceId INT,
PRIMARY KEY (GenderId, ResourceId),
FOREIGN KEY (GenderId) REFERENCES Genders(Id),
FOREIGN KEY (ResourceId) REFERENCES Resources(Id)
);

CREATE TABLE UsersPreferences (
UserId INT,
GenderId INT,
Created DATETIME,
PRIMARY KEY (UserId, GenderId),
FOREIGN KEY (UserId) REFERENCES Users(Id),
FOREIGN KEY (GenderId) REFERENCES Genders(Id)
);

CREATE TABLE UsersInterests (
UserId INT,
InterestId INT,
PRIMARY KEY (UserId, InterestId),
FOREIGN KEY (UserId) REFERENCES Users(Id),
FOREIGN KEY (InterestId) REFERENCES Interests(Id)
);

CREATE TABLE Likes (
LikerId INT,
LikedId INT,
Created DATETIME,
PRIMARY KEY (LikerId, LikedId),
FOREIGN KEY (LikerId) REFERENCES Users(Id),
FOREIGN KEY (LikedId) REFERENCES Users(Id)
);


ALTER TABLE Users
	ADD CONSTRAINT FK_Users_Genders
		FOREIGN KEY (GenderId)
			REFERENCES Genders(Id);

ALTER TABLE UsersResources
	ADD CONSTRAINT FK_UsersResources_Users
		FOREIGN KEY (UserId)
			REFERENCES Users(Id);

ALTER TABLE UsersResources
	ADD CONSTRAINT FK_UsersResources_Resources
		FOREIGN KEY (ResourceId)
			REFERENCES Resources(Id);

ALTER TABLE GendersResources
	ADD CONSTRAINT FK_GendersResources_Genders
		FOREIGN KEY (GenderId)
			REFERENCES Genders(Id);

ALTER TABLE GendersResources
	ADD CONSTRAINT FK_GendersResources_Resources
		FOREIGN KEY (ResourceId)
			REFERENCES Resources(Id);

ALTER TABLE UsersPreferences
	ADD CONSTRAINT FK_UsersPreferences_Users
		FOREIGN KEY (UserId)
			REFERENCES Users(Id);

ALTER TABLE UsersPreferences
	ADD CONSTRAINT FK_UsersPreferences_Genders
		FOREIGN KEY (GenderId)
			REFERENCES Genders(Id);

ALTER TABLE Resources
	ADD CONSTRAINT FK_Resources_ResourceTypes
		FOREIGN KEY (ResourceTypeId)
			REFERENCES ResourcesTypes(Id);

ALTER TABLE InterestsResources
	ADD CONSTRAINT FK_InterestsResources_Interests
		FOREIGN KEY (InterestId)
			REFERENCES Interests(Id);

ALTER TABLE InterestsResources
	ADD CONSTRAINT FK_InterestsResources_Resources
		FOREIGN KEY (ResourceId)
			REFERENCES Resources(Id);

ALTER TABLE UsersInterests
	ADD CONSTRAINT FK_UsersInterests_Users
		FOREIGN KEY (UserId)
			REFERENCES Users(Id);

ALTER TABLE UsersInterests
	ADD CONSTRAINT FK_UsersInterests_Interests
		FOREIGN KEY (InterestId)
			REFERENCES Interests(Id);

ALTER TABLE Likes
	ADD CONSTRAINT FK_Likes_Likers
		FOREIGN KEY (LikerId)
			REFERENCES Users(Id);

ALTER TABLE Likes
	ADD CONSTRAINT FK_Likes_Liked
		FOREIGN KEY (LikedId)
			REFERENCES Users(Id);