Work Scheduling DB Design

Hello,

I'm trying to develop a database schema to schedule and record completion
of maintenance. I feel like I'm getting a bit wrapped around the wheel on
this one, so I was hoping someone might be able to offer some suggestions.

Here are the basic tables I've come up with:

CREATE TABLE task (
task_id SERIAL PRIMARY KEY,
task_name VARCHAR NOT NULL REFERENCES task_type,
-- other fields omitted
);

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 -- task is only done once if NULL
);

CREATE TABLE work_effort (
work_effort_id SERIAL PRIMARY KEY,
task_id INT NOT NULL REFERENCES task,
completion_date DATE NOT NULL
);

Here's some sample data for the schedule table:

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 allows me to generate the following schedule:

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

One of my objectives is to allow some flexibility in being able to change
or update a schedule. For example: "beginning on 7/1/09, task 2 needs to
be done monthly." I don't know if this is best done by being able to
update an existing schedule, or superceding old schedules with new ones.
Either way, it seems like things could get a little hairy in terms of
re-calculating a schedule's periods and maintaining relationships to
completed work.

Which brings me to my next problem: how to properly relate work efforts to
a schedule. Ideally, I'd like to accomplish the following:

1. Given a work effort, determine which period of the schedule it applies
to.

2. Given a schedule and some work efforts, determine if/which periods of
the schedule have not had work done.

3. Enforce that work is completed within the timeframe of a schedule, or
more specifically, within a specific period of the schedule.

5. Enforce that work is done order -- i.e., work needs to be done such
that the periods of a schedule are completed sequentially.

I'm hesitant to pre-generate work efforts based off a schedule, since
changing a schedule means I'll have to regenerate the work efforts, not to
mention that scheduled work might require multiple work efforts to
complete. So I'd like to be able to enter in work as it's completed, and
then enforce/validate that it's the _right_ work being done.

In my (very limited) experience, I've found that a properly designed
database makes the application side of things almost mind-numbingly easy
to implement. But everything I've come up with seems like it would
require some hideous triggers and/or lots of application code to
implement. I don't really see a nice clean solution here, which makes me
think I'm missing something.

If anyone has suggestions or some experience they could offer with this,
I'd greatly appreciate it.

Thanks!

Karl Nack

Futurity, Inc.
773-506-2007