如何最好地处理历史数据的存储？ [英] How to best handle the storage of historical data?
问题描述
我正在尝试确定如何存储历史事务数据。
我应该将它存储在一个表中，记录只是重新插入一个新的时间戳每次？
我应该将历史数据分成单独的历史记录表，只保留活动表中的当前数据。
如果是这样，我最好怎么办？使用触发器自动将数据复制到历史表？
$ b
更新每个Welbog的评论：将会有大量的历史数据（数十万行 - 最终可能达到数百万）
主要是对历史数据运行搜索和报告操作。
性能是一个问题。
如果要求仅用于报告，请考虑构建一个单独的数据仓库。这允许您使用数据结构，例如缓慢更改维度，这对历史报告更好，但在事务系统中无法正常工作。结果组合也会将历史报告从生产数据库中移走，这将是一项性能和维护的成功。
如果您需要在应用程序中提供此历史记录，实现某种版本或逻辑删除功能或使一切完全禁忌和重述（即事务不会被删除，只是反转和重述）。非常仔细地考虑是否 真的 需要这个，因为它会增加很多复杂性。使得能够正确重建历史状态的事务应用程序比它看起来困难得多。
如果您仅需要历史记录用于审计日志记录，可以创建影子表和审计日志触发器。这比在应用程序中正确和全面地实现审计日志记录要简单得多，而且更加强大。触发器还将从应用程序外部的源接收对数据库的更改。
I'm trying to determine how I should store historical transactional data.
Should I store it in a single table where the record just gets reinserted with a new timestamp each time?
Should I break out the historical data into a separate 'history' table and only keep current data in the 'active' table.
If so, how do I best do that? With a trigger that automatically copies the data to the history table? Or with logic in my application?
Update per Welbog's comment:
There will be large amounts of historical data (hundreds of thousands of rows - eventually potentially millions)
Primarily searches and reporting operations will be run on the historical data.
Performance is a concern. The searches shouldn't have to run all night to produce results.
If the requirement is solely for reporting, consider building a separate data warehouse. This lets you use data structures like slowly changing dimensions that are much better for historical reporting but don't work well in a transactional system. The resulting combination also moves the historical reporting off your production database which will be a performance and maintenance win.
If you need this history to be available within the application then you should implement some sort of versioning or logical deletion feature or make everything fully contra and restate (i.e. transactions never get deleted, just reversed out and restated). Think very carefully about whether you really need this as it will add a lot of complexity. Making a transactional application that can reconstruct historical state correctly is considerably harder than it looks. Financial software (e.g. insurance underwriting sytems) fails to do this a lot more than you might think.
If you need the history solely for audit logging, make shadow tables and audit logging triggers. This is much simpler and more robust than trying to correctly and comprehensively implement audit logging within the application. The triggers will also pick up changes to the database from sources outside the application.
这篇关于如何最好地处理历史数据的存储？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
