时间数据库设计，带扭曲（实时vs草稿行） [英] Temporal database design, with a twist (live vs draft rows)

问题描述

我正在寻找实现对象版本控制与需要有活的和草稿对象的额外的扭曲，并可以使用从某人的经验在这一点，因为我开始怀疑，如果它是可能没有潜在可怕的黑客。

I'm looking into implementing object-versioning with the added twist of needing to have both live and draft objects, and could use the insights from someone experience in this, as I'm beginning to wonder if it's even possible without potentially horrific hacks.

为了这个例子，我将它分解为标签的帖子，但我的用例是更一般的（涉及缓慢变化的维度 - http://en.wikipedia.org/wiki/Slowly_changing_dimension ）。

I'll break it down to posts with tags for the sake of the example, but my use-case is a bit more general (involving slowly changing dimensions - http://en.wikipedia.org/wiki/Slowly_changing_dimension).

假设你有一个posts表，一个tags表和一个post2tag表：

Suppose you've a posts table, a tags table, and a post2tag table:

posts (
 id
)

tags (
 id
)

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

< m需要一些事情：

I'm in need of a couple of things:

1. 能够精确显示某个帖子在任意日期时间的样子，包括删除的行


2. 需要一组实体化视图（实时表格）才能查看完整的审核记录。
为了保持引用完整性（即

1. Being able to show exactly how a post looked like at an arbitrary datetime, including for deleted rows.
2. Keep track of who is editing what, for a complete audit trail.
3. Needs a set of materialized views ("live" tables) for the sake of keeping referential integrity (i.e. logging should be transparent to the developers).
4. Needs to be appropriately fast for live and the latest draft rows.
5. Being able to have a draft post coexist with a live post.

我一直在调查各种选项。到目前为止，我想出的最好的（没有点＃4 /＃5）看起来有点像SCD type6-hybrid设置，但是没有一个当前布尔值有一个物化视图为当前行。对于所有的意图和目的，它看起来像这样：

I've been investigating various options. So far, the best I've come up with (without points #4/#5) looks a bit like the SCD type6-hybrid setup, but instead of having a current boolean there's a materialized view for the current row. For all intents and purposes, it looks like 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)
)

我使用pg_temporal来保持期间的索引（created_at，deleted_at）。我使用触发器保持各个表同步。 Yada yada yada ...我创建了触发器，允许取消对帖子/标签的编辑，使得草稿存储在转换中而不发布。当我需要担心post2tag上的draft-row相关关系时，它工作得很好。

I'm using pg_temporal to maintain indexes on period(created_at, deleted_at). And I keep the various tables in sync using triggers. Yada yada yada... I created the triggers that allow to cancel an edit to posts/tags in such a way that the draft gets stored into the revs without being published. It works great.

在这种情况下，所有的地狱都松了，这暗示着我有一些设计问题。

Except when I need to worry about draft-row related relations on post2tag. In that case, all hell breaks loose, and this hints to me that I've some kind of design problem in there. But I'm running out of ideas...

我考虑过引入数据重复（即为每个修订草案引入n个post2tag行）。这种作品，但往往比我想要的慢得多。

I've considered introducing data duplication (i.e. n post2tag rows introduced for each draft revision). This kind of works, but tends to be a lot slower than I'd like it to be.

我考虑介绍草稿表的最后草稿，

I've considered introducing drafts tables for the "last draft", but this quickly tends to become very very ugly.

我考虑过各种各样的标志...

I've considered all sorts of flags...

所以问题：在行版本控制的环境中是否有一种普遍接受的管理活动和非活动行的方法？

So question: is there a generally accepted means of managing live vs non-live rows in a row-version controlled environment? And if not, what have you tried and been reasonably successful with?

推荐答案

我想我已经钉住了。基本上，您将一个（唯一）草案字段添加到相关表格，并且您对草稿进行处理，就好像它们是一个新的帖子/标记/ etc：

I think I nailed it. Basically, you add a (unique) draft field to the relevant tables, and you work on the drafts as if they were a new post/tag/etc.:

posts (
 id pkey,
 public,
 created_at stamptz,
 updated_at stamptz,
 updated_by int,
 draft int fkey posts (id) unique
)

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

tags (
 id pkey,
 public,
 created_at,
 updated_at,
 updated_by,
 draft fkey tags (id) unique
)


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

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

post2tag_revs (
 post_id,
 tag_id,
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (post_id, tag_id, created_at)
)

这篇关于时间数据库设计，带扭曲（实时vs草稿行）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！