Design a process to archive data (SQL Server 2005)

Question

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

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

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

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.

What I need to know?

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

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

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

4. 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.

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

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.

Answer 1

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.