The Perfect Job (part 1) - Going To The Database

(Page 4 of 11 )

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.

Next: The Five Rs >>
 

More MySQL Articles
More By icarus, (c) Melonfire

Comments On: The Perfect Job (part 1)

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