Time is Money (part 1) - So Many Tables, So Little Time
( Page 4 of 9 )

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

time.zip

First, I need a table to hold the list of active projects - let's call that the "projects" table:

#
# Table structure for table 'projects'
#

DROP TABLE IF EXISTS projects;
CREATE TABLE projects (
   pid smallint(5) unsigned NOT NULL auto_increment,
   pname varchar(40) NOT NULL,
   pdesc text NOT NULL,
   PRIMARY KEY (pid)
);

#
# pid - unique project ID
# pname - project name
# pdesc - project description
#

Here are some dummy entries:

#
# Dumping data for table 'projects'
#

INSERT INTO projects (pid, pname, pdesc) VALUES ( '1', 'XTech.com',
'Interface design and development of the XTech corporate site');

INSERT INTO projects (pid, pname, pdesc) VALUES ( '2', 'Melonfire.com',
'Weekly updates of the Melonfire content catalog');

INSERT INTO projects (pid, pname, pdesc) VALUES ( '3', 'VideoMoz (Linux)',
'Software development of the VideoMoz animation and graphics library for
the Linux platform');

INSERT INTO projects (pid, pname, pdesc) VALUES ( '4', 'VideoMoz
(Windows)', 'Software development of the VideoMoz.dll animation library for
Windows 98, 2000 and XP');

INSERT INTO projects (pid, pname, pdesc) VALUES ( '5', 'NamelessCorp
AddBook', 'Design and development of address book application for the
NamelessCorp intranet');

INSERT INTO projects (pid, pname, pdesc) VALUES ( '6', 'NamelessCorp
invDB', 'Design of invoicing database for NamelessCorp Accounting
department');

I also need a table to hold the list of standard, company-defined, billable tasks - the "tasks" table:

#
# Table structure for table 'tasks'
#

DROP TABLE IF EXISTS tasks;
CREATE TABLE tasks (
   tid tinyint(3) unsigned NOT NULL auto_increment,
   tname varchar(40) NOT NULL,
   tdesc text NOT NULL,
   PRIMARY KEY (tid)
);

#
# tid - unique task ID
# tname - task name
# tdesc - task description
#

As you can see, it's almost identical to the "projects" table - except, obviously, for the data it contains:

#
# Dumping data for table 'tasks'
#

INSERT INTO tasks (tid, tname, tdesc) VALUES ( '1', 'Design', 'Interface
design, software architecture design, database schema design');

INSERT INTO tasks (tid, tname, tdesc) VALUES ( '2', 'Development',
'Development of software code, standard libraries and functions');

INSERT INTO tasks (tid, tname, tdesc) VALUES ( '3', 'System Test',
'Verifying software functionality, creating test cases, writing bug
reports');

INSERT INTO tasks (tid, tname, tdesc) VALUES ( '4', 'Technical Support',
'Supporting customers (phone/fax/email/online/site), assisting in software
(un)installation, answering user questions');

INSERT INTO tasks (tid, tname, tdesc) VALUES ( '5', 'Documentation',
'Creating technical manuals, software data specifications, product catalogs
and marketing literature');

INSERT INTO tasks (tid, tname, tdesc) VALUES ( '6', 'Training', 'Executing
training programs for customers (online/site)');

INSERT INTO tasks (tid, tname, tdesc) VALUES ( '7', 'Research',
'Understanding new technology, experimenting with new applications and
tools');

Next, we need a table to hold the list of users allowed to use the applications, together with their passwords. Each user is assigned a unique ID, which will be used throughout the application

#
# Table structure for table 'users'
#

DROP TABLE IF EXISTS users;
CREATE TABLE users (
   uid tinyint(3) unsigned NOT NULL auto_increment,
   uname varchar(255) NOT NULL,
   upass varchar(255) NOT NULL,
   uperms tinyint(4) DEFAULT '0' NOT NULL,
   PRIMARY KEY (uid),
   UNIQUE uname (uname)
);

#
# uid - unique user ID
# uname - user's log-in name
# upass - user's password
# uperms - user's permission level (user or admin)
#

You'll notice the last column in this table contains a permission level for each user. I need this in order to categorize users into two types - regular users or administrators. Only administrators should have the ability to generate summary reports for user activity.

Here is some seed data for this table, which identifies users "joe" and "sherry" to be administrators (in case you're wondering, the passwords in this dummy data are the same as the corresponding username - they've just been encrypted with mySQL's password() function)

#
# Dumping data for table 'users'
#

INSERT INTO users (uid, uname, upass, uperms) VALUES ( '1', 'john',
'2ca0ede551581d29', '0');

INSERT INTO users (uid, uname, upass, uperms) VALUES ( '2', 'joe',
'7b57f28428847751', '1');

INSERT INTO users (uid, uname, upass, uperms) VALUES ( '3', 'vanessa',
'24b841bb4fef7fda', '0');

INSERT INTO users (uid, uname, upass, uperms) VALUES ( '4', 'sherry',
'12ee5cff47618c7a', '1');

It should be noted that these three tables would typically need to be controlled by an administrator, who would be in charge of adding (and removing) new users and categories to the system.

In case you're wondering why I've split these items into separate tables, rather than including them all in a single table, or even hard-coding them into the 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, add new projects, or edit the various tasks) 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)

Finally, we need a table to hold the work hours entered by individual employees, and map these hours to a specific project and task - the "log" table:

#
# Table structure for table 'log'
#

DROP TABLE IF EXISTS log;
CREATE TABLE log (
   lid tinyint(3) unsigned NOT NULL auto_increment,
   pid tinyint(3) unsigned DEFAULT '0' NOT NULL,
   tid tinyint(3) unsigned DEFAULT '0' NOT NULL,
   uid tinyint(3) unsigned DEFAULT '0' NOT NULL,
   hours float unsigned DEFAULT '0' NOT NULL,
   date date DEFAULT '0000-00-00' NOT NULL,
   PRIMARY KEY (lid)
);

#
# lid - unique record ID
# pid - project ID (foreign key to "projects" table)
# tid - task ID (foreign key to "tasks" table)
# uid - user ID (foreign key to "users" table)
# hours - hours worked
# date - date on which hours worked
#

Entries to this table will be made by individual users through the application, and the data in this table will eventually be used to generate summary reports.

>>> More PHP Articles          >>> More By The Disenchanted Developer, (c) Melonfire