Table design for payments

Question

I have a table that holds data for a person who is on a project. The table has a Start date field a nullable End date field. So, a person is on a project from Start to End.

At the moment, they are always billable to the project. But I now have a new requirement that, for a period, they can be non-billable, yet, still working on the project. So, they have been assigned to the project, and are working, but for some reason, the client isn't billed for part of the period they are assigned, or maybe billed at a lower rate.

My idea is to have an exclusion type table, linked to the person assignment table which would have a start date, and end date, and a Rate column, which could be set to zero for no-charge, or else, works as an override value for a period.

Does this seem like valid design? As the person is billed 95% of the time, and probably will never had any exclusion, it makes more sense to me to have an exclusion table.

If anyone has an idea of how to do this better, it would be great.

At the moment, I also have a 'calendar' table, which I join to based on the start/end date of the person's schedule to get the daily rate. So, I could then join to the exclusion date as well, to see if there is an override of the rate?

Issues I might find with my design, are a lot of the joins are based on:

 ON DateValue BETWEEN Start AND End

And I am not sure they're the most efficient joins.

Answer 1

If the exception could be one or more period of times (one-to-many) for one project then your design using an exclusion table is the best design.

Example:

June 1, 2013 to June 30, 2013

Exclusion:

June 9, 2013 - 0 Rate
June 25 to 27 - 30% of Original Rate

However, if the exclusion is possible and can only be a maximum of ONE single period (or one-to-one type of relationship) then you might instead put it on the same fields as other fields on project table.

Example:

June 1, 2013 to June 30, 2013

Exclusion:

June 9, 2013 - 0 Rate

Answer 2

I would use this "exclusion" table as single storage for person-project occupation data. In case when person is assigned to project one time without changes in rate, you will have one record in this table. In other cases you will have a history of rate changes in this table.