当前位置：首页 > 编程日记 > 正文

从AdventureWorks学习数据库建模——保留历史数据

编程日记 2024-10-21 00:20:00

在业务需求中，经常需要我们在系统中能够记录历史信息，能够查看到历史变动情况，这时我们可以通过增加开始结束时间字段来记录数据的历史版本。对数据的历史记录主要分为：关系、属性历史，实体历史和变更历史。

关系、属性历史记录

所谓关系历史记录就是指两个实体之间的关系存在历史版本。比如部门表和员工表，对于某一个时刻来说，一个部门有多个员工，一个员工只属于一个部门，所以是个一对多的关系。而我们希望把这个关系记录下历史变动，那么就会形成多对多关系。多对多关系就形成中间表，然后我们在中间表上加入“开始时间”字段和“结束时间”字段即可记录这个关系的历史。

对某个实体的属性记录历史记录会形成一对多的关系表，比如产品价格属性，我们希望把所有历史定价都记录下来，那么就会形成产品和价格一对多的关系。

在AdventureWorks数据库中，我们可以看到大量的这种记录关系历史的设计。比如：

员工、部门、轮班的历史记录：

NewImage

这就是前面提到的一对多关系因为记录历史变为多对多关系的例子。

产品对成本和售价的历史记录：

NewImage

这就是典型的属性历史记录，对于产品的众多属性，我们之关系成本和售价这两个属性的历史，所有可以建立一对多关系的价格历史表。

销售和区域以及销售配额的历史记录：

NewImage

区域和销售本来也是普通的一对多关系，一个销售属于某个片区，一个区域对应多个销售。现在由于历史记录，所以形成多对多的关系表SalesTerritoryHistory。而对于销售配额，因为是记录到季度的，一季度只有一个销售配额，所以不需要开始时间和结束时间，只需要一个季度第一天即可（结束时间是可以根据这个季度的第一天而计算出来的，所以不需要再存储）。

区域与销售人员的关系在增加了中间表形成多对多后，仍然保留了原来的一对多关系，从数据上来看不是这样的，因为两个表的数据是不一致的，所以我推断这是另外一个一对多关系，而不是原来的区域和销售的分配对应关系表。

小结：

当需要对关系或属性记录历史时，会把关系提升一个复杂度，也就是说原来是一对一的，现在会变成一对多，原来是一对多的，现在会变成多对多。在历史记录表中增加“开始时间”和“结束时间”两个字段来表示该行数据的时间有效性。AdventureWorks数据库中使用了NULL值设为“结束时间”来表明这条数据是当前有效的，但是笔者并不推荐这么做，最好是把两个字段都设置为NOT NULL，在比较时可以得到统一的查询语句：

where @d between StartDate and EndDate

另外SalesTerritoryHistory这个表只记录“开始时间”而不记录“结束时间”这也是一个不好的设计，虽然结束时间是可以计算出来的，但是每次查询的时候还需要去计算结束时间，真不是一个好方法。最好是把两个字段都保留，用户只需要输入开始时间，由前端程序去初始化结束时间，然后一并保存。

实体历史记录

主实体历史记录

实体的历史记录是指对一个实体数据的任何更改，都把整条数据都产生一条新记录，而不是只针对某个属性或者关系。对实体进行历史记录，我们也可以采用添加开始时间结束时间的方式，但是更多的时候我们对整个实体记录历史并不是为了随时查询历史上某个时间点这个实体的值，而是为了记录一个“版本Version”信息，方便在审计某个实体的变更时对比。如果我们是出于审计的需要而记录的历史版本，那么这些历史数据平时是不会参与到业务查询中的，所以并不需要记录开始时间，结束时间，取而代之的，我们可以增加“版本”字段，当然还有审计用到的“最后更新时间”和“最后更新人”，

这样就实体的变化情况，如果我们仅仅是增加Version字段，在查询当前版本时会很麻烦，因为我们必须拿到最高的那个版本号，然后才能把这个最新版本的记录作为当前记录，为了优化这个性能问题，我们一般还需要再添加布尔型的“是否当前版本IsCurrent”字段来标识当前版本。增加了这个字段后，那么在更改实体数据时就会更麻烦一些。首先需要将老数据版本号获得，+1生成新的版本号，然后将老数据的“是否当前版本”字段置为0，更新老数据的“最后更新时间”和“最后更新人”，然后插入新版本号的数据，而且新版本是当前版本。我在AdventureWorks数据库中并没有看到关于实体的历史记录的设计，不过我们可以看SharePoint的数据库设计，就是采用我这里提到的版本设计的方法。有兴趣的可以查看一下SharePoint的ContentDB的AllUserData表，tp_Version就是记录版本的，tp_IsCurrent和tp_IsCurrentVersion就是标记当前版本的。

NewImage

附属实体的历史记录

在进行实体历史记录时，还面临的一个问题是，附属的子实体是否也需要一并进行历史记录。比如我们要对采购订单这么一个实体进行历史记录，每次对采购订单的修改都会生成一个新版本的采购订单。如果一个采购订单下面有100条采购明细，那么我们在编辑了采购订单主表后，创建了新版本的采购主表数据，是否对这100条明细也创建对应的新版本数据呢？如果创建，那么采购明细表的数据量就会飞涨，而且实际上我们这里并没有编辑这100条明细，新版本的明细数据是一模一样的，如果不创建，那么怎么保持这种外键约束呢？毕竟明细表上面的外键对应的可是老版本的采购订单的ID啊！

NewImage

其实两种方案都可以，第一种方案开发简单，如果明细并不是那么多，或者本身单据的数据量并不大，那么重复一点明细表并不会带来太大的影响。第二种方案开发会很复杂，需要新老数据逐条对比，找到差异，如果主表有更改，那么为主表创建新版本，如果100条明细中有2条更改，那么就为这2条创建新版本。

NewImage

下面详细说一下采用第二种的解决方案的模型设计。首先，我们需要断开主表和附属表的外键，将Form和Item作为两个独立的实体，各自添加“版本”，“是否当前版本”等属性。为Form添加业务主键“FormNumber”，用于唯一标识一个表单（由于版本记录的原因，所以FormNumber不是Form的主键），然后在Item表中添加“FormNumber”，用于标识这些Item是属于哪个表单。

select *
from Form 
where IsCurrent=1 and IsDeleted=0 and FormNumber=@formNumber;
select *
from Item 
where IsCurrent=1 and IsDeleted=0 and FormNumber=@formNumber;

变更历史记录

无论前面讲到的对关系，属性还是整个实体的历史记录，都会在业务表中形成新的数据，数据的增加一方面会导致查询的效率变低，另一方面也使得每次查询时都需要带上额外的查询条件，非常不方便。于是我们想到了另一种保存历史记录的方式，那就是我们像记录日志一样，把变更了的部分记录到日志表中。

记录变更日志的好处是不影响现有数据库模型的设计，也就是说所有实体和关系都不需要改，我们只需要增加一个变更日志表即可。但是变更日志一般是前端程序通过对比前后记录，找到变更的属性，然后写入的，并不是数据库做的事。坏处也显而易见，那就是还原历史数据不方便，不能像前面的模型那样可以快速的查询数据的历史状态。

NewImage

所以变更日志表这种处理方式只用于审计的需求，而不能用于业务上要对历史数据的查询需求。在AdventureWorks数据库中有一个TransactionHistory表，用于记录各个订单事务的，虽然不是记录订单变更的，但是也有和变更历史记录类似的结构。

历史数据查询优化

前面提到由于保留历史数据的原因，所以会将数据库中对应表的数据量增加很多倍，数据量的增加必然导致查询变慢，所以我们在记录历史数据后很有必要对表进行查询优化。优化可以采用以下解决方案：

归档表

如果我们的历史数据在平时的业务中并不需要，只有在特殊场景才会用到历史数据表，那么我们可以将历史数据表建立一模一样结构的归档表，然后定时将业务系统中的历史数据转移到归档表中。当然，前端软件系统也要做对应的修改，对于老的历史数据需要查询归档表，而新的数据是查询当前表。在AdventureWorks只对TransactionHistory就建立了对应的归档表。

NewImage

分区

建立分区比归档表的好处是在物理上，老数据和新数据可以存储在不同的地方，新老数据可以各自建立各自的索引树，而在逻辑上对程序来说仍然是访问一个表，前端程序不需要做什么修改。比如对于开始结束日期的历史数据记录方式，我们可以把结束日期为9999-12-31的数据（当前有效数据）分到一个区，剩下的分到另一个区。对于版本记录的方式，我们可以将“是当前版本”分到一个区，把其他的数据分到另一个区。

分区后在更新数据时会导致老数据的区块转移，因为老数据本来是在Current区块的，现在由于更改了实体，老数据需要转移到Old区块，然后将新数据插入到Current区块，除了分区的移动还有对应的索引的变动，所以更新数据时会相对慢一些。

索引

如果对于Oracle数据库，那么我们可以对IsCurrentVersion字段建立位图索引，如果是SQL Server这种不支持位图索引的数据库，那么我们也可以在建立B树索引时把IsCurrentVersion放在第一列，因为这个列是必然放入过滤条件的。

https://www.dkcj.cn/info/23939.html

关系、属性历史记录

实体历史记录

主实体历史记录

附属实体的历史记录

变更历史记录

历史数据查询优化

归档表

分区

索引

相关文章：