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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Time sheet, rates, date dependent
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

reamades
Starting Member

5 Posts

Posted - 04/21/2015 :  14:05:01  Show Profile  Reply with Quote
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

United Kingdom
22859 Posts

Posted - 04/22/2015 :  07:00:21  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000