SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    25
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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:

    Code:
    -- 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. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,032
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    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
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    25
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks.
    Also, do I need to separate the users table into two or three tables based on different user_type?

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,032
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    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.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    Community Advisor silver trophy

    Join Date
    Nov 2006
    Location
    UK
    Posts
    2,551
    Mentioned
    40 Post(s)
    Tagged
    1 Thread(s)
    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. #6
    SitePoint Wizard
    Join Date
    Apr 2007
    Posts
    1,401
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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. #7
    SitePoint Addict
    Join Date
    Aug 2013
    Location
    New Zealand
    Posts
    277
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    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.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •