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.

Any suggestions?

Separate tables means more to manage contiinuously. Easier to generate a next version number and copy the documents in the scope. You would use a flag to control access rights.

End