Home arrow MySQL arrow Page 5 - The Perfect Job (part 1)

The Five Rs - MySQL

Recruitment - the art of matching qualified applications to openpositions within an organization - is one of the most challenging tasks forany Human Resources department. However, powerful open-source tools likePHP and mySQL have made the process simpler, more efficient and moreeconomical than at any time in the past. This case study demonstrates how,by building a complete job listing and resume management system fromscratch.

TABLE OF CONTENTS:
  1. The Perfect Job (part 1)
  2. An Ideal World
  3. Entry Point
  4. Going To The Database
  5. The Five Rs
  6. Lucky Thirteen
  7. Building The Foundation
  8. The Devil Is In The Details
  9. Applying Yourself
  10. Testing Times
  11. Filing It All Away
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 4
June 28, 2001

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

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

 
 
>>> More MySQL Articles          >>> More By icarus, (c) Melonfire
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: