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.
| 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, ProjectIDSo 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  |
 |
|
|
|
|
|
|
|
Time sheet, rates, date dependent