Logo         Community
  Trog
Services
The Company
Community
Columns
Your Account
Contact Us
 
 
The Perfect Job (part 1)
Build a complete job listing and resume management system with PHP and mySQL.

| Going To The Database |

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

Download now!

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 for developing Web applications and executing Web-related projects for corporate customers. ', 'Applicant should be familiar with scripting languages (PHP and Perl), databases (mySQL, PostgreSQL). Applicant should be comfortable with both Windows and *NIX operating system. Applicant will also be required to demonstrate a thorough knowledge of software design and engineering 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 managing projects within the organization. Responsibilities include developing project plans and schedules, tracking project progress, communicating with the 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 project management tasks, and must bring enthusiasm and professionalism to the post.', '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)


How to do Everything with PHP & MySQL
How to do Everything with PHP & MySQL, the best-selling book by Melonfire, explains how to take full advantage of PHP's built-in support for MySQL and link the results of database queries to Web pages. You'll get full details on PHP programming and MySQL database development, and then you'll learn to use these two cutting-edge technologies together. Easy-to-follow sample applications include a PHP online shopping cart, a MySQL order tracking system, and a PHP/MySQL news publishing system.

Read more, or grab your copy now!


previous page more like this  print this article  next page
 
Search...
 
In trog...
Logging With PHP
Building A Quick-And-Dirty PHP/MySQL Publishing System
Output Buffering With PHP
Date/Time Processing With PHP
Creating Web Calendars With The PEAR Calendar Class
more...
 
In the hitg report...
Crime Scenes
Animal Attraction
Lord Of The Strings
more...
 
In boombox...
Patience - George Michael
Think Tank - Blur
My Private Nation - Train
more...
 
In colophon...
Hostage - Robert Crais
The Dead Heart - Douglas Kennedy
Right As Rain - George Pelecanos
more...
 
In cut!...
American Chai
The Core
Possession
more...
 
Find out how you can use this article on your own Web site!


Copyright © 1998-2018 Melonfire. All rights reserved
Terms and Conditions | Feedback