设计归档数据的过程 (SQL Server 2005) [英] Design a process to archive data (SQL Server 2005)

问题描述

我们正在设计一个流程，根据日期、状态等不同标准来归档一组记录...

We're designing a process to archive a set of records based on different criteria like date, status, etc...

简化的一组表格: Claim、ClaimDetails、StatusHistory(跟踪索赔状态的变化)、Comments &文件

Simplified set of tables: Claim, ClaimDetails, StatusHistory (tracks changes in Claim status), Comments & Files

环境:SQL Server 2005、ASP.Net MVC (.NET Framework v3.5 SP1)

Environment: SQL Server 2005, ASP.Net MVC (.NET Framework v3.5 SP1)

Claim 是主要实体，它在提到的子表中有子行.有些有详细信息，有些则用于跟踪更改.最终，根据某些标准，Claim 变为准备存档"，如上所述.简而言之，归档的 Claims 将从数据库中识别出来，并在网络应用程序中进行不同的处理.

Claim is the main entity and it has child row(s) in the sub tables mentioned. Some have details and others are used to track changes. Eventually based on some criteria a Claim becomes "ready to archive" as explained above. In simple words archived Claims will be identified from the database and treated differently in the web-app.

这是一个最简单的版本:(顶视图)

Here's a simplest version: (top view)

• 创建一个脚本，在数据库中标记Claim存档".
• 归档的行及其子行可以保留在同一个表中(设置一个标志)，也可以移动到另一个表中，该表将是原始表的副本.
• 在网络应用中，我们将让用户根据存档状态过滤声明.
• 将来可能需要取消归档"声明.

• Create a script which marks a Claim "archived" in db.
• Archived row and its child row(s) can either be kept in the same table (set a flag) or moved to a different table which will be a replica of the original one.
• In the web app we'll let the user filter Claims based on the archive status.
• There might be a need to "unarchive" a Claim in the future.

我需要知道什么?

1. 我们需要使这尽可能简单易行，并灵活地适应未来的变化 - 请提出一种方法.

1. We need to keep this as simple and easy as possible and also flexible to adapt future changes - pls suggest an approach.

及时调度的 SQL 脚本是否是这种情况的最佳选择?

Is a timely scheduled SQL script the best option for this scenario?

我应该考虑使用单独的表格还是只为每个表格添加一个存档"标志?

Should I consider using separate tables or just add an "Archived" flag to each table?

对于所选方法的性能考虑 - 如果我们计划有大约 10,000 个 Claims 和如果是 100 万个，会有什么不同.简而言之，请提一盏灯&重载方法.

For performance consideration of the selected approach - what difference would it make if we plan to have about 10,000 Claims and what if its 1 million. In short pls mention a light & heavy load approach.

我们将物理上传的文件存储在我们的服务器上 - 我相信这可以保持原样.

We store files uploaded physically on our server - I believe this can stay as it is.

注意:一个声明在所有提到的表中可以有任意数量的子记录，所以它会变成 n 倍.

Note: A claim can have any number of child record(s) in all the mentioned table so it gets n-fold.

是否有标准框架或模式可供我参考以了解存档过程.任何示例参考文章或工具都会帮助我了解更多信息.

Is there a standard framework or pattern that I should refer to learn about archiving process. Any sample ref article or tool would help me learn more.

推荐答案

您可以找到有关此主题的一些很好的讨论 这里 和这个是另一个.

You can find some good discussion on this topic here and this is another one.

将存档数据放在单独的表格中可提供更大的灵活性，例如您想跟踪将索赔标记为存档的用户或索赔存档的日期，或者查看索赔创建后对索赔所做的所有更改.

Having archived data in separate table gives more flexibility like if you want to track the user who marked a claim as archived or the date when a claim is archived or to see all the changes made to a claim after it is created.

这篇关于设计归档数据的过程 (SQL Server 2005)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！