问题:
我有一个表 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 也可行)
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 Andrewstb1_id
、tb2_id
、column_name
和新旧值的列。你有什么想法?感谢你的时间 :) - eestein