转载——防止基础表数据变动,导致相关的历史记录数据产生变动的解决方案
转载——防止基础表数据变动,导致相关的历史记录数据产生变动的解决方案
原文链接:http://www.cnblogs.com/surfsky/archive/2009/11/06/1597242.html
1.首先先定义两个概念:
- 基础表:一般是维度表,如用户、部门、产品等基础信息表。
- 记录表:其它有依赖于基础表的表就暂且称之为记录表吧。
2.问题的提出:
- 若不做特殊处理,基础表数据的变动,会导致相关的记录表历史数据无法反映历史原貌。如产品价格更改等,查某张历史订单,发现其产品信息已经不存在或者由新记录替代,已经不能如实反映历史状态。
3.可选解决方案:
3.1采用记录归档方式
- 步骤
新建归档记录表,原id字段有一个对应的name字段
新建和流转时都采用id字段
完成后将记录拷贝到归档记录表,并填充name字段 - 优点
基础表无需变动。归档逻辑清晰,字段清晰。
可以针对完成的单子做物理及索引上的查询优化 - 缺点
冗余程度高
查询时要区分历史表还是新表
3.2采用字段冗余方式
- 步骤
id和name字段都并存
新建时根据id自动填写name字段
流转时使用id字段
完成后使用name字段 - 优点
基础表无需变动。记录表冗余度低。 - 缺点
还是有字段冗余,对于多外键的表冗余度就大了
看起来表字段设计得很外行_
3.3基础表采用数据仓库式的历史表方案
- 步骤
基础表多两个字段: startdate,enddate
基础表不允许物理删除记录,删除时只是将enddate写为now
任何对基础表关键信息的变更都将新增一条记录,将老记录enddate设置为now,新录startdate为now - 优点
记录表无需变动
任何时间点的基础表数据都可以查询到 - 缺点
查询麻烦:任何和基础表关联的操作都必须用时间段表达式,不容易理解且代码繁杂。如:
查询当前的用户信息:where dmn_user.enddate = date '3000-12-31'
查询2009-01-01时的用户信息:where dmn_user.startdate >= date '2009-01-01' and dmn_user.enddate <= date '2009-01-01'
3.4基础表采用RowId+Status方案
- 步骤
基础表都有以下字段: Id, RowId,Status。Id是可重复编号,RowId是自动创建且唯一主键,供它表关联。
基础表不允许物理删除记录,删除时只是将Status设置为“Deleted”
任何对关键信息的变更都将新增一条记录,将老记录status设置为old or deleted,新记录status为inuse or normal - 优点
更改代价小,逻辑好理解 - 缺点
将所有关联到id的操作都迁移到rowid,调试有工作量(不过也是在允许范围内)
结论:
这4种方案都有实际应用场合。
方案一:常用于很严谨的场合,归档表只准插入查询,不允许任何更改。如历史订单、档案。
方案二:这个方案其实很常用,是一种折衷的适当冗余的方案。但不太适合有很多外键的场合。
方案三:常用于数据仓库,可以查询到任一时刻的历史信息(很强大),缺点就是对不便对id做查询优化,不适合OLTP类型的系统(个人看法)。
方案四:可适用于各种场合。历史数据稳定,查询便利,且不影响性能。
相对而言,方案四较为稳妥且实施便利,推荐之。