任务报告的数据库架构

发布于 2024-09-14 08:24:52 字数 739 浏览 3 评论 0原文

我有一个任务跟踪应用程序，这是非常基本的。它当前有许多任务，进度会随着任务完成而更新，从 0 到 100%。

问题是，这些数据都是非常短暂的，我想在任务的顶部添加报告，因此如下所示：

两个日期之间添加的任务数量
两个日期之间完成的任务数量
工作量（所以这是总计针对任务的工作量，除以 100，再乘以两个日期之间完成百分比的变化，

依此类推。

如何及时冻结数据以便进行此类报告？

这些是我到目前为止想到的想法：

想法 1.永远不要更改任务定义，而是添加一个“TaskProgress”表，其中包含任务进度的粒度更新 - 然后您可以“总结它们”获取当前状态或获取两个日期之间的记录以查看两个日期之间取得了多少进展。好处是，这将在报告要求发生合理数量的变化后继续存在，坏处是获取任务的当前进度将需要更多的查询工作。

想法2.创建一个表来存储进度变化。每次更新任务时，还添加一条记录，其中包含由更改引起的计算班次。然后，报告只需选择所需日期之间的记录并对它们进行总结即可。好处是，这不会极大地影响现有功能，也不会需要太多查询工作，坏处是，如果该表不包含您想要添加到报告中的信息，则您必须更改该表。

想法 3。想法 3 是根据想法 1 创建“TaskProgress”表，但仍保留任务的当前总计进度。这确实意味着任务进度项的总和再次作为任务本身的总计存储，但读取这些值比计算它们更快，并且为以后更改报告提供了更大的灵活性。

非常欢迎对这些想法提出任何意见或更好的建议。

原文

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.

分享到QQ

分享到微博