TechJobHub/database.sql at main · makmodol1173/TechJobHub · GitHub

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
CREATE TABLE recruiter (
    recruiter_id INT AUTO_INCREMENT PRIMARY KEY,
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    address TEXT,
    profile_picture VARCHAR(255)
);

CREATE TABLE company (
    company_id INT AUTO_INCREMENT PRIMARY KEY,
    recruiter_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    address TEXT,
    description TEXT,
    trade_license_number VARCHAR(50) UNIQUE NOT NULL,
    website_url VARCHAR(255),
    FOREIGN KEY (recruiter_id) REFERENCES recruiter(recruiter_id)
);

CREATE TABLE job_seeker (
    job_seeker_id INT AUTO_INCREMENT PRIMARY KEY,
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    address TEXT,
    profile_picture VARCHAR(255),
    education TEXT,
    resume VARCHAR(255)
);


CREATE TABLE skill (
    skill_id INT AUTO_INCREMENT PRIMARY KEY,
    job_seeker_id INT NOT NULL,
    skill_name VARCHAR(100) NOT NULL,
    FOREIGN KEY (job_seeker_id) REFERENCES job_seeker(job_seeker_id)
);

CREATE TABLE bookmark (
    bookmark_id INT AUTO_INCREMENT PRIMARY KEY,
    job_seeker_id INT NOT NULL,
    job_post_id INT NOT NULL,
    FOREIGN KEY (job_seeker_id) REFERENCES job_seeker(job_seeker_id),
    FOREIGN KEY (job_post_id) REFERENCES job_post(job_post_id)
);

CREATE TABLE application (
    application_id INT AUTO_INCREMENT PRIMARY KEY,
    job_seeker_id INT NOT NULL,
    job_post_id INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (job_seeker_id) REFERENCES job_seeker(job_seeker_id),
    FOREIGN KEY (job_post_id) REFERENCES job_post(job_post_id)
);

CREATE TABLE job_post (
    job_post_id INT AUTO_INCREMENT PRIMARY KEY,
    recruiter_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    key_responsibilities TEXT,
    location VARCHAR(255),
    educational_requirement TEXT,
    deadline DATE NOT NULL,
    year_of_experience INT,
    type VARCHAR(50),
    keywords TEXT,
    FOREIGN KEY (recruiter_id) REFERENCES recruiter(recruiter_id)
);

CREATE TABLE questions (
    question_id INT AUTO_INCREMENT PRIMARY KEY,
    job_post_id INT NOT NULL,
    question_1 TEXT,
    question_2 TEXT,
    question_3 TEXT,
    question_4 TEXT,
    question_5 TEXT,
    question_6 TEXT,
    question_7 TEXT,
    question_8 TEXT,
    question_9 TEXT,
    question_10 TEXT,
    FOREIGN KEY (job_post_id) REFERENCES job_post(job_post_id)
);

CREATE TABLE answers (
    answer_id INT AUTO_INCREMENT PRIMARY KEY,
    application_id INT NOT NULL,
    answer_1 TEXT,
    answer_2 TEXT,
    answer_3 TEXT,
    answer_4 TEXT,
    answer_5 TEXT,
    answer_6 TEXT,
    answer_7 TEXT,
    answer_8 TEXT,
    answer_9 TEXT,
    answer_10 TEXT,
    FOREIGN KEY (application_id) REFERENCES application(application_id)
);

CREATE TABLE assessment (
    assessment_id INT AUTO_INCREMENT PRIMARY KEY,
    answer_id INT NOT NULL,
    mark INT,
    FOREIGN KEY (answer_id) REFERENCES answers(answer_id)
);

CREATE TABLE interview (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    interview_date DATE NOT NULL,
    interview_time TIME NOT NULL,
    meet_link VARCHAR(500) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (email) REFERENCES job_seeker(email)
);