I am considering applying object-versioning using the added twist of requiring to possess both live and draft objects, and may make use of the experience from someone experience of this, as I am starting to question whether it's even possible without potentially terrible hacks.

I'll break it lower to posts with tags with regard to the example, but my use-situation is a little more general (including gradually altering dimensions - http://en.wikipedia.org/wiki/Slowly_changing_dimension).

Suppose you've a posts table, a tags table, along with a post2tag table:

posts (
 id
)

tags (
 id
)

post2tag (
 post_id fkey posts(id),
 tag_id fkey tags(id)
)

I am looking for a few things:

  1. Having the ability to show just how a publish appeared as if in an arbitrary datetime, including for erased rows.
  2. Keep an eye on who's editing what, for an entire audit trail.
  3. Needs some materialized sights ("live" tables) with regard to keeping referential integrity (i.e. logging ought to be transparent towards the designers).
  4. Must be properly fast for live and the most recent draft rows.
  5. Having a draft publish exist together having a live publish.

I have been looking into various options. To date, the very best I have develop (without points #4/#5) looks a little such as the SCD type6-hybrid setup, but rather than getting a present boolean there is a materialized view for that current row. For those intents and reasons, it appears such as this:

posts (
 id pkey,
 public,
 created_at,
 updated_at,
 updated_by
)

post_revs (
 id,
 rev pkey,
 public,
 created_at,
 created_by,
 deleted_at
)

tags (
 id pkey,
 public,
 created_at,
 updated_at,
 updated_by
)


tag_revs (
 id,
 public,
 rev pkey,
 created_at,
 created_by,
 deleted_at
)

post2tag (
 post_id fkey posts(id),
 tag_id fkey tags(id),
 public,
 created_at,
 updated_at,
 updated_by
)

post2tag_revs (
 post_id,
 tag_id,
 post_rev fkey post_revs(rev), -- the rev when the relation started
 tag_rev fkey tag_revs(rev), -- the rev when the relation started
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (post_rev, tag_rev)
)

I am using pg_temporal to keep indexes on period(produced_at, erased_at). And That I keep your various tables synchronized using triggers. Yada yada yada... I produced the triggers that permit to cancel an edit to posts/tags in a way the draft will get saved in to the revs without having to be released. It really works great.

Except when I have to be worried about draft-row related relations on post2tag. For the reason that situation, all hell breaks loose, which hints in my experience that I have some type of design condition in there. But I am drained of ideas...

I have considered presenting data duplication (i.e. n post2tag rows introduced for every draft revision). This type of works, but is commonly a great deal reduced than I'd want it to be.

I have considered presenting drafts tables for that "last draft", but this rapidly has a tendency to become super ugly.

I have considered a variety of flags...

So question: it is possible to generally recognized way of controlling live versus non-live rows consecutively-version controlled atmosphere? And when not, whoever else attempted and been reasonably effective with?

post2tag_revs includes a problem for the reason that it's attempting to express 2 essentially different concepts.

A tag put on a draft publish revision only ever is applicable to that certain revision, unless of course the revision is ever released.

When a tag is released (i.e. connected having a released publish revision), it is applicable to each future revision from the publish until it's suspended.

And associating having a released revision, or unasociating, is not always synchronised having a revision being released, unless of course you unnaturally enforce this by cloning a revision so you are able to connect tag additions or removals...

I'd alter the model by looking into making post2tag_revs.publish_rev only relevant for draft tags. When the revision is released (and also the tag is live), I'd make use of a time stamp column to mark the start and finish from the released validity. You might desire a new post2tag_revs admission to represent this transformation.

While you explain, this will make this relationship bi-temporal. You may improve performance within the "normal" situation with the addition of a boolean to post2tag to point the tag is presently connected using the publish.