R – Task planning data/object structure


I need to create a database structure for managing a gantt style system of tasks and resources, there are some custom needs and the technology involved doesn't play well with others, so I'm thinking of doing it from scratch. My needs are simple:

  • Tasks, prioritized
  • Resources (just people)
  • Calendars (mostly being able to handle non-working days)

I've checked the Microsoft project data structure but it's way to complex for my needs, and haven't been able to find anything simpler that's more or less documented.

The most important use case is taking an unassigned task and drop it into the worklist of a resource, making all the dates adjustments in the surrounding tasks.

There's no need to plot a graph for the moment.

Is there some standard way of doing this that I'm missing?

Best Solution

Sounds like you mostly just need a TASKS table with the task name/description, status, priority, duration and start/end dates, with some kind of flags to indicate which fields are "fixed"; e.g., if a task has a "drop dead" end date, then that's fixed and can't be modified by the scheduler. Likewise, if something has already started (or has to be started immediately), then the start date is fixed and can't be changed. You can add "% complete" or "hours worked" or "scheduled/actual start/finish" or whatever other fields you want. Resources will probably be just a simple name+id pair. For flexibility I'd suggest having a TASK-ASSIGNMENTS table that maps tasks to resources. This will permit assigning a task to more than one resource (like assigning a task to a team) and will also give you a place to store an "assigned-to" date. Depending on how comprehensive your scheduler has to be, you might also want to have a separate TASK-DEPENDENCIES table to track dependencies (can't schedule framing before the foundation is poured, etc.) For calendars you might be better off just storing "exception" dates like holidays or vacation days and otherwise assuming a standard work week. Unless, of course, there is no such thing on your projects, in which case why worry? Just schedule tasks whenever they fit and let people fill in the start date when they start the task. Calendars could either be global or resource-specific.

Do you need to group tasks by project? If so, then of course you'll need a PROJECTS table, and you might want to have some kind of PROJECT-RESOURCES table that maps resources to projects. This is a good place to store the amount of work a resource is supposed to devote to a particular project (like 30% of their time) and/or the priority a resource is supposed to give a particular project. You might also want to have some way to assign a calendar to a project, either as a default or an override (probably want a flag to specify which).

I could go on, but I see I already have...