Versioning without Triggers or 3rd Party Tools 2

[Deadline]

Hello,

We are trying to implement a versioning mechanism for any table. I'd like to have your suggestions and recommendations in designing one. Triggers and 3rd Party Tools aren't permitted at this stage.

The scenario is like this :

When a record in a table is 'updated', it should not be actually updated or deleted but it needs to be 'moved up' and a new one inserted with a different version number and the changes.

For example, considering a Master Record M1, stored in database, with multiple child records(S1, S2, S3)- If S1 is changed/updated, its latest version becomes 2, while S2 and S3's latest version number remains 1. The overall version of M1 becomes 2(two).

The idea behind this is, to implement a generic design to fetch any version of the Master Record with a simple SELECT, given the version number.

Please provide me with some pointers in this. We've been discussing this for quite some time now in our team, with no conclusive solution yet. I can explain further if necessary.

Thank you very much in advance.

Thank you,
RR.
__________________________________
The best is yet to come.

 

[fluteplr]

In order to do this without triggers, you would have to do ALL your updates through stored procedures that would first do all the mucking around you explained above.

The problem with this theory is what happens when an update is done by some programmer or dba who 'just needs to fix something' and forgets to go through the procedure.

Why you would want to do this and try do do it with out triggers is totally beyond me. Lots of luck.

I would rethink the entire concept and go with something much simpler.

For example

for each table where you need to track versions, create a table with the same structure plus a datetime field and possibly a field for storing who did the update, call it something like archiveMytable.

Each time a record is updated, use a TRIGGER, yes there it is, to copy the entire record from the 'deleted' psuedo table to the 'archive' version with a datetime stamp and who did the update.

 

[Deadline]

Hello FlutePlr,
Thank you very much for your response. We originally thought of implementing this versioning scheme for about any set of Master Detail Table. We thought using the technique you've mentioned (with triggers) will reduce/limit the possibility of generalizing the implementation, as we may have to create a duplicate table for every table under consideration.

Since even after several rounds of discussions, we couldn't find a aceptable design for our problem, we decided to implement this solution with Triggers anyway. Thank you again for responding.

If you come across any other approach please do let us know. Even a small clue would do.

Thank you,
RR.
__________________________________
The best is yet to come.

 

[Inwoner]

I also think Triggers are the best solution. I want to build on that.

I would recommend that for every entry, you create an extra field (I like to called it IsActiveRec). Throught the triggers, you ensure that only the last record contains Isactiverec=1, and all the old entries contain IsActiveRec=0.

Since most queries are normally build on Active Data, you can put WHERE ISACTIVEREC=1 in your queries, thus improving performance quite a lot.

Also build an index on this. Normally you don't need an index if data is distruted 50/50, but after you database have aged a little, there will be more non-active records than active records, so an index will provide better performance there