1

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!

2 Answers 2

2

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.

2
  • Thank you for your answer! How would I query “what resource is available form @start_date to @finish_date” when using this schema?
    – vzhernovoi
    Apr 3, 2013 at 20:40
  • Thank you very much, Chris! I will probably go this route, since it allows to enter comments for each specific date and accounts for weekends/holidays in an easier way.
    – vzhernovoi
    Apr 6, 2013 at 19:16
0

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.

3
  • Than you, Joel! It makes very much sense. What should I do if I have several types of resources and some of them can only be assigned to one project at a time? Putting the resource/type relationship aside, how would the allocation look like?
    – vzhernovoi
    Mar 31, 2013 at 16:54
  • @vzhernovoi - I've edited my answer to show what the ALLOCATION table would look like if you wanted to enforce the "one project at a time" rule through declarative constraints. If you have multiple resource types then you should probably have one allocation table per resource type table. There are other ways around this but it would involve making design decisions that could have unintended negative consequences. Google around for the term "entity-subtyping" for a discussion of how to track multiple types of an item in a relational database.
    – Joel Brown
    Mar 31, 2013 at 18:19
  • thank you many times! I suppose enforcing the logic through the table design wouldn't be the best option (because of the amount of entries to maintain, I assume), any hints on how to do this with queries?
    – vzhernovoi
    Mar 31, 2013 at 21:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.