Options for supporting live and WIP versions of same data?
I'm working on a design for a product where certain entities in the schema will have multiple instances of the data in different states of approval or ongoing revision.
One example would be a database entity that represents a 'policy'(i.e. standing instructions to complete some duty). At any one time the product might have a version of the 'policy' that has been published and is available for public viewing, and at the same time a revised copy of the orginal policy that is being updated by its authors. Only the policy authors would be able to see both public and revised instances. Once the revisions are complete the new version would be made public, and the earlier version retired.
I'm wondering how best to design this type of data pattern in my schema.
Should I employ separate tables for the approved and being revised instances? Or should both instances go into the same table, and have extra fields to distinguish on version/state?
In reality the logical instance covers multiple tables, as there's a lot of subordinate data to store.