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:
- Having the ability to show just how a publish appeared as if in an arbitrary datetime, including for erased rows.
- Keep an eye on who's editing what, for an entire audit trail.
- Needs some materialized sights ("live" tables) with regard to keeping referential integrity (i.e. logging ought to be transparent towards the designers).
- Must be properly fast for live and the most recent draft rows.
- 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.