Employee Contacts (Part 1) – Building the MySQL database.

1. Building the MySQL database.
2. Building a Data Abstraction Layer with PHP
3. Building the Data Access Layer with PHP
4. Building the Business Object Layer with PHP
5. Building the Presentation Layer with PHP & HTML

I remember back when I built my very first web application. The company I was working for hired me to be desktop support. But there were only 2 of us in the IT department. They had an intranet site where they would keep all the contact information for their employees. It also had all the details for every construction site they had worked on. The receptionist would enter the details into an excel spreadsheet then save it as an HTML file. It wasn’t pretty and it wasn’t very useful. I had never done any programming, database development, or even basic HTML, but I was determined to provide a more dynamic and user friendly environment. I was able to find a book on ASP that basically led you through building an entire ASP website that provided the basic functionality I was looking for. I learn best by seeing a real working example, so this made it extremely easy for me to pick up ASP and to get the site up in only a matter of a couple of weeks.

I will be doing a series where we will build an entire PHP/MySQL web application. The series should go as follows.

Part 1 – Building the Employee Management Database in MySQL.
Part 2 – Develop the Data Abstraction Layer.
Part 3 – Build the Data Object Layer.
Part 4 – Build the Business Object Layer.
Part 5 – Build the Presentation Layer.

Since the series is meant for beginners, I am not going to be using PEAR. Also, some may say that for a beginner, all these layers are absurd. But I think, even from my own experience, that it is best to start out doing things the right way. Otherwise, you have to unlearn things in the future. This is a very basic example and I will be throwing comments in all along the way.

If you do not have MySQL installed, you can visit the MySQL website and download it.

Employee Management Schema

As you can see, we will have 4 database tables for our solution.

• Departments – Contains a list of all departments within our company.
• Locations – This is a list of all the cities where we have offices. If you are in a single office environment, this would not be needed. Unless you are like me and like to think ahead, expecting growth.
• Jobtitles – A list of all possible titles an employee can hold.
• Employees – This is just your basic employee information. You could consider adding phone, mobile, email, etc.

The first thing we want to do is create our database. I would suggest if you haven’t done so, that you install and configure phpMyAdmin. It is a web based database management tool for MySQL. Or if you have some other tool you prefer, use it and create a new database called ‘employeemanagement’. If you are doing it from the command line…
CREATE DATABASE `employeemanagement` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `employeemanagement`;
The first thing we do is create the database. Then we want to user the employeemanagement database for our next scripts. We also want to create a new user that only has access to this database.
This will create the new user in MySQL. IDENTIFIED is the password for empmgmt. For more information on creating MySQL users, you can reference the MySQL Reference Manual.
CREATE USER 'empmgmt'@'%' IDENTIFIED BY 'empmgmt';
Before we can use this account, we need to give at the appropriate permissions. We want to limit this user to only the employeemanagement database. The user empmgmt will have SELECT, INSERT, UPDATE, DELETE access only. To do that we issue the following command.
GRANT SELECT , INSERT , UPDATE , DELETE ON `employeemanagement` . * TO 'empmgmt'@'%';
We can now move on to building our tables. If you have not done so, be sure and switch to the employeemanagement database, else you will create these tables in whatever database you are in.

There are a few things I would like to point out before executing the scripts below. First, you could build these tables through phpMyAdmin or whatever tool you choose. I am just providing the scripts so that you can run them. Second, I am running MySQL 5.0, so depending on the version you are running, these scripts may need to be slightly modified. If you need help, please comment below and I will assist you the best I am able. Third, the auto_increment is to assure that I have unique records in each table. You will notice in the employees table that we have several foreign keys that each reference one of our tables. Finally, I am using MyISAM tables, but you could change this to be InnoDB if that is your requirement.
Departments
CREATE TABLE `departments` (
`department_id` int(10) unsigned NOT NULL auto_increment,
`department_name` varchar(50) NOT NULL,
PRIMARY KEY (`department_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Locations
CREATE TABLE `locations` (
`location_id` int(10) unsigned NOT NULL auto_increment,
`location_name` varchar(50) NOT NULL,
PRIMARY KEY (`location_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Job Titles

CREATE TABLE `jobtitles` (
`jobtitle_id` int(10) unsigned NOT NULL auto_increment,
`jobtitle` varchar(50) NOT NULL,
PRIMARY KEY (`jobtitle_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Employees
CREATE TABLE `employees` (
`employee_id` int(10) unsigned NOT NULL auto_increment,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`department_id` int(10) unsigned default NULL,
`jobtitle_id` int(10) unsigned default NULL,
`location_id` int(10) unsigned default NULL,
PRIMARY KEY (`employee_id`),
KEY `FK_employees_department_id` (`department_id`),
KEY `FK_employees_jobtitle_id` (`jobtitle_id`),
KEY `FK_employees_location_id` (`location_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Now we have our database built. Next time we will build the data abstraction layer. Please comment and let me know if this helps you or if you have any questions.