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

Going To The Database - 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
Having written down the requirements, it becomes much easier to begin designing the architecture of the system. The first (and most important) part of this design process is database design, in which I will be designing the tables to hold application data.

This is a good time for you to download the source code, so that you can refer to it throughout this article (you will need a Web server capable of running PHP and a mySQL database).

job.zip

I plan to store job listings in a single "listing" table.

# # Table structure for table 'listing' # DROP TABLE IF EXISTS listing; CREATE TABLE listing ( jcode varchar(10) NOT NULL, designation varchar(255) NOT NULL, responsibilities text NOT NULL, qualifications text NOT NULL, cname varchar(255) NOT NULL, cmail varchar(255) NOT NULL, posted date DEFAULT '0000-00-00' NOT NULL, fk_department tinyint(3) unsigned DEFAULT '0' NOT NULL, fk_location tinyint(3) unsigned DEFAULT '0' NOT NULL, fk_salary tinyint(3) unsigned DEFAULT '0' NOT NULL, PRIMARY KEY (jcode), KEY jcode (jcode) ); # # jcode - unique identifier for each job listing # designation - job designation # responsibilities - job responsibilities # qualifications - job qualifications # cname - name of person posting job # cmail - email address of person posting job # fk_department - foreign key - which department is this job in? # fk_location - foreign key - which city is this job in? (for multi-location organizations) # fk_salary - foreign key - what is the expected compensation range for this job? #
Let's fill this up with a couple of dummy entries.

# # Dumping data for table 'listing'#INSERT INTO listing (jcode, designation, responsibilities, qualifications,cname, cmail, posted, fk_department, fk_location, fk_salary) VALUES ('X5436', 'Senior Web Developer', 'Applicant will be responsible fordeveloping Web applications and executing Web-related projects forcorporate customers. ', 'Applicant should be familiar with scriptinglanguages (PHP and Perl), databases (mySQL, PostgreSQL). Applicant shouldbe comfortable with both Windows and *NIX operating system. Applicant willalso be required to demonstrate a thorough knowledge of software design andengineering principles.', 'Roger Rabbit', 'roger@site.com', '2001-05-22','3', '4', '1');INSERT INTO listing (jcode, designation, responsibilities, qualifications,cname, cmail, posted, fk_department, fk_location, fk_salary) VALUES ('KA6547', 'Project Manager', 'Applicant will be responsible for managingprojects within the organization. Responsibilities include developingproject plans and schedules, tracking project progress, communicating withthe customer, and ensuring that deadlines and deliveries are met.','Applicant should be familiar with office applications like Word, Excel,Powerpoint and Project. Applicant should have prior experience with projectmanagement tasks, and must bring enthusiasm and professionalism to thepost.', 'Bugs Bunny', 'a@a.com', '2001-04-05', '4', '5', '11');
As you can say, this table references information in three other tables via foreign keys - let's take a look at those next, together with sample entries for each.

# # Table structure for table 'location' # DROP TABLE IF EXISTS location; CREATE TABLE location ( id tinyint(3) unsigned NOT NULL auto_increment, location varchar(255) NOT NULL, PRIMARY KEY (id) ); # # id - unique record identifier # location - name of city where applicant will be posted # # Dumping data for table 'location' # INSERT INTO location (id, location) VALUES ( '1', 'New York'); INSERT INTO location (id, location) VALUES ( '2', 'London'); INSERT INTO location (id, location) VALUES ( '3', 'Paris'); INSERT INTO location (id, location) VALUES ( '4', 'Tokyo'); INSERT INTO location (id, location) VALUES ( '5', 'Bombay'); # # Table structure for table 'department' # DROP TABLE IF EXISTS department; CREATE TABLE department ( id tinyint(3) unsigned NOT NULL auto_increment, department varchar(255) NOT NULL, PRIMARY KEY (id) ); # # id - unique record identifier # department - name of department # # Dumping data for table 'department' # INSERT INTO department (id, department) VALUES ( '1', 'Human Resources'); INSERT INTO department (id, department) VALUES ( '2', 'Accounting'); INSERT INTO department (id, department) VALUES ( '3', 'Engineering'); INSERT INTO department (id, department) VALUES ( '4', 'Design'); INSERT INTO department (id, department) VALUES ( '5', 'Administration'); # # Table structure for table 'salary' # DROP TABLE IF EXISTS salary; CREATE TABLE salary ( id tinyint(3) unsigned NOT NULL auto_increment, salary varchar(255) NOT NULL, PRIMARY KEY (id) ); # # id - unique record identifier # salary - salary range # # # Dumping data for table 'salary' # INSERT INTO salary (id, salary) VALUES ( '1', 'Not specified'); INSERT INTO salary (id, salary) VALUES ( '2', '< USD 20,000'); INSERT INTO salary (id, salary) VALUES ( '3', 'USD 20,000-29,900'); INSERT INTO salary (id, salary) VALUES ( '4', 'USD 30,000-39,900'); INSERT INTO salary (id, salary) VALUES ( '5', 'USD 40,000-49,900'); INSERT INTO salary (id, salary) VALUES ( '6', 'USD 50,000-59,900'); INSERT INTO salary (id, salary) VALUES ( '7', 'USD 60,000-69,900'); INSERT INTO salary (id, salary) VALUES ( '8', 'USD 70,000-79,900'); INSERT INTO salary (id, salary) VALUES ( '9', 'USD 80,000-89,900'); INSERT INTO salary (id, salary) VALUES ( '10', 'USD 90,000-99,900'); INSERT INTO salary (id, salary) VALUES ( '11', '> USD 100,000');
These are very simple tables, each having a primary key (id) and a corresponding value. In case you're wondering why I've split these items into separate tables, rather than including them all in the "listing" table, or even hard-coding them into the end application, the reason is very simple: I want to make it easier for an administrator to add and edit these values.

By breaking them into separate tables, an administrator who wants to customize the application (for example, change the number and name of the job locations, or edit the various department names) can do so without having to mess about with the program code. This is part of a process known as "normalization", and it's very important when designing a database with two or more tables (links to some good articles on normalization appear at the end of this article)

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: