Home arrow PHP arrow Page 4 - Time is Money (part 1)

So Many Tables, So Little Time - PHP

For consultancies that bill on an hourly basis - lawyers,accountants et al - time tracking is a critical part of the billingprocess. For small- and medium-size organizations, resource tracking,allocation and analysis is essential for business efficiency and planning.This article addresses both requirements by teaching you how to build atimesheet system to track and analyze work hours with PHP and MySQL.

TABLE OF CONTENTS:
  1. Time is Money (part 1)
  2. Up A Creek
  3. Bills, Bills, Bills
  4. So Many Tables, So Little Time
  5. Open Sesame
  6. The Lazy Programmer Strikes Again
  7. Today's Menu
  8. Too Much Information
  9. Time For Bed
By: The Disenchanted Developer, (c) Melonfire
Rating: starstarstarstarstar / 5
October 22, 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).

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
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: