database

Human Resources Management System

Database

---

CREATE TABLE public.users(
	id INTEGER GENERATED BY DEFAULT AS IDENTITY(INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1),
	email CHARACTER VARYING(320) NOT NULL,
	password CHARACTER VARYING(25) NOT NULL,
	CONSTRAINT pk_users PRIMARY KEY(id),
	CONSTRAINT uc_users_email UNIQUE(email)
);

CREATE TABLE public.employees(
	id INTEGER NOT NULL, 
	first_name CHARACTER VARYING(35) NOT NULL,
	last_name CHARACTER VARYING(35) NOT NULL,
	CONSTRAINT pk_employees PRIMARY KEY(id),
	CONSTRAINT fk_emplpyees_users FOREIGN KEY(id) REFERENCES public.users(id)
);

CREATE TABLE public.candidates(
	id INTEGER NOT NULL,
	first_name CHARACTER VARYING(35) NOT NULL,
	last_name CHARACTER VARYING(35) NOT NULL,
	national_id CHARACTER VARYING(11) NOT NULL,
	birth_year INTEGER NOT NULL,
	CONSTRAINT pk_candidates PRIMARY KEY(id),
	CONSTRAINT fk_candidates_users FOREIGN KEY(id) REFERENCES public.users(id),
	CONSTRAINT uc_candidates_national_id UNIQUE(national_id)
);

CREATE TABLE public.employers(
	id INTEGER NOT NULL,
	company_name CHARACTER VARYING(255) NOT NULL,
	web_address CHARACTER VARYING(50) NOT NULL,
	phone_number CHARACTER VARYING(12) NOT NULL,
	CONSTRAINT pk_employers PRIMARY KEY(id),
	CONSTRAINT fk_employers_users FOREIGN KEY(id) REFERENCES public.users(id)
);

CREATE TABLE public.verification_codes(
	id INTEGER GENERATED BY DEFAULT AS IDENTITY(INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1),
	code CHARACTER VARYING(38) NOT NULL,
	is_verified BOOLEAN NOT NULL,
	verified_date DATE,
	CONSTRAINT pk_verification_codes PRIMARY KEY(id),
	CONSTRAINT uc_verification_codes_code UNIQUE(code)
);

CREATE TABLE public.verification_code_candidates(
	id INTEGER NOT NULL,
	candidate_id INTEGER NOT NULL,
	CONSTRAINT pk_verification_code_candidates PRIMARY KEY(id),
	CONSTRAINT fk_verification_code_candidates_verification_codes FOREIGN KEY(id) REFERENCES public.verification_codes(id),
	CONSTRAINT fk_verification_code_candidates_candidates FOREIGN KEY(candidate_id) REFERENCES public.candidates(id)
);

CREATE TABLE public.verification_code_employers(
	id INTEGER NOT NULL,
	employers_id INTEGER NOT NULL,
	CONSTRAINT pk_verification_code_employers PRIMARY KEY(id),
	CONSTRAINT fk_verification_code_employers_verification_codes FOREIGN KEY(id) REFERENCES public.verification_codes(id),
	CONSTRAINT fk_verification_code_employers_employers FOREIGN KEY(employers_id) REFERENCES public.employers(id)
);

CREATE TABLE public.employee_confirms(
	id INTEGER GENERATED BY DEFAULT AS IDENTITY(INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1),
	employee_id INTEGER NOT NULL,
	is_confirmed BOOLEAN NOT NULL,
	confirm_date DATE,
	CONSTRAINT pk_employee_confirms PRIMARY KEY(id),
	CONSTRAINT fk_employee_confirms_employees FOREIGN KEY(employee_id) REFERENCES public.employees(id)
);

CREATE TABLE public.employee_confirm_employers(
	id INTEGER NOT NULL,
	employer_id INTEGER NOT NULL,
	CONSTRAINT pk_employee_confirm_employers PRIMARY KEY(id),
	CONSTRAINT fk_employee_confirm_employers_employee_confirms FOREIGN KEY(id) REFERENCES public.employee_confirms(id),
	CONSTRAINT fk_employee_confirm_employers_employers FOREIGN KEY(employer_id) REFERENCES public.employers(id)
);

CREATE TABLE public.job_titles(
	id INTEGER GENERATED BY DEFAULT AS IDENTITY(INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1),
	title CHARACTER VARYING(255),
	CONSTRAINT pk_job_titles PRIMARY KEY(id),
	CONSTRAINT uc_job_titles_title UNIQUE(title)
);

CREATE TABLE public.countries(
	id INTEGER GENERATED BY DEFAULT AS IDENTITY(INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1),
	country_name CHARACTER VARYING(60) NOT NULL,
	CONSTRAINT pk_countries PRIMARY KEY(id),
	CONSTRAINT uc_countries_country_name UNIQUE(country_name)
);

CREATE TABLE public.cities(
	id INTEGER GENERATED BY DEFAULT AS IDENTITY(INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1),
	country_id INTEGER NOT NULL,
	city_name CHARACTER VARYING(105) NOT NULL,
	CONSTRAINT pk_cities PRIMARY KEY(id),
	CONSTRAINT fk_cities_countries FOREIGN KEY(country_id) REFERENCES public.countries(id),
	CONSTRAINT uc_cities_city_name UNIQUE(city_name)
);

CREATE TABLE public.job_advertisement(
	id INTEGER GENERATED BY DEFAULT AS IDENTITY(INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1),
	employer_id INTEGER NOT NULL,
	job_title_id INTEGER NOT NULL,
	job_description CHARACTER VARYING(255) NOT NULL,
	city_id INTEGER NOT NULL,
	min_salary INTEGER,
	max_salary INTEGER,
	number_of_open_positions INTEGER NOT NULL,
	posting_date DATE NOT NULL,
	application_deadline DATE NOT NULL,
	is_active BOOLEAN NOT NULL,
	CONSTRAINT pk_job_advertisement PRIMARY KEY(id),
	CONSTRAINT fk_job_advertisement_employers FOREIGN KEY(employer_id) REFERENCES public.employers(id),
	CONSTRAINT fk_job_advertisement_job_titles FOREIGN KEY(job_title_id) REFERENCES public.job_titles(id),
	CONSTRAINT fk_job_advertisement_cities FOREIGN KEY(city_id) REFERENCES public.cities(id)
);