Database Schema For Task Reporting

I have a task tracking application, which is very basic. It currently has a number of tasks and the progress is updated as the tasks get completed, from 0 to 100%.

The problem is, this data is all very transient and I want to add reporting over the top of the tasks, so things like:

Number of tasks added between two dates
Number of tasks completed between two dates
Amount of effort (So this is the Total Effort against the task, divided by 100 and timesed by the change in percentage complete) between two dates

And so on.

How do you freeze data in time in order to allow this kind of reporting?

These are the ideas I've thought of so far:

Idea 1. Don't ever change the task definition, but add a "TaskProgress" table that contains the granular updates to progress for the task - you can then "sum them up" to get the current status or take the records between two dates to see how much progress was made between the two dates. The up-side is that this will survive a reasonable amount of change to the reporting requirements, the down-side is that getting the current progress of a task will require more query-effort.

Idea 2. Create a table to store progress changes. Each time a task is updated, also add a record that contains the calculated shift caused by the change. The reports are then just a case of selecting the records between the required dates and summing them up. The up side is that this won't greatly affect existing functionality and won't be too much query-effort, the down side is that you would have to change this table if it didn't contain information you wanted to add to a report.

Idea 3. Idea 3 would be to create the "TaskProgress" table from Idea 1, but still keep the totalled-up current progress against the task. It does mean that the sum of the task progress items is stored again as a total on the task itself, but it will be faster to read these values than calculate them and it gives more flexibility for changing reports later.

Any comments on these ideas, or better suggestions are very welcome.

I'll try to explain a little better. At the moment the task data relates to the current state of the task. ie "PercentageComplete: 100, Status: Complete". But to get a report you need to know how much change there was between two dates. For example, on date 1 it was "PercentageComplete: 80" and on date 2 it was "PercentageComplete: 90", therefore there was progress of 10% during that period. I may use a replication of data to run the reports, but this is unrelated to the schema needed to support the report itself (ie I need to store each change in the state of a task if I want to report on it)

Sohnee 2010-08-17 14:19:54

Now I get you. You will need some sort of auditing table to store the changes. I personally would keep the Tasks table unchanged, but add an audit table something along the lines of Idea 2. Every time the progress column is changed you add a row to the audit table containing task id, datetime, old value and new value. You could easily do this using a trigger. This has the advantage that you are still able to get at the current progress value easily, and the reporting concerns are decoupled from the main table.

Tim 2010-08-17 15:28:47

Okay, this is what I'm doing... Each time a task is saved, it triggers an addition to the task progress table, recording the change. This can then be used to calculate the progress (or un-progress) of tasks and you can take the records from the task progress table between two dates to see where you got to in that period. Cheers.

Sohnee 2010-08-20 13:02:53

ansaurus

tags:

views:

answers:

Database Schema For Task Reporting

related questions