What is the best way to archive data in a relational database?
- by GenericTypeTea
I have a bit of an issue with a particular aspect of a program I'm working on. I need the ability to archive (fix) a table so that a change anywhere in the system will not affect the results it returns.
This is the basic structure of what I need to fix:
Recipe --> Recipe (as sub recipe)
Recipe --> Ingredients
So, if I fix a Recipe, I need to ensure all the sub recipes (including all the sub recipes sub recipes and so forth) are fixed and all its ingredients are fixed. The problem is that the sub recipe and ingredients still need to be modifiable as they are used by other recipes that are not fixed.
I came up with a solution whereby I serialize (with protobuf-net) a master object that deals with the recipe and all the sub recipes and ingredients and save the archive data to a table like follows:
Archive{
ReferenceId, (i.e. RecipeId)
ReferenceTypeId, (i.e. Recipe)
ArchiveData varbinary(max)
}
Now, this works great and is almost perfect... however I totally forgot (I'd love to blame the agile development mentally, however this was just short sighted) that this information needs to be reported on. As far as I'm aware I can't think how I could inflate the serialized data back into my Recipe Object and use it in a Report. I'm using the standard SQL 2005 report services at the moment.
Alternatively, I guess I could do the following:
Duplicate every table and tag the
word "Archive" on the end of the
table name. This would then give me
an area of specific archive data...
but ignoring my simplified example,
there'd actually be about 15 tables
duplicated.
Add a nullable, non-foreign key
property called "CopiedFromId" to
every table that contains fixed data
and duplicate every record that the
recipe (and all it's sub recipes and
all their sub recipes) touches.
Create some sort of denormalised
structure that could be restored
from at a later date to the
original, unfixed recipe. Although I
think this would be like option 1
and involve a lot of extra tables.
Anyway, I'm at a total loss and do not like any of the ideas particularly. Can anyone please advise the best course of action?
EDIT:
Or 4) Create tables specific to what the report requires and populate them with the data when the user clicks the report button? This would cause about 4 extra tables for the report in question.
