2

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hemant Tank
  • 1,724
  • 4
  • 28
  • 56

1 Answers1

2

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.

Community
  • 1
  • 1
kishore
  • 719
  • 9
  • 29
  • Thanks but this is more like maintaining DB log on every edit. I need something that maintains the "Archive" once the record is archived, it becomes readonly so there's no need to maintain multiple versions. Besides, we're talking about a few million records over a timespan of 2-3 years. – Hemant Tank Dec 21 '11 at 08:26
  • 1
    Having an Archived flag on each table is the simplest solution, but it is not a very flexible approach for any future changes you might have. You should also consider how often archived data is being used in your applications. If active claims are accessed more often than archived ones then creating a separate table for archived claims gives better performance, as you will be querying a small set of data. – kishore Dec 21 '11 at 17:32