The Perfect Job (part 1) - The Five Rs

(Page 5 of 11 )

The four tables you've seen above will enable me to build the pages need to list open jobs, together with their descriptions. However, once the user has decided to apply for a job, the data he enters needs to be stored somewhere. Consequently, I've created some tables to store this information.

Note that the information I plan to request in the job application form can broadly be broken down into five sections (personal information, education, work history, skills and references), and so the tables I've constructed follow that breakdown too.

#
# Table structure for table 'r_user'
DROP TABLE IF EXISTS r_user;
CREATE TABLE r_user (
rid tinyint(3) unsigned NOT NULL auto_increment,
jcode varchar(10) NOT NULL,
fname varchar(255) NOT NULL,
lname varchar(255) NOT NULL,
dob date DEFAULT '0000-00-00' NOT NULL,
addr1 varchar(255) NOT NULL,
addr2 varchar(255),
city varchar(255) NOT NULL,
state varchar(255) NOT NULL,
zip varchar(10) NOT NULL,
fk_country tinyint(3) unsigned DEFAULT '0' NOT NULL,
phone varchar(25) NOT NULL,
email varchar(255) NOT NULL,
url varchar(255),
relo tinyint(4) DEFAULT '0' NOT NULL,
posted date DEFAULT '0000-00-00' NOT NULL,
PRIMARY KEY (rid),
KEY jcode (jcode),
KEY rid (rid)
);
#
# rid - unique identifier for each application/resume, used to reference it
throughout the application
# jcode - job this application is for
# fname - applicant's first name
# lname - applicant's last name
# dob - applicant's date of birth
# addr1 - applicant's address
# add2 - applicant's address
# city - applicant's city
# state- applicant's state
# zip - applicant's zip code
# fk_country - applicant's country; foreign key to "country" table
# phone - applicant's phone number
# email - applicant's email address
# url - applicant's Web site
# relo - whether applicant is willing to relocate
# posted - date application was posted
#

The "r_user" table holds the applicant's personal information, and contains one record per application; the "rid" field serves as a unique identifier per application.

#
# Table structure for table 'r_education'
#
DROP TABLE IF EXISTS r_education;
CREATE TABLE r_education (
rid tinyint(3) unsigned DEFAULT '0' NOT NULL,
institute varchar(255) NOT NULL,
fk_degree tinyint(3) unsigned DEFAULT '0' NOT NULL,
fk_subject tinyint(3) unsigned DEFAULT '0' NOT NULL,
year year(4) DEFAULT '0000' NOT NULL,
KEY fk_degree (fk_degree),
KEY fk_subject (fk_subject),
KEY rid (rid)
);
#
# rid - which application is this information for?
# institute - name of educational institution
# fk_institute - degree type; foreign key to "degree" table
# fk_subject - degree subject; foreign key to "subject" table
# year - degree obtained in which year?
#
#
# Table structure for table 'r_employment'
#
DROP TABLE IF EXISTS r_employment;
CREATE TABLE r_employment (
rid tinyint(3) unsigned DEFAULT '0' NOT NULL,
employer varchar(255) NOT NULL,
fk_industry tinyint(3) unsigned DEFAULT '0' NOT NULL,
start_year year(4) DEFAULT '0000' NOT NULL,
end_year year(4) DEFAULT '0000' NOT NULL,
responsibilities text NOT NULL,
KEY rid (rid)
);
#
# rid - which application is this information for?
# employer - name of employer
# fk_industry - employer's industry; foreign key to "industry" table
# start_year - started work in...?
# end_year - ended work in...?
# responsibilities - free-form description of job responsibilities at this
workplace
#
#
# Table structure for table 'r_skill'
#
DROP TABLE IF EXISTS r_skill;
CREATE TABLE r_skill (
rid tinyint(3) unsigned DEFAULT '0' NOT NULL,
skill varchar(255) NOT NULL,
experience tinyint(3) unsigned DEFAULT '0' NOT NULL,
KEY skill (skill),
KEY experience (experience),
KEY rid (rid)
);
#
# rid - which application is this information for?
# skill - name of skill
# experience - years experience in this skill
#
#
# Table structure for table 'r_reference'
#
DROP TABLE IF EXISTS r_reference;
CREATE TABLE r_reference (
rid tinyint(3) unsigned DEFAULT '0' NOT NULL,
name varchar(255) NOT NULL,
phone varchar(25) NOT NULL,
email varchar(255),
KEY rid (rid)
);
#
# rid - which application is this information for?
# name - reference's name
# phone - reference's phone number
# email - reference's email address
#

The "r_education", "r_employer", "r_skills" and "r_reference" tables hold education, work history, skills and references respectively. Note that these tables can contain more than one record per applicant (because an applicant might list several skills or references in a single application), with the records linked to each other via the unique "rid" field.

This article copyright Melonfire 2001. All rights reserved.

Next: Lucky Thirteen >>
 

More MySQL Articles
More By icarus, (c) Melonfire

Comments On: The Perfect Job (part 1)

>>> Be the FIRST to comment on this article!