OK DB Wizzards

Here is what I need to do. I am writing a project tracking application for my department to track time by project. What is needed is how a each person spends each week on based on % of time dedicated to differant projects. So a user comes in sees a list of projects they are assigned to and allocates there time to each one so that it sums to 100. I also need to allow users to go forward in time to forcast there days available for project work. The idea is to have a dumby project that everyone is allocated 100% too by default. We have "administrative" projects such as vacation, holidays, training, etc that can be billed to also. Any suggestions on table structure? The one I am thinking about is as follows:


T_Week
----------
WeekID
StartDate
EndDate

T_PersonTime
--------------------
PersonTimeID
PersonID(fk)
ProjectID(fk)
WeekID(fk)
PctAllocation(int)


The week table would be built out for several years and would act as a timeline by which everyones Project allocations are referenced to. Then if a person worked on 4 projects one week they would have 4 entries in the PersonTime table with 25 in the PctAllocation field. Looking forward everyones time would be set to 100% allocation to the dumby "Available days" project. I think this is a whacked out way to do this and need some guidance.

 

hmmm, I don't anything about that shit

but keep in mind not 100% of all their time is chargable, and if there's mulit-tasking, then you'll want them to be able to charge multiple jobs at the same time...just a thought

 

Well 100% of time is chargeable to something. So for a week you might say 50% project x and 25% project y and 25% vacation or training or holiday. There are the billable projects and there are the admin projects that take up time but cant be billed to a business partner.

 

How in depth do you want to get on this?

Actually you are looking at two different types of billable hours. Indirect time vs direct time. Indirect time is what you are calling administrative projects, vacation, holiday, sick etc. Direct time is the time actually spent working on a project and is billable to that project directly.


Generally you want to bill time to a project for the actual amount time that is worked on it. Have the employees clock into and out of projects then credit that time to the project multiplied by the amount that the employee gets paid per hour or what you charge the job per hour for the employees time. If an employee can work on multiple projects at the same time the you have to keep track of when they clocked into a job, when they clocked into the second, and the third and so forth, determine the percentage of the time clocked into each job and determine how much goes to each one.

Indirect time, if you want to bill to jobs as a percentage is determined by figuring out the total time worked directly, the percentage that each job commanded of that total time and then pro-rate the indirect time to each job proportionately. Usually though this is considered to be overhead and distributed when you determine the billable rate per hour.


A lot to absorb but it can all be simplified to suit your purposes. A lot of what I deal with on a daily basis is writing custom applications to track labor and materials to specific jobs (bill jobs for actual labor and materials used).

If you want, feel free to PM me and I'll help you as much as possible. I even have a couple of applications that you may want to look and I just happen to know where there is a web based labor tacking application that you can go and play around with a bit.

 

Just use Microsoft Project

 

Project rocks, and the web components are awesome.

But, to answer the question on table design:

Having a week table doesn't make sense to me. You want a table designed for each object type. From what I've heard so far:
People, Projects, Customers, and Time. Time holds all of the data for a year, and you would archive that table each year (at that point changing the name to the year it was for, keeping the current time table called "current_time" or something) and start clean, just to keep indexes smaller, backups and queries faster, etc.

People fields:
People ID (key field)
First Name
Last Name
Title
possibly tons more

Projects fields:
Project ID (key field)
Name
Description
Customer ID
Default Bill Rate

Customers fields:
Customer ID (key field)
Name
Description
Address
possibly tons more

Current_Time fields:
Time ID (key field)
Week (date field, track by Friday/Saturday end of week date)
Person ID
Project ID
Time %

With those fields, you could easily do queries with joins to show all projects for customer x, all projects where employee x has submitted time, generate a total time report for project x for any given time period, etc. If you included bill rate information in there, you could generate statements for customers. For internal projects, you'd be able to mark a project with a customer ID that = your company. To add expenses to this, you'd just need an expense table that looks similar to the Current_Time table. If you had external systems that track details of some of these items (like a customer contact DB or an HR system or the like), you'd want to be able to include fields to allow linking your tables with these external sources (e.g. put the HR employee ID in your people table as another field - don't use it as your key field, as you might include business partners in your billing system that aren't in that external HR system...).

Hope that helps.

 

You can hook it up to a SQL database, too.

 

Oh well, I do things oddly but it works great.