数据库设计 – 没有历史表的数据库版本控制

我正在通过这个 post进行表格的记录级版本控制.我注意到该架构处理历史表的使用.但是,我的方案不需要回滚,但检索时间点记录.这是我尝试使用单个表进行版本控制的设计.请注意,这是一个裸骨表数据(没有约束,索引等).我打算基于id进行索引,因为这涉及列上的group by子句.

例如,我有一个表测试在哪里

id is the identifier,

modstamp is the timestamp of the data (never null)

除上述列外,该表还包含簿记列

local_modstamp is the timestamp at which the record was updated

del_modstamp is the timestamp at which the record was deleted

在备份期间,所有记录都从源获取并插入到记录将具有值local_modstamp = null和del_stamp = null的位置.

id |modstamp                   |local_modstamp |del_modstamp |
---|---------------------------|---------------|-------------|
1  |2016-08-01 15:35:32 +00:00 |               |             |
2  |2016-07-29 13:39:45 +00:00 |               |             |
3  |2016-07-21 10:15:09 +00:00 |               |             |

获得记录后,这些是处理数据的方案(假设参考时间[ref_time]是进程运行的时间)：

>正常插入.
>更新：使用local_modstamp = ref_time更新最新记录.然后插入新记录.
查询将是：
更新测试集local_modstamp =其中id =和local_modstamp不为null且del_modstamp不为null
插入测试值(…)
>删除：使用del_modstamp = ref_time更新最新记录.
更新测试集del_modstamp =其中id =和local_modstamp不为null且del_modstamp不为null

该设计旨在获取local_modstamp不为null且del_modstamp不为null的最新记录.
但是,我遇到了一个问题,我打算使用查询(最内层查询)检索时间点：

select id,max(modstamp) from test where modstamp <= <ref_time> and (del_modstamp is null || del_modstamp <= <ref_time>) group by id;

似乎我犯了一个错误(我有吗？)使用null作为占位符来识别表的最新记录.有没有办法使用现有的设计来获取时间点记录？

如果没有,我想可能的解决方案是将local_modstamp设置为最新记录.这将需要在更新的情况下使用max(local_modstamp)更新逻辑.我可以坚持现有的架构来检索时间点数据吗？

我现在正在使用SQL-Server,但这种设计也可以扩展到其他数据库产品.我打算使用更通用的方法来检索数据,而不是使用供应商特定的hacks.

解决方法

create table Entities(
    ID     int identity primary key,S1     [type],-- Static data
    Sn     [type],-- more static data
    V1     [type],-- Volatile data
    Vn     [type]   -- more volatile data
);

静态数据是在实体的生命周期内不会更改或不需要跟踪的数据.必须跟踪易失性数据更改和这些更改.

将volatile属性移动到单独的表：

create table EntityVersions(
    ID        int  not null,Effective date not null default sysdate(),Deleted   bit  not null default 0,V1        [type],Vn        [type],constraint PK_EntityVersions primary key( ID,Effective ),constraint FK_EntityVersionEntity foreign key( ID )
        references Entities( ID )
);

实体表不再包含易失性属性.

插入操作使用静态数据创建主实体记录,从而生成唯一ID值.该值用于插入具有易失性数据初始值的第一个版本.更新通常对主表没有任何作用(除非实际更改了静态值),并且新版本的新易失性数据被写入版本表.请注意,不会对现有版本进行任何更改,尤其是最新版本或“当前版本”.插入新版本,结束操作.

要“撤消”最新版本或任何版本,只需从版本表中删除该版本即可.

例如,具有以下属性的Employees表：

EmployeeNum,HireDate,FirstName,LastName,PayRate,Dept,PhoneExt

当然,EmployeeNum与HireDate和FirstName一起是静态的. PhoneExt可能会不时更改,但我们不在乎.所以它被指定为静态.最终的设计是：

Employees_S
===========
  EmployeeNum (PK),PhoneExt

Employees_V
===========
  EmployeeNum (PK),Effective (PK),IsDeleted,Dept

2016年1月1日,我们聘请了Sally Smith.静态数据插入到Employees_S中,生成EmployeeNum值1001.我们使用该值也插入第一个版本.

Employees_S
===========
  1001,2016-01-01,Sally,12345

Employees_V
===========
  1001,Smith,35.00,Eng

3月1日,她获得加薪：

Employees_S
===========
  1001,Eng
  1001,2016-03-01,40.00,Eng

5月1日,她结婚了：

Employees_S
===========
  1001,2016-05-01,Jones,Eng

请注意,除了生效日期不能相同的限制之外,同一实体的版本完全相互独立.

要查看员工1001的当前状态,请查看以下内容：

select  s.EmployeeNum,s.HireDate,s.FirstName,v.LastName,v.PayRate,v.Dept,s.PhoneExt
from    Employees_S s
join    Employees_V v
    on  v.EmployeeNum = s.EmployeeNum
    and v.Effective = ( select  Max( Effective )
                        from    Employees_V
                        where   EmployeeNum = v.EmployeeNum
                            and Effective <= SysDate() )
where   s.EmployeeNum = 1001
    and v.IsDeleted = 0;

这是很酷的部分.要查看员工1001的状态,比如2月11日,这是查询：

select  s.EmployeeNum,s.PhoneExt
from    Employees_S s
join    Employees_V v
    on  v.EmployeeNum = s.EmployeeNum
    and v.Effective = ( select  Max( Effective )
                        from    Employees_V
                        where   EmployeeNum = v.EmployeeNum
                            and Effective <= '2016-02-11' )
where   s.EmployeeNum = 1001
    and v.IsDeleted = 0;

它是相同的查询 – 除了子查询的最后一行.当前和历史数据驻留在同一个表中,并使用相同的语句进行查询.

这是另一个很酷的功能.这是7月1日,我们知道,在9月1日,Sally将转移到营销部门,另一次加薪.文书工作已经完成.继续并插入新数据：

Employees_S
===========
  1001,2016-09-01,50.00,Mkt

倒数第二个版本仍将显示为当前版本,但在9月1日或之后执行的第一个查询将显示营销数据.

Here是我在技术博览会上做过几次演示的幻灯片.它包含有关如何完成上述所有操作(包括查询)的更多详细信息. here是一个更详细的文档.

（编辑：瑞安网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!