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 tw开发者_开发知识库o 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'm not sure I understand your ideas, but if the problem is being able to report on transient data then why not use a copy of the database? We always report on a copy normally for performance reasons, but it would solve your problem. Backup the database then restore it to another database, do your reporting, then drop the database.