sql - 时态数据库设计，有一个转折(实时行与草稿行)

Question

我正在研究实现对象版本控制，同时需要同时拥有事件对象和草稿对象，并且可以利用某人在这方面的经验的见解，因为我开始怀疑它是否可能没有潜在的可怕的黑客攻击。

为了示例，我将其分解为带有标签的帖子，但我的用例更一般(涉及缓慢变化的维度 - http://en.wikipedia.org/wiki/Slowly_changing_dimension)。

假设您有一个 posts 表、一个 tags 表和一个 post2tag 表:

posts (
 id
)

tags (
 id
)

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

我需要一些东西:

1. 能够准确显示帖子在任意日期时间的样子，包括已删除的行。
2. 跟踪谁在编辑什么，以获得完整的审计线索。
3. 需要一组物化 View (“实时”表)以保持参照完整性(即日志记录应该对开发人员透明)。
4. 对于实时 和 最新的草稿行，需要适当的快。
5. 能够让草稿帖子与实时帖子共存。

我一直在研究各种选择。到目前为止，我想出的最好的(没有点#4/#5)看起来有点像 SCD type6-hybrid 设置，但是没有当前 bool 值，而是当前行的物化 View 。出于所有意图和目的，它看起来像这样:

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 ...我创建了触发器，允许取消对帖子/标签的编辑，这样草稿就可以存储在 revs 中而不被发布。效果很好。

Except 当我需要担心 post2tag 上的草稿行相关关系时。在那种情况下，一切都乱套了，这暗示我那里有某种设计问题。但我的想法已经用完了......

我考虑过引入数据重复(即为每个修订草案引入 n 个 post2tag 行)。这种方法有效，但往往比我希望的要慢很多。

我考虑过为“最后的草稿”引入草稿表，但这很快就会变得非常难看。

我考虑过各种标志...

所以问题是:在行版本控制的环境中，是否有一种普遍接受的方法来管理事件行与非事件行？如果没有，您尝试过什么并取得了一定的成功？

Answer 1

Anchor modeling是实现时间 dB 的好方法——参见 Wikipedia article 也是。需要一些时间来适应，但效果很好。有一个 online modeling tool 如果您加载提供的 XML 文件 [文件 -> 从本地文件加载模型]您应该会看到类似这样的内容——也可以使用 [Layout --> Togle Names]。

[Generate --> SQL Code] 将为表、 View 和时间点函数生成 DDL。代码比较长，这里就不贴了。检查代码——您可能需要修改它为您的数据库。

这是加载到建模工具中的文件。

<schema>
<knot mnemonic="EXP" descriptor="Expired" identity="smallint" dataRange="char(1)">
<identity generator="true"/>
<layout x="713.96" y="511.22" fixed="true"/>
</knot>
<anchor mnemonic="US" descriptor="User" identity="int">
<identity generator="true"/>
<attribute mnemonic="USN" descriptor="UserName" dataRange="varchar(32)">
<layout x="923.38" y="206.54" fixed="true"/>
</attribute>
<layout x="891.00" y="242.00" fixed="true"/>
</anchor>
<anchor mnemonic="PO" descriptor="Post" identity="int">
<identity generator="true"/>
<attribute mnemonic="TIT" descriptor="Title" dataRange="varchar(2)">
<layout x="828.00" y="562.00" fixed="true"/>
</attribute>
<layout x="855.00" y="471.00" fixed="true"/>
</anchor>
<anchor mnemonic="TG" descriptor="Tag" identity="int">
<identity generator="true"/>
<attribute mnemonic="TGT" descriptor="TagText" dataRange="varchar(32)">
<layout x="551.26" y="331.69" fixed="true"/>
</attribute>
<layout x="637.29" y="263.43" fixed="true"/>
</anchor>
<anchor mnemonic="BO" descriptor="Body" identity="int">
<identity generator="true"/>
<attribute mnemonic="BOT" descriptor="BodyText" dataRange="varchar(max)">
<layout x="1161.00" y="491.00" fixed="true"/>
</attribute>
<layout x="1052.00" y="465.00" fixed="true"/>
</anchor>
<tie timeRange="datetime">
<anchorRole role="IsTagged" type="PO" identifier="true"/>
<anchorRole role="IsAttached" type="TG" identifier="true"/>
<anchorRole role="BYAuthor" type="US" identifier="false"/>
<knotRole role="Until" type="EXP" identifier="false"/>
<layout x="722.00" y="397.00" fixed="true"/>
</tie>
<tie timeRange="datetime">
<anchorRole role="Contains" type="PO" identifier="true"/>
<anchorRole role="ContainedIn" type="BO" identifier="false"/>
<layout x="975.00" y="576.00" fixed="true"/>
</tie>
<tie>
<anchorRole role="CreatedBy" type="TG" identifier="true"/>
<anchorRole role="Author" type="US" identifier="false"/>
<layout x="755.10" y="195.17" fixed="true"/>
</tie>
<tie>
<anchorRole role="CreatedBy" type="PO" identifier="true"/>
<anchorRole role="Author" type="US" identifier="false"/>
<layout x="890.69" y="369.09" fixed="true"/>
</tie>
<tie>
<anchorRole role="ModifiedBy" type="BO" identifier="true"/>
<anchorRole role="Author" type="US" identifier="false"/>
<layout x="1061.81" y="322.34" fixed="true"/>
</tie>
</schema>