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.