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

Databases
1

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.

-- 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

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

:slight_smile:

3

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.