suggestions for maintaining master/child task list

I have a task table that has a hierarchial structure as follows:

Visit One

    Task A
    Task B
    Task C

        FollowTask C1
        FollowTask C2

Visit Two ...

I have completed/cancelled flags on every row. I have two major issues to keep track of.

1. Ability to quickly complete all tasks by flagging Parent Task as completed - this should set the completed flag of all child rows to completed
2. Once all the child tasks are flagged as completed then the parent should be flagged completed.

I can put a lot of logic in the frontend application (and I need some for warning messages on issue 1) but I really wanted a backend solution. I would like a realtime solution as opposed to a nightly or hourly cleanup.

My guess is this is not a new chore and there are some good solutions out there. Right now I am thinking of something along the lines of

1. On RowTrigger capture task_id / parent_task_id to tmp table
2. On StatementTrigger process all tasks in tmp table and do the math.

or something related but with DBMS_JOBS

From a users perspective they won't update a lot of tasks at once but the task table will get very large. So I am happy with a solution that works quickly on a small dataset and might need a bit of runtime if I have to do any batch processing.

Any suggestions would be greatly appreciated.

thx

jack Received on Mon May 02 2005 - 11:51:06 CDT