MySQL Storing logged progress and expected progress

Question

I need to log progress of a project at weekly intervals, but I also need to store expected progress at these intervals for comparison in the future.

Typically each project has several tasks of different durations which make up the total duration of the project.

When a project is first stored, I will know the start and end dates and so I would then store the weekly intervals in a logs table. I can then map this against the tasks to store expected progress at each interval. I then plan to use a cron job to log the actual progress each week.

I don't feel easy about this solution. What if the planned dates for the project change? If another task is stored and extends the overall duration of the project, the original stored intervals might not match up. Also, what if a task starts midweek? This won't match up to the project wide intervals?

How might I execute this within the confines of MySQL?

Answer 1

A few tips:

1. Don't store aggregated task figures in the project table. Since the task figures are subject to change, they should only be stored in historical snapshot tables and aggregations should be calculated.
2. There may be business factors that affect whether the planned end date should be stored or just calculated. E.g. project managers may have a fixed date they've agreed to that can only be changed after agreement with the customer (perhaps separately from adding/removing tasks). It may also be useful to calculate the currently expected end date but the previous point applies to this.
3. Would suggest a simple "snapshot" table that does not dictate when progress snapshots are made but instead allows the current figures for a particular task to be recorded at any particular time.
4. It may be useful to store the original estimate for tasks as well as the current time logged and remaining estimate. If this is the case, the original estimate does not also need to be stored in the snapshot table since it is assumed not to change and can simply be referenced.

Here's a simple schema as a starter for ten:

CREATE TABLE `project`
(`id` int auto_increment,
 `name` varchar(255),
 `startDate` datetime,
 `plannedEndDate` datetime,
PRIMARY KEY (`id`));

CREATE TABLE `task`
(`id` int auto_increment,
 `project_id` int,
 `originalEstimate` int,
 `timeLogged` int,
 `remainingEstimate` int,
PRIMARY KEY (`id`),
KEY `FK_task_project` (`project_id`),
CONSTRAINT `FK_task_project` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`));

CREATE TABLE `progress`
(`id` int auto_increment,
 `task_id` int,
 `timestamp` datetime,
 `timeLogged` int,
 `remainingEstimate` int,
PRIMARY KEY (`id`),
KEY `FK_progress_task` (`task_id`),
CONSTRAINT `FK_progress_task` FOREIGN KEY (`task_id`) REFERENCES `task` (`id`));