How do I design a database for a resource scheduling and allocation application?

Question

I have two entities: Resource and Project. A Project runs for a certain amount of time (in calendar days), and I need to allocate several Resources to it (allocation interval - 1 work day, not an hourly basis).

Now if I create two tables (resource and project), how do I achieve this? How should the tables be designed? Do I create a new entry for every day and resource?

Any help is much appreciated!

Answer 1

If you need to answer questions like the following:

• What does the resource allocation look like today?
• Which projects have no resources assigned next Monday?
• How many resources were assigned to each project each day last month?

Then I would go with storing a row for every day each resource is assigned to a project. While these kinds of query are possible when using start/end date ranges, they're harder to write. Overlapping dates are also easier to prevent entirely using constraints or identify using queries. So your table would be like:

project_resource_days (
  project_id,
  resource_id,
  day
);

This also has the benefit that you can add further fields to give more information about why a resource was allocated to a project on a given day, should this need arise in the future (e.g. general notes, unbudgeted resources, project overrun, etc.)

I'm not clear what your requirements are regarding allocating resources to single or multiple projects. If some resources can only be assigned to one project on a given day and others to multiple projects on a given day, then you could have two allocation tables with the structure above. The different would be in the primary key definitions:

(resource_id, day) -- a resource can only be allocated to one project/day
(resource_id, project_id, day) -- resource assigned to multiple projects/day

You'll then need to extend your model/add further checks to ensure that resources are inserted into the correct allocation table depending upon their type.

UPDATE

To show the unassigned resources:

select * from resources r
where  not exists (select null 
                   from   project_resource_days p
                   where  p.resource_id = r.resource_id
                   and    p.day between @startdate and @finishdate)

Count of project resources allocated on given day(s):

select project_id, day, count(*)
from   project_resource_days
where  day between @start and @end
group  by project_id, day

Using start and end day ranges is a valid solution to this, I just think it's better to explicitly store the days the resources are allocated because:

• The queries are generally simpler
• You don't have to infer anything from the data

For example, say you're allocating a resource for all of March and April. If you just have a single record with start: 01-mar-2013, end: 30-apr-2013, does that mean resources were allocated/used on weekends and over Easter?

If you have an entry/day and there's a record for 31-mar-2013, then the resource was allocated on Easter Sunday. If this is wrong, then you can just delete that record. With a range 1/mar - 30/apr, you've either got to update the start or end and insert a new record or maintain a list of "non-allocation days". Either way, these solutions are more awkward than just deleting. You could argue this could be avoided by forcing people to enter the exact dates that will be used (excluding weekends), but eventually someone will get lazy and enter the full range.

You've not stated how many resource allocations you expect each day, but I wouldn't worry about the size of the data at this stage unless you're expecting it to be in the high millions.

Answer 2

The only possible reason that you might want to consider creating an entry for each resource and day is that it would give you the ability to use declarative constraints to enforce a business rule that each resource can only be allocated to one project (at most) per day. You didn't say anything about this being a business rule, so I wouldn't suggest modeling it that way. It's just more data to maintain.

You could use a schema like this:

RESOURCE:
 id (PK),
 name,
 ...

PROJECT:
 id (PK),
 name,
 ...

ALLOCATION:
 resource_id (PK/FK),
 project_id (PK/FK),
 start_date (PK),
 finish_date

This lets you assign resources to multiple projects over one or more ranges of dates. It has the advantage of minimizing the number of records to maintain, assuming that resources are assigned to projects for more than just a day at a time.

Alternatively, if you want the allocation table to be structured so that it enforces the rule that each resource can only be allocated to one project at a time, then you could use the following schema. Note that this requires a record per resource per day.

ALLOCATION:
  resource_id (PK/FK),
  project_id (FK),
  work_date (PK)

If you wanted to enforce this business rule through application logic instead of through declarative constraints in the database, you could use the first schema noted above instead.

---

Edit: Query to detect overlaps

If you use the first option, where allocations include a date range, then you need to use some application logic to enforce a business rule that says a resource can only be allocated to one project at a time.

To do this, what you want to do is this: Before every insert or update of a record in ALLOCATION, you need to check to see if the record you are about to write would overlap in an illegal way with all of the other records that are already in the table.

A query that would find potentially overlapping records would look something like this:

select
  resource_id
, project_id
, start_date
, finish_date
from ALLOCATION
where resource_id = @ResourceOfInterest
  and start_date <= @NewFinishDate
  and finish_date >= @NewStartDate

If you are inserting and the above query returns zero rows, then you are good to go.

If there are one or more rows returned, you can insert without creating an overlap.

If there is one row and you are updating, you need to make sure that you aren't just finding the row that you plan to update. You have to check the other column values to see if that is what you've found.

---

Queries to Answer Resource Allocation Questions

Using allocation tables with date ranges (instead of one record per day) makes answering some kinds of questions a little more complex. Still, it isn't hard to answer the same questions and the time you save maintaining daily rows will more than compensate for a little extra complexity in some of your queries. For example:

What does the resource allocation look like today?

select
  resource_id
, project_id
-- whatever else you want to join in...
from ALLOCATION
-- inner join whatever else you want...
where start_date <= @TodaysDate
  and finish_date >= @TodaysDate

Which projects have no resources assigned next Monday?

select
  A.resource_id
, A.project_id
-- whatever else you want to join in...
from ALLOCATION A
-- inner join whatever else you want...
where not exists
( select B.resource_id, B.project_id
  where B.start_date <= @NextMondaysDate
    and B.finish_date >= @NextMondaysDate
    and B.resource_id = A.resource_id
    and B.project_id = A.project_id )

How many resources were assigned to each project each day last month?

select
  A.project_id
, B.CheckDate
, COUNT(A.resource_id)
-- whatever else you want to join in...
from ALLOCATION A
-- inner join whatever else you want...
right outer join
(select DATEADD(DD,N-1,@FirstDayOfMonth) as CheckDate from NUMBER 
 where N <= DATEDIFF(DD,@FirstDayOfMonth,@LastDayOfMonth)+1) B
on  A.start_date <= B.CheckDate
and A.finish_date >= B.CheckDate 
group by
  A.project_id
, B.CheckDate   
-- Also group by anything else you've joined in, like project name, etc.

This last one uses a Number Table which is something every database developer should have in their toolbox.