Repository navigation
Expand file tree
/
Copy pathhacktech.sql
More file actions
122 lines (113 loc) · 4.45 KB
/
Copy pathhacktech.sql
File metadata and controls
122 lines (113 loc) · 4.45 KB
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
DROP TABLE IF EXISTS race;
DROP TABLE IF EXISTS diet;
DROP TABLE IF EXISTS status;
DROP TABLE IF EXISTS applications;
DROP TABLE IF EXISTS members;
DROP TABLE IF EXISTS caltech_waiver;
DROP TABLE IF EXISTS medical_info;
DROP TABLE IF EXISTS users;
-- Users Table
CREATE TABLE users (
user_id INT NOT NULL AUTO_INCREMENT,
email VARCHAR(255) NOT NULL,
last_login DATETIME DEFAULT NULL,
password_hash VARCHAR(255) NOT NULL,
password_reset_key CHAR(32) DEFAULT NULL,
password_reset_expiration DATETIME DEFAULT NULL,
activated BOOLEAN DEFAULT FALSE,
confirm_account_key CHAR(32) DEFAULT NULL,
admin BOOLEAN DEFAULT FALSE,
PRIMARY KEY (user_id),
UNIQUE (email)
);
-- Members Table
CREATE TABLE members (
user_id INT NOT NULL,
first_name VARCHAR(255) NOT NULL,
preferred_name VARCHAR(255) DEFAULT NULL,
middle_name VARCHAR(255) DEFAULT NULL,
last_name VARCHAR(255) NOT NULL,
date_of_birth DATE NOT NULL,
PRIMARY KEY (user_id),
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
CREATE TABLE applications(
application_id INT NOT NULL AUTO_INCREMENT,
user_id INT NOT NULL,
application_year DATETIME DEFAULT NULL,
phone VARCHAR(64) DEFAULT NULL,
school VARCHAR(255) DEFAULT NULL,
major VARCHAR(255) DEFAULT NULL,
degree_type VARCHAR(50) DEFAULT NULL,
graduation_year CHAR(4) DEFAULT NULL,
github VARCHAR(255) DEFAULT NULL,
linkedin VARCHAR(255) DEFAULT NULL,
resume VARCHAR(255) DEFAULT NULL,
latino BOOLEAN DEFAULT NULL,
gender CHAR(1) DEFAULT NULL, -- W, M, O
shirt_size VARCHAR(2) DEFAULT NULL, -- XS, S, M, L, XL
transportation BOOLEAN DEFAULT NULL,
in_state BOOLEAN DEFAULT NULL,
bus_from VARCHAR(50) DEFAULT NULL,
airport VARCHAR(50) DEFAULT NULL,
diet_rest BOOLEAN DEFAULT NULL,
diet_rest_detail TEXT DEFAULT NULL,
q1 TEXT DEFAULT NULL,
q2 TEXT DEFAULT NULL,
q3 TEXT DEFAULT NULL,
q4 TEXT DEFAULT NULL,
q5 TEXT DEFAULT NULL,
q6 TEXT DEFAULT NULL,
code_of_conduct BOOLEAN DEFAULT NULL,
PRIMARY KEY (application_id),
UNIQUE (user_id, application_year) -- One application per year.
);
-- Review Table
CREATE TABLE status(
user_id INT NOT NULL,
application_id INT NOT NULL,
status VARCHAR(50) NOT NULL,
decider_user_id INT DEFAULT NULL,
reimbursement_amt INT DEFAULT NULL,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (application_id) REFERENCES applications(application_id),
PRIMARY KEY (user_id, application_id)
);
CREATE TABLE diet(
user_id INT,
diet_id INT NOT NULL AUTO_INCREMENT,
diet_restrictions VARCHAR(50),
PRIMARY KEY (diet_id),
UNIQUE(user_id, diet_restrictions),
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
CREATE TABLE race(
user_id INT,
race_id INT NOT NULL AUTO_INCREMENT,
race_type VARCHAR(50),
PRIMARY KEY (race_id),
UNIQUE(user_id, race_type),
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
CREATE TABLE caltech_waiver(
user_id INT,
caltech_waiver_id INT NOT NULL AUTO_INCREMENT,
reviewer_user_id INT NOT NULL,
caltech_waiver_path VARCHAR(255) NOT NULL,
caltech_waiver_status VARCHAR(20) NOT NULL,
submitted_time DATETIME NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(user_id),
PRIMARY KEY(caltech_waiver_id),
UNIQUE(user_id)
);
CREATE TABLE medical_info(
user_id INT,
medical_info_id INT NOT NULL AUTO_INCREMENT,
reviewer_user_id INT NOT NULL,
medical_info_path VARCHAR(255) NOT NULL,
medical_info_status VARCHAR(20) NOT NULL,
submitted_time DATETIME NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(user_id),
PRIMARY KEY(medical_info_id),
UNIQUE(user_id)
);