-
Notifications
You must be signed in to change notification settings - Fork 0
/
Database.sql
175 lines (155 loc) · 5.07 KB
/
Database.sql
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
create table IF NOT EXISTS users(
id SERIAL PRIMARY KEY NOT NULL,
email varchar(100) NOT NULL,
password varchar(100) NOT NULL
);
create table IF NOT EXISTS personals(
id SERIAL NOT NULL,
user_id integer NOT NULL,
first_name varchar(20) NOT NULL,
last_name varchar(20) NOT NULL,
status boolean DEFAULT true,
PRIMARY KEY(id),
CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES users(id)
);
create table IF NOT EXISTS jobs(
id SERIAL PRIMARY KEY NOT NULL,
job_name varchar(50) NOT NULL
);
create table IF NOT EXISTS schools(
id SERIAL PRIMARY KEY NOT NULL,
name varchar(50) NOT NULL
);
create table IF NOT EXISTS school_parts(
id SERIAL PRIMARY KEY NOT NULL,
name varchar(50) NOT NULL,
);
create table IF NOT EXISTS languages(
id SERIAL PRIMARY KEY NOT NULL,
language_name varchar(50) NOT NULL
);
create table IF NOT EXISTS programing_languages(
id SERIAL PRIMARY KEY NOT NULL,
language_name varchar(50) NOT NULL
);
create table IF NOT EXISTS job_seekers(
id SERIAL NOT NULL,
user_id integer NOT NULL,
job_wanted_id integer NOT NULL,
first_name varchar(20) NOT NULL,
last_name varchar(20) NOT NULL,
nationality_id char(11) NOT NULL,
year_of_birth varchar(10) NOT NULL,
status boolean DEFAULT false,
PRIMARY KEY(id),
CONSTRAINT fk_users FOREIGN KEY(user_id) REFERENCES users(id),
CONSTRAINT fk_job_wanted FOREIGN KEY(job_wanted_id) REFERENCES jobs(id)
);
create table IF NOT EXISTS jobseeker_contact_informations(
id SERIAL NOT NULL,
jobseeker_id integer NOT NULL,
linkedin_adress varchar(100) NOT NULL,
github_adress varchar(100) NOT NULL,
about varchar(200) NOT NULL,
status boolean DEFAULT false,
PRIMARY KEY(id),
CONSTRAINT fk_job_seekers FOREIGN KEY(jobseeker_id) REFERENCES job_seekers(id)
);
create table IF NOT EXISTS jobseeker_school_informations(
id SERIAL NOT NULL,
school_id integer NOT NULL,
school_part_id integer NOT NULL,
jobseeker_id integer NOT NULL,
university_start_date date NOT NULL,
university_graduation_date date,
PRIMARY KEY(id),
CONSTRAINT fk_schools FOREIGN KEY(school_id) REFERENCES schools(id),
CONSTRAINT fk_school_parts FOREIGN KEY(school_part_id) REFERENCES school_parts(id),
CONSTRAINT fk_job_seekers FOREIGN KEY(jobseeker_id) REFERENCES job_seekers(id)
);
create table IF NOT EXISTS job_experiences(
id SERIAL NOT NULL,
job_seeker_id integer NOT NULL,
job_id integer NOT NULL,
company_name varchar(50) NOT NULL,
start_date date NOT NULL,
end_date date,
PRIMARY KEY(id),
CONSTRAINT fk_job_seekers FOREIGN KEY(job_seeker_id) REFERENCES job_seekers(id),
CONSTRAINT fk_jobs FOREIGN KEY(job_id) REFERENCES jobs(id)
);
create table IF NOT EXISTS known_languages(
id SERIAL NOT NULL,
job_seekers_id integer NOT NULL,
language_id integer NOT NULL,
level integer NOT NULL,
PRIMARY KEY(id),
CONSTRAINT fk_job_seekers FOREIGN KEY(job_seekers_id) REFERENCES job_seekers(id),
CONSTRAINT fk_languages FOREIGN KEY(language_id) REFERENCES languages(id)
);
create table IF NOT EXISTS known_programing_languages(
id SERIAL NOT NULL,
job_seekers_id integer NOT NULL,
programing_language_id integer NOT NULL,
PRIMARY KEY(id),
CONSTRAINT fk_job_seekers FOREIGN KEY(job_seekers_id) REFERENCES job_seekers(id),
CONSTRAINT fk_programing_languages FOREIGN KEY(programing_language_id) REFERENCES programing_languages(id)
);
create table IF NOT EXISTS employers(
id SERIAL NOT NULL,
user_id integer NULL,
company_name varchar(30) NOT NULL,
website varchar(30) NOT NULL,
telephone varchar(15) NOT NULL,
status boolean DEFAULT false ,
PRIMARY KEY(id),
CONSTRAINT fk_users FOREIGN KEY(user_id) REFERENCES users(id)
);
CREATE TABLE IF NOT EXISTS email_activations
(
id SERIAL NOT NULL,
user_id integer NOT NULL,
activation_code character varying(200) NOT NULL,
is_activated bool NOT NULL default false,
PRIMARY KEY(id),
CONSTRAINT fk_users FOREIGN KEY(user_id) REFERENCES users(id)
);
CREATE TABLE IF NOT EXISTS mernis_activations
(
id SERIAL NOT NULL,
user_id integer NOT NULL,
is_approved boolean NOT NULL,
PRIMARY KEY(id),
CONSTRAINT fk_users FOREIGN KEY(user_id) REFERENCES users(id)
);
CREATE TABLE IF NOT EXISTS cities
(
id SERIAL PRIMARY KEY NOT NULL,
name character varying(50) NOT NULL
);
CREATE TABLE IF NOT EXISTS images
(
id SERIAL NOT NULL,
user_id integer NOT NULL,
image_link character varying(200) NOT NULL,
PRIMARY KEY(id),
CONSTRAINT fk_users FOREIGN KEY(user_id) REFERENCES users(id)
);
CREATE TABLE IF NOT EXISTS job_adverts
(
id SERIAL NOT NULL,
employer_id integer NOT NULL,
job_position_id integer NOT NULL,
city_id integer NOT NULL,
description character varying NOT NULL,
min_salary integer,
max_salary integer,
number_of_open_position integer NOT NULL,
created_at timestamp with time zone NOT NULL,
application_deadline timestamp with time zone,
is_active boolean NOT NULL default true,
PRIMARY KEY(id),
CONSTRAINT fk_employers FOREIGN KEY(employer_id) REFERENCES employers(id),
CONSTRAINT fk_jobs FOREIGN KEY(job_position_id) REFERENCES jobs(id),
CONSTRAINT fk_cities FOREIGN KEY(city_id) REFERENCES cities(id)
);