Time sheet, rates, date dependent

Question

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

Answer 1

I take it that your main concern is tracking the changing rates and charging the prevailing rate when work was performed. That's not really a problem.

There is a pattern I call Temporal Normal Form. It's a way to track changing data using normalization -- a pattern we all know and love.

So the Rates table will be normalized according to static data:

create table Rates_S(
    ID        int identity,
    AgentID   int not null,
    ProjectID int not null,
    constraint PK_Rates_S primary key( ID )
);

and varying data:

create table Rates_V(
    RateID    int not null,
    Effective date not null,
    Rate      number( 8, 2 ) not null,
    constraint PK_Rates_V primary key( RateID, Effective ),
    constraint FK_Rates_V_Rate_S foreign key( RateID )
        references Rates_S( ID )
);

Notice there is no EndDate field. The rate, once started, continues until changed. This eliminates Row Spanning Dependencies, where different fields in different rows must remain in synch.

So now let's see how much a billing entry will cost the project. We're looking for the rate that was in effect on the date the hours are logged:

select  b.ProjectID, b.AgentID, b.Date, b.Hours * v.Rate as Charge
from    Billing   b
join    Rates_S   s
    on  s.ProjectID = b.ProjectID
    and s.AgentID   = b.AgentID
join    Rates_V   v
    on  v.RateID    = s.ID
    and v.Effective =(
        select  Max( Effective )
        from    Rates_V
        where   RateID = v.RateID
            and Effective <= v.Effective );

Notice I've added how much time is being billed to the Billing table.

This looks like a lot to get one rate, but it is a pattern and once you write it a coupla hundred times, it will sink in. It is also quite efficient.

Answer 2

I suppose you don't change rates more often than once a day... and that there's a day in a week or in a month when all data for the past week or month is considered final. if that is the case, if I were you, I would do a calculation on that day for the elapsed period on a per-day basis for each agent and project, and store the results with all the information needed to repeat that calculation into a separate table. That table would have (AgentID, ProjectID, Date) as the primary key and TimeWorked and RateCharged (the actual value of the rate, not RateID from Rates table) columns to enable the calculation of the total amount for that agent, project and day. You can also add a DateTimeCalculated column to store the information when the entry in that table was made.

Answer 3

If the rate can change during a project I interpret that as meaning that until the rate changed the charge was based on the old rate and after the rate was charged the new rate is charged. If you allow this you need a separate table with a start, and end, date, AgentID and rate for that period.

Answer 4

Microsoft SQL Server 2016 and Azure SQL database now support temporal tables, a feature that is designed to let you query and compare data for specific points in time. Changesets basically. It's also extremely simple to set up and use within existing databases.