We're creating a new version of a 20 years old database, in which we used to keep version history by creating new rows in each table (SCD Type 2).
SCD Type 2 is not that hard when you need to keep history on a single table. However, if a table has dependencies that go all the way up (or down) in the database structure, you need to keep history of the whole structure. It is difficult to keep this clean enough, so I am looking at alternatives.
1rst alternative
I am aware of solutions like Oracle's flashback. What I am not sure about is if it is flexible when it comes to small schema changes (adding a column)? We won't have big changes to our schema, but in 5 years we might discover that we need some additional info.
2nd alternative
We're thinking of creating a history table where we keep the affected structures as XML. If the schema changes, we will create the tools that are going to update the history to include the new schema.
Any pros and cons of these solutions? Any suggestions are welcome (for other alternatives too)!
PS: As you might have guessed we're using Oracle.
