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.

  • See this previous question for some ideas.
    – andba77
    Commented Aug 28, 2015 at 12:57
  • @andba77 Thank you! There are some alternatives there, but I will need some feedback also on the aforementioned solutions.
    – Midas
    Commented Sep 1, 2015 at 9:11


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.