Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Time sheet, rates, date dependent

Author  Topic 

reamades
Starting Member

5 Posts

Posted - 2015-04-21 : 14:05:01
I am trying to figure out the best database design for the following;

I have individuals (stored in Agents table) that enter their billing or time worked each day for different projects. Now we have different rates that we pay based on the person and what the project is. And we also have to account for individuals getting pay cuts or raises over time. So....

I have the following tables;
Agents - table of each person
Projects - table of all projects we work
Rates - table with AgentID, ProjectID, StartDate, EndDate, Rate
Billing - table with Date, AgentID, ProjectID

So my current implementation a billing entry must be made by an agent, and I have to create a join to the rates table based on AgentID, ProjectID, and Date between StartDate and EndDate. This just seems incredibly ugly and all the billing data can be easily changed if a change were made to the rates. I'm assuming most time sheets calculate the total rate and store it in the table so it never changes unless someone specifically goes back to update the data? I'm just wondering if there is a better method for tracking this sort of data. Thanks

Kristen
Test

22859 Posts

Posted - 2015-04-22 : 07:00:21
quote:
Originally posted by reamades

I'm assuming most time sheets calculate the total rate and store it in the table so it never changes unless someone specifically goes back to update the data?



Yes, that's what I would consider "normal" too (actually I would store Hours/Minutes and Rate, rather than Total Amount, so that either Rate or Time could be manually adjusted if necessary.

Where a Rate applies per Project, and the Rate Card can change over Time, you need to have Start/End dates for the rates too.

(If the Rate goes up TODAY you might still have someone enter a Time Sheet for YESTERDAY. You probably would NOT have someone enter a timesheet for Last Year! ... but I suppose the rate could go up two or three times in a short interval, and it would be reasonable to enter New Rates which come into effect on Some Future Date).

You might also want to store, in the Billing record, the ID of the Rate used - it might help with debugging if the wrong rate is selected
Go to Top of Page
   

- Advertisement -