· 6 years ago · Dec 15, 2019, 10:58 PM
1CREATE TABLE IF NOT EXISTS supplicants (
2 supplicant_id SERIAL,
3 name VARCHAR,
4 address VARCHAR,
5 telephone_number VARCHAR,
6 email VARCHAR,
7 PRIMARY KEY (supplicant_id)
8) WITHOUT OIDS;
9
10CREATE TABLE IF NOT EXISTS cases (
11 case_id SERIAL,
12 category VARCHAR,
13 description text,
14 supplicant_id int8,
15 case_status VARCHAR NOT NULL,
16 operator_id int8,
17 industry_affiliation VARCHAR,
18 source_of_knowledge_jc VARCHAR,
19 application_date_time TIMESTAMP,
20 meeting_date_time TIMESTAMP,
21 done bool NOT NULL,
22 PRIMARY KEY (case_id)
23) WITHOUT OIDS;
24
25ALTER TABLE cases ADD CONSTRAINT cases_supplicant_id_foreign FOREIGN KEY (supplicant_id) REFERENCES supplicants (supplicant_id) ;
26
27CREATE TABLE IF NOT EXISTS documents (
28 document_id SERIAL,
29 title text,
30 case_id int8 NOT NULL,
31 type VARCHAR,
32 data_id int8,
33 PRIMARY KEY (document_id)
34) WITHOUT OIDS;
35
36ALTER TABLE documents ADD CONSTRAINT documents_case_id_foreign FOREIGN KEY (case_id) REFERENCES cases (case_id) ;
37
38CREATE TABLE IF NOT EXISTS site_users (
39 user_id SERIAL,
40 name VARCHAR,
41 ROLE int8,
42 st_login VARCHAR NOT NULL,
43 personal_data text,
44 password_hash VARCHAR,
45 cookie text,
46 PRIMARY KEY (user_id),
47 UNIQUE(st_login, cookie)
48) WITHOUT OIDS;
49
50CREATE TABLE IF NOT EXISTS applications (
51 application_id SERIAL,
52 title VARCHAR,
53 case_id int8 NOT NULL,
54 description text,
55 addressee_user_id int8 NOT NULL,
56 STATUS VARCHAR NOT NULL,
57 date_time TIMESTAMP,
58 PRIMARY KEY (application_id)
59) WITHOUT OIDS;
60
61ALTER TABLE applications ADD CONSTRAINT applications_addressee_user_id_foreign FOREIGN KEY (addressee_user_id) REFERENCES site_users (user_id) ;
62ALTER TABLE applications ADD CONSTRAINT applications_case_id_foreign FOREIGN KEY (case_id) REFERENCES cases (case_id) ;
63
64CREATE TABLE IF NOT EXISTS administration (
65 admin_id SERIAL,
66 name VARCHAR,
67 TYPE VARCHAR,
68 email VARCHAR,
69 user_id int8 NOT NULL,
70 PRIMARY KEY (admin_id)
71) WITHOUT OIDS;
72
73ALTER TABLE administration ADD CONSTRAINT administration_user_id_foreign FOREIGN KEY (user_id) REFERENCES site_users (user_id) ;
74
75CREATE TABLE IF NOT EXISTS notifications (
76 notification_id SERIAL,
77 text text NOT NULL,
78 STATUS VARCHAR NOT NULL,
79 user_id int8 NOT NULL,
80 date_time TIMESTAMP,
81 event_data json,
82 PRIMARY KEY (notification_id)
83) WITHOUT OIDS;
84
85ALTER TABLE notifications ADD CONSTRAINT notifications_user_id_foreign FOREIGN KEY (user_id) REFERENCES site_users (user_id) ;
86
87CREATE TABLE IF NOT EXISTS teachers (
88 teacher_id SERIAL,
89 competence VARCHAR,
90 user_id int8 NOT NULL,
91 PRIMARY KEY (teacher_id)
92) WITHOUT OIDS;
93
94ALTER TABLE teachers ADD CONSTRAINT teachers_user_id_foreign FOREIGN KEY (user_id) REFERENCES site_users (user_id) ;
95
96CREATE TABLE IF NOT EXISTS case_teachers_relations (
97 relation_id SERIAL,
98 case_id int8 NOT NULL,
99 teacher_id int8 NOT NULL,
100 ROLE VARCHAR,
101 PRIMARY KEY (relation_id)
102) WITHOUT OIDS;
103
104ALTER TABLE case_teachers_relations ADD CONSTRAINT case_teachers_relations_case_id_foreign FOREIGN KEY (case_id) REFERENCES cases (case_id) ;
105ALTER TABLE case_teachers_relations ADD CONSTRAINT case_teachers_relations_teacher_id_foreign FOREIGN KEY (teacher_id) REFERENCES teachers (teacher_id) ;
106
107CREATE TABLE IF NOT EXISTS students (
108 student_id SERIAL,
109 teacher_id int8,
110 functional_group VARCHAR,
111 points DECIMAL DEFAULT 0.0,
112 user_id int8 NOT NULL,
113 subgroup_name VARCHAR,
114 PRIMARY KEY (student_id)
115) WITHOUT OIDS;
116
117CREATE TABLE IF NOT EXISTS students_subgroup (
118 name VARCHAR NOT NULL,
119 admin_id int8 NOT NULL,
120 PRIMARY KEY (name)
121) WITHOUT OIDS;
122
123ALTER TABLE students ADD CONSTRAINT students_user_id_foreign FOREIGN KEY (user_id) REFERENCES site_users (user_id) ;
124ALTER TABLE students ADD CONSTRAINT students_teacher_id_foreign FOREIGN KEY (teacher_id) REFERENCES teachers (teacher_id) ;
125ALTER TABLE students ADD CONSTRAINT students_subgroup_name_foreign FOREIGN KEY (subgroup_name) REFERENCES students_subgroup (name) ;
126
127ALTER TABLE students_subgroup ADD CONSTRAINT students_subgroup_admin_id_foreign FOREIGN KEY (admin_id) REFERENCES students (student_id) ;
128
129CREATE TABLE IF NOT EXISTS case_student_relations (
130 relation_id SERIAL,
131 case_id int8 NOT NULL,
132 student_id int8 NOT NULL,
133 ROLE VARCHAR,
134 PRIMARY KEY (relation_id)
135) WITHOUT OIDS;
136
137ALTER TABLE case_student_relations ADD CONSTRAINT case_student_relations_case_id_foreign FOREIGN KEY (case_id) REFERENCES cases (case_id) ;
138ALTER TABLE case_student_relations ADD CONSTRAINT case_student_relations_student_id_foreign FOREIGN KEY (student_id) REFERENCES students (student_id) ;
139
140CREATE TABLE IF NOT EXISTS rejected_requests (
141 request_id SERIAL,
142 reason text,
143 operator_id int8,
144 supplicant_name VARCHAR,
145 request_text text,
146 date_time TIMESTAMP,
147 PRIMARY KEY (request_id)
148) WITHOUT OIDS;
149
150ALTER TABLE rejected_requests ADD CONSTRAINT rejected_requests_operator_id_foreign FOREIGN KEY (operator_id) REFERENCES students (student_id) ;
151
152CREATE TABLE IF NOT EXISTS points_feed (
153 event_id SERIAL,
154 student_id int8 NOT NULL,
155 teacher_id int8 NOT NULL,
156 points DECIMAL NOT NULL,
157 reason text,
158 date_time TIMESTAMP,
159 PRIMARY KEY (event_id)
160) WITHOUT OIDS;
161
162ALTER TABLE points_feed ADD CONSTRAINT points_feed_student_id_foreign FOREIGN KEY (student_id) REFERENCES students (student_id) ;
163ALTER TABLE points_feed ADD CONSTRAINT points_feed_teacher_id_foreign FOREIGN KEY (teacher_id) REFERENCES teachers (teacher_id) ;
164
165CREATE TABLE IF NOT EXISTS cases_feed (
166 event_id SERIAL,
167 case_id int8 NOT NULL,
168 case_status VARCHAR NOT NULL,
169 date_time TIMESTAMP,
170 event_data json,
171 done bool NOT NULL,
172 PRIMARY KEY (event_id)
173) WITHOUT OIDS;
174
175ALTER TABLE cases_feed ADD CONSTRAINT cases_feed_case_id_foreign FOREIGN KEY (case_id) REFERENCES cases (case_id) ;
176
177CREATE TABLE IF NOT EXISTS duty_roster (
178 duty_id SERIAL,
179 student_id int8 NOT NULL,
180 class_room VARCHAR,
181 start_date_time TIMESTAMP,
182 finish_date_time TIMESTAMP,
183 PRIMARY KEY (duty_id)
184) WITHOUT OIDS;
185
186ALTER TABLE duty_roster ADD CONSTRAINT duty_roster_student_id_foreign FOREIGN KEY (student_id) REFERENCES students (student_id) ;