I am currently in the planning stages for a web based application which reallt needs record versioning.

However I do not want the versioning system to over complicate the whole model and all of the queries. So I had an idea that someone who knows better might tell me is completely crazy, but here goes:-

Run 2 identical databases: 'current version' 'old version' except that the 'current' database would have triggers on all the tables so that when a record was changed it put the current version of the record (which is about to be updated) into the 'old version' database. So there would be just 2 record versions; the current and previous.

Then to make this more sophisticated I was thinking that I could run CDP hot backups on the 'old version' database, so then if for whatever reason they needed a version from last week, we could go back to that restore point on the 'old version' db and then they would be able to view those versions in the application.

Any advice/discussion would be much appreciated.

Thanks in advance!