Architecture billable-hour tracking

[gene2152]

I work for an architecture firm, and am setting up a
database to track hours worked on various projects.

The three first tables are fairly simple. Each
project has many employees, and each employee works on
many projects. So I'll have these three tables:

tblProjects
tblEmployees
tblProjectEmployeeBridge

But when I get to the hours worked I get confused. I
will need to replicate reports from a 15-year-old
database which has crashed. Those reports tracked
hours worked (projected and actual) by month, then
week. A typical line from these reports showed
something like this.

They way we want to track the hours is as follows:

Month 1 (the current month), by week (1 to 5 weeks), projected and
actual hours for each week);
Month 2 (the month after the current month) first week projected, then
total projected for month;
Month 3 (projected total for the month only)

The reports are run every monday, and the information will be new each
month. In other words this is really just used for forecasting the
weeks in the current and next two months.

Any ideas on how to set up the tables for this, and how they should be
related to the Employee and Projects tables?
thanks

[Pieter Linden]

Almost directly out of Elmasri's book on databases...

Employee---(1,M)----WorksOn---(M,1)----Project

CREATE TABLE Employee(
EmployeeID Long Integer PRIMARY KEY,
FirstName Text(25) NOT NULL,
LastName Text(25) NOT NULL,
.....
);

CREATE TABLE Project(
ProjectID Long Integer PRIMARY KEY,
ProjectName Text(25) NOT NULL,
.....
UNIQUE (ProjectName)
);

CREATE TABLE WorksOn(
EmployeeID Long Integer NOT NULL,
ProjectID Long Integer NOT NULL,
HoursWorked Number
FOREIGN KEY (EmployeeID) REFERENCES Employee.EmployeeID,
(ProjectID) REFERENCES Project.ProjectID)
):

All the HoursWorked info goes into the WorksOn table. It's just WHO
worked on WHAT for HOW LONG? (Or you could put in start time/stop time
stuff...)

Presumably, your projected hours would go into a field somewhere.
Your actuals would be sums of HoursWorked. If you use actual
dates/times, just use DateDiff() to get the difference in minutes and
then use integer division to deal with hours and all that.

Looks like your reports are just filtering for current and future
months... so you'd just filter your reports when you open them.

[gene2152]

Hey,

thanks for the tips. I'm reviewing your advice now, but actually I was
able to come up with a rough logical schema and ERD while at work
today. They can be viewed at:

http://www.geocities.com/gene2152

Please feel free to take a look and let me know if you see any glaring
mistakes. The funny parts that gave me problems in planning were how
to track the projected AND actual hours (I settled on a HourStatus
entity). I also settled on using the Month attribute, which is a
Month/Year figure, and the Period entity instead of week (we have 4-5
weeks per month plus a weird Projected Total figure we need to track).

thanks

[clintonG]

You should know about and review the many data models available
at Database Answers [1] which seems to be a one of a kind resource.
--
<%= Clinton Gallagher, "Twice the Results -- Half the Cost"
Architectural & e-Business Consulting -- Software Development
NET cs*********@REMOVETHISTEXTmetromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/

[1] http://www.databaseanswers.com/

"gene2152" <ge******@yahoo.com> wrote in message
news:7e*************************@posting.google.co m...

Hey,

thanks for the tips. I'm reviewing your advice now, but actually I was
able to come up with a rough logical schema and ERD while at work
today. They can be viewed at:

http://www.geocities.com/gene2152

Please feel free to take a look and let me know if you see any glaring
mistakes. The funny parts that gave me problems in planning were how
to track the projected AND actual hours (I settled on a HourStatus
entity). I also settled on using the Month attribute, which is a
Month/Year figure, and the Period entity instead of week (we have 4-5
weeks per month plus a weird Projected Total figure we need to track).

thanks

[Pieter Linden]

I definitely agree that you should look around for schemas... this is
a pretty common problem.... a few other thoughts:

CREATE TABLE Employee(
EmployeeID Long Integer PRIMARY KEY,
FirstName Text(25) NOT NULL,
LastName Text(25) NOT NULL,
HrlyRate Currency
.....
);

CREATE TABLE Project(
ProjectID Long Integer PRIMARY KEY,
ProjectName Text(25) NOT NULL,
EstBudget Currency,
.....
UNIQUE (ProjectName)
);

CREATE TABLE WorksOn(
EmployeeID Long Integer NOT NULL,
ProjectID Long Integer NOT NULL,
HoursWorked Number
FOREIGN KEY (EmployeeID) REFERENCES Employee.EmployeeID,
(ProjectID) REFERENCES Project.ProjectID)
):

Then you could just do the projected vs the actuals by grouping by
project and totaling HoursWorked*Rate for each employee on the
project...

[Pieter Linden]

I definitely agree that you should look around for schemas... this is
a pretty common problem.... a few other thoughts:

CREATE TABLE Employee(
EmployeeID Long Integer PRIMARY KEY,
FirstName Text(25) NOT NULL,
LastName Text(25) NOT NULL,
HrlyRate Currency
.....
);

CREATE TABLE Project(
ProjectID Long Integer PRIMARY KEY,
ProjectName Text(25) NOT NULL,
EstBudget Currency,
.....
UNIQUE (ProjectName)
);

CREATE TABLE WorksOn(
EmployeeID Long Integer NOT NULL,
ProjectID Long Integer NOT NULL,
HoursWorked Number
FOREIGN KEY (EmployeeID) REFERENCES Employee.EmployeeID,
(ProjectID) REFERENCES Project.ProjectID)
):

Then you could just do the projected vs the actuals by grouping by
project and totaling HoursWorked*Rate for each employee on the
project...