I'm really wrapped around the wheel on this one. I hope someone can help.

So we have some tasks that need to get done (DDL is for PostgreSQL):
Code:
CREATE TABLE task (
    task_id SERIAL PRIMARY KEY,
    task_name VARCHAR NOT NULL REFERENCES task_type,
    -- other fields omitted
);
Now we need a work schedule for our tasks:
Code:
CREATE TABLE schedule (
    schedule_id SERIAL PRIMARY KEY,
    task_id INT NOT NULL REFERENCES task,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    periodicity INTERVAL -- if NULL, then task is only done once
);
Here's some sample data for this table:
Code:
schedule_id | task_id | start_date | end_date   | periodicity
-------------------------------------------------------------
 1          | 1       | 05/01/2009 | 05/30/2009 | null
 2          | 2       | 06/01/2009 | 07/31/2009 | 2 weeks
Which would allow us to generate a schedule like:
Code:
task_id | start_date | end_date
---------------------------------
 1      | 05/01/2009 | 05/30/2009
 2      | 06/01/2009 | 06/14/2009
 2      | 06/15/2009 | 06/28/2009
 2      | 06/29/2009 | 07/12/2009
 2      | 07/13/2009 | 07/26/2009
 2      | 07/27/2009 | 07/31/2009
And then there's the actual performance of work:
Code:
CREATE TABLE work (
    work_id SERIAL PRIMARY KEY,
    task_id INT NOT NULL REFERENCES task,
    completion_date DATE NOT NULL
);
This seems nice and flexible, but I don't know how to properly relate an instance of work to a schedule. In particular, I'd like to be able to determine:

  1. For a given work instance, determine which period of the schedule it applies to.
  2. Given a schedule and some work, determine if/which periods of the schedule have not had work done.
  3. Enforce that work is done order -- i.e., work needs to be done such that the periods of a schedule are completed sequentially


I'd also like to make it possible to change/update a schedule. For example: "beginning on 7/1/09, task 2 needs to be done monthly (instead of every 2 weeks)". It seems like this could get quite hairy in terms of re-calculating a schedule's periods and maintaining relationships to completed work.

In my (limited) experience, I've found that a properly designed database makes the application side of things almost mind-numbingly easy to implement. The fact that I'm having problems with this suggests my design is flawed, or maybe I'm missing out on some requirements. I'd really appreciate some help on this.

Thanks!