设计存档数据的过程（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...

简化的表格集： 声明，声明详细信息，StatusHistory （跟踪声明状态的更改），评论& 文件

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

环境： SQL Server 2005，ASP.Net MVC v3.5 SP1）

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

声明是主要实体，它在子表中有子行提到。有些有细节，其他则用于追踪变更。最终根据一些标准，如上所述，声明变成准备归档。以简单的方式存档声明将从数据库中识别出来，并在Web应用程序中进行了不同的处理。

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)

• 创建标记声明的脚本存档的数据库。


• 存档的行及其子行可以保存在同一个表中（设置一个标志）或移动到另一个表，该表将是


• 在网络应用程序中，我们将根据归档状态筛选声明。


• 将来可能需要取消存档声明。

• 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个索赔，那么它将会有什么不同，如果它的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屋！