How do I design a database for a resource scheduling and allocation application? 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!
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 &lt;= @NewFinishDate
  and finish_date &gt;= @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 &lt;= @TodaysDate
  and finish_date &gt;= @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 &lt;= @NextMondaysDate
    and B.finish_date &gt;= @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 &lt;= DATEDIFF(DD,@FirstDayOfMonth,@LastDayOfMonth)+1) B
on  A.start_date &lt;= B.CheckDate
and A.finish_date &gt;= 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.