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
|
CREATE TABLE IF NOT EXISTS types
(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL UNIQUE
) ENGINE = InnoDB
DEFAULT CHARSET = utf8;
CREATE TABLE IF NOT EXISTS class
(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL UNIQUE
) ENGINE = InnoDB
DEFAULT CHARSET = utf8;
CREATE TABLE IF NOT EXISTS users
(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(255) NOT NULL,
middlename VARCHAR(255) DEFAULT NULL,
surname VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
class_id INTEGER NOT NULL,
type_id INTEGER NOT NULL,
is_admin BOOLEAN DEFAULT FALSE,
UNIQUE KEY uk_name (name, middlename, surname),
CONSTRAINT `fk_class_user` FOREIGN KEY (class_id) REFERENCES class (id),
CONSTRAINT `fk_type_user` FOREIGN KEY (type_id) REFERENCES types (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8;
CREATE TABLE IF NOT EXISTS quotes
(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
user_id INTEGER NOT NULL, -- Person who heard it
author_id INTEGER NOT NULL, -- Person who said it
quote VARCHAR(255) NOT NULL,
UNIQUE KEY uk_quote (author_id, quote),
CONSTRAINT `fk_user_quote1` FOREIGN KEY (user_id) REFERENCES users (id),
CONSTRAINT `fk_user_quote2` FOREIGN KEY (author_id) REFERENCES users (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8;
CREATE TABLE IF NOT EXISTS ranking_questions
(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
question VARCHAR(255) NOT NULL,
type_id INTEGER NOT NULL,
UNIQUE KEY uk_ranking_question (question, type_id),
CONSTRAINT `fk_type_question` FOREIGN KEY (type_id) REFERENCES types (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8;
CREATE TABLE IF NOT EXISTS ranking_answers
(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
question_id INTEGER NOT NULL,
user_id INTEGER NOT NULL, -- Submitter
answer_id INTEGER NOT NULL, -- Selected pupil
UNIQUE KEY uk_ranking_answer (question_id, user_id),
CONSTRAINT `fk_question_answer` FOREIGN KEY (question_id) REFERENCES users (id),
CONSTRAINT `fk_user_answer1` FOREIGN KEY (user_id) REFERENCES users (id),
CONSTRAINT `fk_user_answer2` FOREIGN KEY (answer_id) REFERENCES users (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8;
CREATE TABLE IF NOT EXISTS mottos
(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
description VARCHAR(255) NOT NULL DEFAULT '',
UNIQUE KEY main (name, description)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8;
CREATE TABLE IF NOT EXISTS motto_votes
(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
user_id INTEGER NOT NULL,
motto_id INTEGER NOT NULL,
votes SMALLINT UNSIGNED NOT NULL DEFAULT 0,
UNIQUE KEY uk_vote (user_id, motto_id),
CONSTRAINT `fk_voted_user` FOREIGN KEY (user_id) REFERENCES users (id),
CONSTRAINT `fk_voted_vote` FOREIGN KEY (motto_id) REFERENCES mottos (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8;
CREATE TABLE IF NOT EXISTS profile_input_types
(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
type VARCHAR(20) NOT NULL UNIQUE
) ENGINE = InnoDB
DEFAULT CHARSET = utf8;
CREATE TABLE IF NOT EXISTS profile_questions
(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
question VARCHAR(255) NOT NULL UNIQUE,
question_type INTEGER NOT NULL,
CONSTRAINT `fk_profile_question_type` FOREIGN KEY (question_type) REFERENCES profile_input_types (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8;
CREATE TABLE IF NOT EXISTS profile_answers
(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
question_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
answer TEXT NULL, -- Consider VARCHAR
UNIQUE KEY uk_profile_answer (question_id, user_id),
CONSTRAINT `fk_profile_user` FOREIGN KEY (user_id) REFERENCES users (id),
CONSTRAINT `fk_profile_question` FOREIGN KEY (question_id) REFERENCES profile_questions (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8;
CREATE TABLE IF NOT EXISTS profile_comments
(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
profile_id INTEGER NOT NULL, -- User's profile
user_id INTEGER NOT NULL, -- User who commented
comment TEXT NOT NULL,
CONSTRAINT `fk_user_profile` FOREIGN KEY (profile_id) REFERENCES users (id),
CONSTRAINT `fk_user_commenter` FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8;
CREATE TABLE IF NOT EXISTS question_questions
(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
question VARCHAR(255) NOT NULL UNIQUE
) ENGINE = InnoDB
DEFAULT CHARSET = utf8;
CREATE TABLE IF NOT EXISTS question_options
(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
answer_option VARCHAR(50),
question_id INTEGER NOT NULL,
CONSTRAINT `fk_question_question2` FOREIGN KEY (question_id) REFERENCES question_questions (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8;
CREATE TABLE IF NOT EXISTS question_answers
(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
question_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
option_id INTEGER NOT NULL,
UNIQUE KEY uk_question_answer (question_id, user_id),
CONSTRAINT `fk_question_user` FOREIGN KEY (user_id) REFERENCES users (id),
CONSTRAINT `fk_question_question` FOREIGN KEY (question_id) REFERENCES question_questions (id),
CONSTRAINT `fk_question_answer2` FOREIGN KEY (option_id) REFERENCES question_options (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8;
CREATE TABLE IF NOT EXISTS profile_char
(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
profile_id INTEGER NOT NULL, -- profile
user_id INTEGER NOT NULL, -- user who submitted
txt VARCHAR(255) NOT NULL,
UNIQUE KEY uk_profile_char (profile_id, user_id),
CONSTRAINT `fk_char_user` FOREIGN KEY (profile_id) REFERENCES users (id),
CONSTRAINT `fk_char_user2` FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8;
CREATE TABLE IF NOT EXISTS teacher_prediction
(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
user_id INTEGER NOT NULL UNIQUE,
teacher_id INTEGER NOT NULL,
CONSTRAINT `fk_teacher_user` FOREIGN KEY (user_id) REFERENCES users (id),
CONSTRAINT `fk_teacher_teacher` FOREIGN KEY (teacher_id) REFERENCES users (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8;
CREATE TABLE IF NOT EXISTS secrets
(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
user_id INTEGER NOT NULL,
secret VARCHAR(255) NOT NULL,
UNIQUE KEY uk_secret (user_id, secret),
CONSTRAINT `fk_user_secret` FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8;
CREATE TABLE IF NOT EXISTS profile_image_ratios
(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
question_id INTEGER NOT NULL UNIQUE,
x INTEGER NOT NULL,
y INTEGER NOT NULL,
CONSTRAINT `fk_profile_image_question` FOREIGN KEY (question_id) REFERENCES profile_questions (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8;
|
