冻结往年数据
我有一个数据库可以跟踪员工当年和往年的数据。 在下面的示例中，我将使用日历年（年份从 1 月开始，到 12 月结束） - 情况并非总是如此，一些用户的年份从 7 月到 6 月 - 或 4 月到 3 月，等等。
有很多表，通过一些繁重的计算，及时查看该员工的数据。
今年的数据是用户最看重的。但往年的数据会对今年产生影响（因此，对 2008 年数据进行的更改将对 2009 年产生连锁效应，然后影响到 2010 年，依此类推）。
显然，这会对查看报告时的性能产生负面影响，因为查看今年的数据将意味着艰难地遍历前几年的所有数据 - 计算和创建视图，直到找到最终结果。随着应用程序的老化，这个问题会变得越来越严重——比如在 2015 年，任何从一开始（2008 年）使用该系统的人都将等待很长时间才能获取他们的数据。
我们计划冻结前几年的数据，这样我们就不会使用 2008 年、2009 年、2010 年的数据和今年的数据，而是使用一个包含前一年数据（对前几年完成的所有计算）和今年数据的块。
这样我们就得到了去年所有数据的最终结果数据，只需要添加到今年就可以得到最终结果。
显然，我们必须阻止用户输入/更新前几年的数据。
我的问题是实现这一目标的最佳方法是什么？我想您需要一些等到新的一年并进行一些计算的过程。
预先感谢，
ViperMAN。
I have a database that tracks employee’s data for the current year and previous years.
In examples below I will use calendar years (years start in Jan and end in Dec)- this is not always the case, some users have their year running from July to June- or April to March, etc.
There are many tables that, with a few heavy calculations, make a view of the employee’s data at this point in time.
This current year’s data is what users look at mostly. But data from previous years impact on this year (so, a change made to the 2008 data- will have a knock on effect on 2009, and then into 2010 and so onto this year).
Obviously, this has a negative impact on performance when viewing reports as viewing this year’s data will mean trudging through all the previous years- calculating and creating views until the end result is found. As the application ages, this problem will get worse and worse- say in 2015, anybody using the system from its inception (2008) will be waiting for a long time to get their data.
We plan to freeze previous years data so instead of having data from 2008, 2009, 2010 and this year’s data- we would have one block with the previous year’s data (with all calculations done for those previous years) and this year’s data.
In this way, we would have the end results data for all the previous year’s already calculated and we would only need to add to this year to get the final result.
Obviously, we would have to prevent users from entering/updating data in previous years.
My question is what is the best way to achieve this? I presume you would need some process that waits until the new year and does some calculations.
Thanks in advance,
ViperMAN.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您描述的方法通常称为数据归档，您可以让 DBA 在每年新年聚会后的第一个工作日手动运行一些查询，以便准备和存储计算数据。
另外，如果我做对了，您的应用程序需要拒绝用户修改前几年的数据。
the approach you describe is normally referred as data archiving, you can have some queries a DBA runs manually every year the first working day after the new year party so the calculated data is prepared and stored.
Also, your application needs to deny users to modify previous years data, if I have got it right.
我想到的一种方法如下：
检查今天是否是员工年的第一天-
如果是，则获取过去一年的所有数据并将它们添加到之前的所有年份中。
显然，这是一个简化版本，但我认为可以使用。
想法？
One approach I was thinking works as follows:
Check if today was the first day of an employees year-
If yes, get all of the elapsed year's data and add them to all the previous years.
Obviously, this is a simlified version- but one that I think could work.
Thoughts?
如果您有不应编辑的数据，并且可以定义该数据是什么，那么我将结合使用存储过程和安全设置来确保旧数据保持准确。
如果您使用存储过程作为过滤器，则可以在存储过程中使用逻辑来根据当前日期时间检查记录，并且仅在一切符合您的要求时才允许更新。
If you have data that should not be edited, and you can define what that data is, then I would use a combination of stored procedures and security settings to ensure the old data stays accurate.
If you used stored procedures as a filter, you can have logic in your stored procedure that checks the record against the current DateTime and only allows the update if everything fits your requirements.