SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jan 2014
    Location
    LA, USA
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Please take a look at the database I designed for my website

    My website is a job seek site. There are three types of users: User, Employee or Admin. User can search and apply for a job, Employee can post a job, browser resumes, Admin is to manage the site. Here are all the tables that I defined.


    Code:
    -- Users table, users = jobseekers, containing jobseekers info  
    DROP TABLE IF EXISTS users;
    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(60) NOT NULL,
      user_phone VARCHAR(11) NOT NULL,
      user_address VARCHAR(250) NOT NULL,
      active CHAR(32) NULL,
      last_login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
                                         ON UPDATE CURRENT_TIMESTAMP,
      last_login_ip VARCHAR(15) NOT NULL,
      registration_time DATETIME NOT NULL,
      registration_ip VARCHAR(15) NOT NULL,
      PRIMARY KEY (user_id),
      UNIQUE KEY (email),
      INDEX login (email, pass)
    ) ENGINE = INNODB;
    
    
    -- Employers table, containing employers info
    DROP TABLE IF EXISTS employers;
    CREATE TABLE employers (
      employer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
      first_name VARCHAR(20) NOT NULL,
      last_name VARCHAR(40) NOT NULL,
      company_name VARCHAR(80) NOT NULL,
      email VARCHAR(80) NOT NULL,
      pass CHAR(40) NOT NULL,
      employer_phone VARCHAR(11) NOT NULL,
      employer_mobile VARCHAR(11),
      employer_address VARCHAR(250) NOT NULL,
      active CHAR(32) NULL,
      last_login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
                                         ON UPDATE CURRENT_TIMESTAMP,
      last_login_ip VARCHAR(15) NOT NULL,
      registration_time DATETIME NOT NULL,
      registration_ip VARCHAR(15) NOT NULL,
      PRIMARY KEY (employer_id),
      UNIQUE KEY (email),
      INDEX login (email, pass)
    ) ENGINE = INNODB;
    
    
    -- Administrators table, containing site administrators info
    -- Note: move created_time after last_login_time, otherwise SQL error #1293
    DROP TABLE IF EXISTS administrators;
    CREATE TABLE administrators (
       admin_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,
       last_login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
                                         ON UPDATE CURRENT_TIMESTAMP,
       last_login_ip VARCHAR(15) NOT NULL,
       created_time DATETIME NOT NULL,
       PRIMARY KEY (admin_id)
    ) ENGINE = INNODB;
    
    
    -- CVs table, containing CV info
    DROP TABLE IF EXISTS cvs;
    CREATE TABLE cvs (
       cv_id INT(10) UNSIGNED NOT NULL auto_increment,
       cv_name VARCHAR(60) NOT NULL,
       user_id INT UNSIGNED NOT NULL,
       description VARCHAR(80),  
       PRIMARY KEY (cv_id)
    ) ENGINE = INNODB;
    
    
    -- Jobs table, containing job information
    -- Note: must use MYISAM to support Fulltext search
    DROP TABLE IF EXISTS jobs;
    CREATE TABLE jobs (
       job_id INT(10) UNSIGNED NOT NULL auto_increment,
       job_title VARCHAR(30) NOT NULL,
       employer_id INT UNSIGNED NOT NULL,
       company_name VARCHAR(80) NOT NULL,
       description TEXT NOT NULL,
       town VARCHAR(30) NOT NULL,
       county VARCHAR(30) NOT NULL,
       contact_name VARCHAR(40) NOT NULL,
       contact_phone VARCHAR(11) NOT NULL,
       contact_email VARCHAR(80) NOT NULL,
       salary SMALLINT(5) UNSIGNED NOT NULL,
       confirm TINYINT(1) UNSIGNED NOT NULL default 0,
       posted_time TIMESTAMP NOT NULL,
       deadline INT(10) UNSIGNED NOT NULL,
       job_status SET('open', 'closed') NOT NULL,  
       employer_paid SET('yes', 'no') NOT NULL,
       PRIMARY KEY (job_id),
       FULLTEXT (job_title, description)
    ) ENGINE = MYISAM;
    
    
    -- Jobs users applied
    DROP TABLE IF EXISTS jobs_applied;
    CREATE TABLE jobs_applied (
       jobs_applied_id INT(10) UNSIGNED NOT NULL auto_increment,
       user_id INT UNSIGNED NOT NULL,
       cv_id INT(10) UNSIGNED NOT NULL,
       cv_name VARCHAR(60) NOT NULL,
       job_id INT(10) unsigned NOT NULL,
       job_title VARCHAR(30) NOT NULL,
       company_id INT(10) unsigned NOT NULL,
       company_name VARCHAR(80) NOT NULL,
       applied_time TIMESTAMP NOT NULL,
       PRIMARY KEY (jobs_applied_id)
    ) ENGINE = INNODB;
    
    
    -- Reports table, containing info to produce site reports
    DROP TABLE IF EXISTS reports;
    CREATE TABLE reports (
       report_id INT(10) UNSIGNED NOT NULL auto_increment,
       user_id INT UNSIGNED NOT NULL,
       employer_id INT UNSIGNED NOT NULL,
       job_id INT(10) unsigned NOT NULL,
       job_title VARCHAR(30) NOT NULL,
       job_posttime TIMESTAMP NOT NULL,
       content VARCHAR(250) NOT NULL,
       report_time INT(10) UNSIGNED NOT NULL,
       PRIMARY KEY (report_id)
    ) ENGINE = INNODB;
    Some people say the tables I designed are exactly what I must not do in a relational database because they are full of duplicates. I don't get it. Anyone take a look at my design please?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by generalcc View Post
    Some people say the tables I designed are exactly what I must not do in a relational database because they are full of duplicates. I don't get it.
    exactly what you should not do? pshhhh, hyperbole

    full of duplicates? yawn

    your design is fine

    i might use a supertype/subtype structure to "remove" the "duplicate" data, but you'll be okay with what you have

    do you know about foreign keys? you should define some

    defining foreign keys properly is an exercise requiring rigour but it invariably is worth the effort

    p.s. good luck fitting CV data into 80 bytes

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast GhostGambler's Avatar
    Join Date
    Apr 2007
    Location
    Germany, NRW
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would merge all three user tables into one table and create e.g. a "type" column. You have to be more careful with your data in this case, but this also offers you more flexibility and potentially saves you some double code work. Just think of logging in a user with your structure: Create three login forms? Or query three tables? I would not want to do either of those.

    Nevertheless for your (up till now) little application this might not make a huge difference.


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
  •