Database design for a job seek website(using MySQL and PHP)

Get Started
1

Hi all,

I am creating a job seek website with PHP and MySQL, one question is how to design the database better? The basic function of the website is that ‘jobseekers’ can login and search jobs, upload CVs, and ‘employers’ can login and post jobs, and browser CVs. Currently I just created 2 tables:


-- Table structure for table users
-- user_type: 0 - jobseekers
--            1 - employers
--            2 - administrator

CREATE TABLE users (
user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(40) NOT NULL,
email VARCHAR(80) NOT NULL,
pass CHAR(40) NOT NULL,
user_type TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
active CHAR(32),
last_login DATETIME NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY (user_id),
UNIQUE KEY (email),
INDEX login (email, pass)
) ENGINE = INNODB;


-- Table structure for table jobs

CREATE TABLE jobs (
job_id INT(11) NOT NULL AUTO_INCREMENT,
title VARCHAR(200) NOT NULL,
description text NOT NULL,
county VARCHAR(30) NOT NULL,
PRIMARY KEY (job_id)
)  ENGINE = MYISAM ;

But it’s far from enough I guess. Any suggestion how to improve the design? Also are there any resources I can refer to?

2

You might want to consider having “Employment Agency” as another user type and give the users the option to filter out any jobs from an Employment Agency

3

Thanks.
Also, do I need to separate the users table into two or three tables based on different user_type?

4

Add a table for user groups (types) and have a “linking” table called something like “user_group_membeship” with two fields, one for the user id and one for the user group id to allow for the possibility of a user needing to be in more then one group.

5

You might perhaps also have these tables if the functionality required them:

CV (id, user_id, uploaded_date, filename) - users might upload more than one CV for different jobs
Job_applications (id, user_id, job_id, applied_date, application_status) - users might like to see their history of applications and what happened
Searches (id, user_id, term) - this can allow users to easily access previous searches and could form the basis of an alerts system, matching jobs to users as they are added

6

It’s really hard to design DB tables w/o completing the front-end design. I’m sure you can guess some tables but it’s much easier to design db structures after UI design. This way, you know all the data that needs to be captured.

7

You might also want to look into the legal requirements for your country. That and your site’s terms and conditions will probably have an impact on your data model.