thanks. The bitemporal tables are somewhat similar except that there is not a date range to distinguish between versions of objects. One could use this long transaction feature for comparing the results of different plans of implementing something - and these could be affecting many objects from the parent version (or it's parent and so forth).
Similarly as with bitemporal tables the FK constraints are problematic. If an object is to be deleted in parent version, any created child object of this in child version cannot be posted to parent version later. That's why I believe it is best first merge the changes done in parent version to current version before you are allowed to post.
To solve this in application layer for an existing application is quite complex and this is why I am looking for a more generic solution within the database.
This seems pretty complex to address at any layer! But it would be a useful feature for some situations, for sure. It seems like the approach taken by Ruby/Rails and similar frameworks for "db migrations" might have some useful design patterns for you. Each transaction has a link to its future and past transaction. If the system wants to traverse from "migration 6 to migration 3" and the DB is currently at migration 6 (stored a version number in the db itself), it runs the exit function to downgrade to migration 5, then runs the logic for migration 5, then runs the downgrade to 4, etc.
It can be slow, as implemented, but it allows you to attach logic to every step forward and backward in time that ensures you can handle even structure changes in the tables, etc (as well as changing lookup values in tables, etc).
