Sql-server – Time sheet, rates, date dependent

database-designsql server

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

Best Answer

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.

Related Solutions

Sql-server – Select query according to date

You simply need to think about the date range slightly differently. An event falls in your given range if the start date is prior to the end of your reporting period, and the end date is after the beginning of your reporting period:

Select * from tbl_Events
where StartDate <= @end
    AND EndDate >= @start;

For example:

CREATE TABLE MyTestDates
(
    MyTestDateID INT NOT NULL PRIMARY KEY CLUSTERED CONSTRAINT PK_MyTestDates IDENTITY(1,1)
    , StartDate DATETIME
    , EndDate DATETIME
    , EventName NVARCHAR(255)
);

INSERT INTO MyTestDates (StartDate, EndDate, EventName) 
VALUES ('2012-01-27', '2012-01-30', 'Test Event 1');

INSERT INTO MyTestDates (StartDate, EndDate, EventName) 
VALUES ('2012-01-27', '2012-02-28', 'Test Event 2');

INSERT INTO MyTestDates (StartDate, EndDate, EventName) 
VALUES ('2011-01-01', '2012-03-22', 'Test Event 3');

DECLARE @FromDate DATETIME;
DECLARE @ToDate DATETIME;

SET @FromDate = '2012-01-01';
SET @ToDate = '2012-02-01'
SELECT @FromDate AS ReportFrom, @ToDate AS ReportTo, * 
FROM MyTestDates
WHERE StartDate <= @ToDate
    AND EndDate >= @FromDate;

SET @FromDate = '2012-01-28';
SET @ToDate = '2012-01-29'
SELECT @FromDate AS ReportFrom, @ToDate AS ReportTo, * 
FROM MyTestDates
WHERE StartDate <= @ToDate
    AND EndDate >= @FromDate;

SET @FromDate = '2010-01-01';
SET @ToDate = '2012-05-15'
SELECT @FromDate AS ReportFrom, @ToDate AS ReportTo, * 
FROM MyTestDates
WHERE StartDate <= @ToDate
    AND EndDate >= @FromDate;

DROP TABLE MyTestDates;

The three SELECT statements return all events in this example.

Database design with multiple tables

Making one large table would be a mistake: you'll have a table with lots of NULL values and it would soon evolve into a maintenance nightmare. And about saving the data as XML or JSON -- (slaps your wrist) that's for even considering it.

Every Resulttype should get its own table, each of these tables has a primary key over an ID column and (this is the trick!) all of these IDs are taken from the same sequence!

Then you only need a table with the columns projectid, resultid (and optionally resulttype). Since you will look for the primary key, even searching over all tables can be done fast.

Best Answer

Related Solutions

Sql-server – Select query according to date

Database design with multiple tables

Related Question