如何设计n:m关系的历史记录

Question

问题：

我有一个表 A 和另一个表 B 之间的 n:m 关系，并且需要完整记录这个关系的历史，换句话说，我必须能够重现过去任何时刻的状态。

想法：

我的第一个尝试是将关系拆解为1:m和m:n关系，通过插入虚拟表来保存关系在任何给定时间发生更改时的状态。就像这样:

TABLE A         TABLE B
--------        ---------
ID   INT        ID    INT                   

TABLE HIST             TABLE CROSS_REF
----------             -----------------
ID        INT          REF_B_ID      INT
REF_A_ID  INT          REF_HIST_ID   INT
VERSION   TIMESTAMP

优点： 容易查询所需信息。 缺点： 这种尝试会创建大量冗余数据，特别是当"B"-Side很大且只有小的更改时。（例如在使用案例“公司-员工”中）

第二种方法是维护两个n:m关系，一个用于当前状态，另一个用于发生的更改。

TABLE A         TABLE B
--------        ---------
ID   INT        ID    INT                   

TABLE CHANGES          TABLE CROSS_REF
----------             -----------------
REF_A_ID     INT          REF_A_ID      INT
REF_B_ID     INT          REF_B_ID   INT
ACTION_TIME  TIMESTAMP
ACTION_TYPE  VARCHAR

< p > 优点：没有冗余数据。 缺点：难以查询过去特定时间点的状态。

问题

• 你有没有其他建议可以为这个应用程序设计数据模型？
• 如果没有真正更好的方法来解决这个问题：我该怎么办？或者是否主要取决于给定的用例，就像上面描述的例子一样？
• 如果我采用第二种方法，如何查询过去某个特定时间点的状态？我能够在客户端解决这个问题，但我想知道数据库方面的解决方案。（PL / SQL 也可行）

- stg

Answer 1

这个常用模型怎么样？

create table cross_ref
  ( a_id    references a
  , b_id    references b
  , from_ts timestamp
  , to_ts   timestamp
  , primary key (a_id, b_id, from_ts)
  );

（注意，我使用了您的 timestamp ，通常我会使用 date ）

请将内容翻译为中文并保留HTML标签。

- Tony Andrews

---

谢谢您的建议，确实这是一个好主意。最终我想使用JPA来进行ORM，所以可能使用这种方法会有些棘手，但无论如何，这是进一步考虑的良好起点。 - stg

Tony，我和OP有同样的问题。如果我理解你的答案正确，你建议使用一个单独的表来保存某个时间点的关系。我不明白from和to是什么？你用它们来跟踪记录的有效性吗？此外，我需要存储整个记录的历史，而不仅仅是关系，我在几个地方读到应该使用创建一个仅存储更新列的历史表的方法。将您的解决方案添加到其中，我需要2个单独的表还是可以使用同一个表并添加额外的字段？对于我的情况是否有更好的解决方案？ - eestein

@eestein，from_ts和to_ts列的目的是根据OP的要求“能够重现过去任何时刻的状态”。因此，您可以查询select b_id from cross_ref where a_id = 1 and date '2015-01-01' between from_ts and to_ts来查找在该日期与A相关联的B。 - Tony Andrews

@TonyAndrews 好的，目前我不需要那个......你觉得将你的解决方案与我提到的方案合并会是我的最佳选择吗？我会有一个表，其中包含 tb1_id、tb2_id、column_name 和新旧值的列。你有什么想法？感谢你的时间 :) - eestein

关于存储“历史记录”，这取决于您的要求。对于安全审计跟踪（“用户Joe在2015年7月29日11:23将员工123的薪水更改为20000”），您可以使用通用表格，其中包含行（表名、列名、键、旧值、新值、用户、日期时间）。但是，如果是为了回答业务问题，则具有原始记录的所有列以及从/到日期的历史记录可能更好。有时会将其合并，即当前记录也在“历史记录”（仅）表中，其中from=有效时，to=null。 - Tony Andrews

好的，我明白了。我在 Stack Overflow 上读到了“安全审计跟踪”的内容，但现在我正在寻找修订/版本历史记录。用户应该能够将记录恢复到特定的过去版本。我开始创建一个不同的表格，其中包含主表格的所有列以及您提到的一些额外列，但后来我读到这不是最好的方法，因为很多字段不会被使用并且占用大量空间...但我的主要关注点是如何保存整个记录和相关数据。我正在考虑使用 XML 来保存它们，这是我目前的解决方案，如果您有任何想法 :/ - eestein