Designing a data model to handle both recurrence and nested dependencies

Question

I'm building a web application that involves both recurrence and nested dependencies, and I'm struggling to pin down the appropriate data model.

The problem that the application aims to solve is: we have a few processes that we need to do at regular intervals, which involve multiple steps and multiple people. Some of those steps can't begin until earlier steps are completed. For example:

• Every month, we need to produce an exec briefing on the previous month's performance. Once the month ends, 3 people need to pull data from 3 systems. Once all 3 have been pulled, a fourth person needs to analyse the data and prepare the briefing. This process should begin on the 1st of the month, though exceptions are made for weekends/holidays, etc.

The web application should be able to:

• Show all tasks that are currently actionable (e.g. not blocked by other tasks they depend on)
• Modify future occurrences (e.g. skip one, or change a date) like you would a repeating event on a calendar
• Keep history for each task, e.g. completion date and who completed it, and comments

I think the nested dependency aspect is a bit easier to solve, or at least more familiar to me: a recursive common table expression seems a good fit for it. Where I'm struggling is making this work with recurrence.

Options I've considered so far:

A. 'definitions' and 'instances' tables

e.g. process_definitions, process_instances, task_definitions, and task_instances. Project future instances using the schedule from the definition (e.g. via generate_series()), and create the instances at the point the user interacts with one (e.g. to change its date, mark it completed, leave a comment).

The trouble with this is:

• What about the dependencies? I'd normally have a task_dependencies table, since it's a many-to-many relationship. I'd either need to copy these records for each task instance (which could quickly amount to a very large table), or do a rather convoluted join (instead of task_instances->task_instance_dependencies->task_instances, it would be task_instances->task_definitions->task_dependencies->task_definitions->task_instances)

B. denormalised version

Similar to (my understanding of) an iCalendar feed, just have a single table of processes and a single table of tasks with the definitions copied to each instance. This is potentially simpler (though may just shift the complexity), but would still lead to a very large task_dependencies table.

C. array column

Store task dependencies as an ARRAY column, but then I wouldn't get the benefit of foreign key constraints (and I'm not actually sure how to do JOINs that way anyway)

How would you design the data model for this use case?

Answer 1

Personally I think you’d make your life much easier by finding a “project management” application that meets your needs. I’m sure that there are web-based ones, as well as standalone ones.

A project management application will let you easily do things like:

• Define arbitrary tasks; eg. “Task 1: Wingle the Wangle”; “Task 2: Frongle the Doodle”.

• Enter task details; eg. Task 1 needs 5 hours work; Task 2 must be finished by Wednesday; Task 3 can only be done by Fred or Mary.

• Enter task dependencies; eg. Task 1 must be finished before any of Tasks 2, 3 or 4 can be started - but the latter three tasks can be done in any order.

• Enter resource calendars; eg. Fred does not work Fridays; Mary is away next month.

The project management application will then calculate all important missing information, as best it can, from whatever data you supply.

For example, if you tell it that the project above will start next Monday, it might tell you the exact expected start and finish date of every task therein.

That may well be simple to do manually for just a few tasks, but becomes exponentially more difficult as the number and complexity of tasks increases.

A project management application will certainly use a database behind the scenes. But the database is just one part - you need screens, reports and lots of other potentially complex program code “behind the scenes” to make the whole thing work.

So your question is a bit like asking, how to design a petrol tank to let you go from one point to another point 10 miles away? The answer is, you’ll need a lot more than a petrol tank! Just go out and buy a car 🙂

HTH

REPLY TO OP’s COMMENT

The website doesn't let me reply to the OP’s comment☹️ Could an admin change the next text to a comment?

——— Sorry, but project management applications are complex beasts, requiring potentially complex database schemas. The way that I suggest you proceed, is the standard data modelling approach: (1) identify relevant entities (TASK, RESOURCE, CALENDAR etc.); (2) determine the relationships between all those entities (eg. a TASK can have many RESOURCEs, and vice versa, so TASK:RESOURCE is a many-to-many relationship); then (3) design the database tables accordingly: two entities related 1:1 can be stored in a single table; 1:many requires two tables; many:many requires three tables. Good luck! ——-