常见数据库设计(2)——历史数据问题之单记录变更

在各种应用软件中，客户总是希望看到自己操作关键业务的历史数据（更或者是将来的历史数据，如本年计划明年的商品价格），并且要跟踪变化来源于哪一个版本。历史记录，如果我们按某次修改时，需要新增的记录条件的角度来看，如果只需要新增一条记录（如商品价格的变动，一次只变动），我们称之为单记录变更；如果我们需要新增一条记录，并且还需要在不同的表中新增对应的详细记录并且是一对多的关系时（如报价时，我们需要储存报价流水和报价物品清单列表），我们称之为多记录变更。

一，单记录变更、无储存未来历史记录的需求，储存于单表中

付款计划PayPlan

字段名类型是否可空中文名描述

id char(36) no guid

...其它属性...

num int no 版本号在某个项目中递增

is_use int no 是否启用默认0否，1是

use_date datetime yes

ischeck int no 是否确认默认0未确认，1确认

checker char(36) yes 确认人

check_date datetime yes 确认日期

说明：用户添加一条数据，未确认时，可以修改、删除。但是当用户确认时（当项目使用工作流时，也可以用工作流替换确认的3个字段），

更新is_use为1（是），并且更新操作人信息。在用户确认完之后，不能添加，修改、删除。需要修改时，则将原有数据复制一份（除主键外），并设置版本号加1,设置is_use和ischeck=0，设置use_date、checker和check_date为NULL，然后修改操作在新的版本中进行，并且系统中使用的依然是前一个版本的数据。当修改流水，确认后，需要先将本类其它的is_use改为0，并且更新自己的is_use为1及其它信息。

二，单记录变更、无储存未来历史记录的需求，储存于多表中（一个主表存储在用记录，另一个子表储存历史记录）

在上一个方案中，[单记录变更、无储存未来历史记录的需求，储存于单表中]，如果当变更频率高中，表中的数据量增大，为了获取在用的那条记录（客户是常用到的就是这个），查询时间会浪费在很多无用的记录上。为了解决无关的数据问题，我们将在用的数据储存于主表中，而变更的历史，储存于子表，这样我们在获取在用记录时，就去除了很多无用的数据。

付款计划PayPlan

字段名类型是否可空中文名描述

id char(36) no guid

...其它属性...

num int no 版本号在某个项目中递增

is_use int no 是否启用默认0否，1是

ischeck int no 是否确认默认0未确认，1确认

checker char(36) yes 确认人

check_date datetime yes 确认日期

付款计划PayPlanVar

字段名类型是否可空中文名描述

id char(36) no guid

pay_plan_Id char(36) no 付款计划编号，主表的编号

...其它属性...

num int no 版本号在某个项目中递增

is_use int no 是否启用默认0否，1是

ischeck int no 是否确认默认0未确认，1确认

checker char(36) yes 确认人

check_date datetime yes 确认日期

说明：用户添加一条数据，未确认时，可以修改、删除。但是当用户确认时（当项目使用工作流时，也可以用工作流替换确认的3个字段），

更新is_use为1（是），并且更新操作人信息。在用户确认完之后，不能添加，修改、删除。第一次修改时，将主表（PayPlan）数据复制一份到PayPlanVar中，当然也要将版本号加1以及其它状态信息还原，在确认后，将主表再复制一份到历史表中，用作历史数据，然后将本次修改的数据，更新到主表中去，并更新主表的版本号等信息。

第一次修改以后，再需要修改数据，将主表数据复制到子表中，同样版本号加其它信息还原，但在确认后只需要将版本等信息更新回主表即可。

三，单记录变更、有储存未来历史记录的需求，储存于单表

如，当某个供应商在今年就定出明年的商品价格，如果我们商品价格使用的第一或第二种设计方案，我们不得不在明年手工并且在确定的时间内更新价格表。

商品价格表(GoodsPrice)

字段名类型是否可空中文名描述

id char(36) no guid

...其它属性...

num int no 版本号

start_time datetime no 开始生效日期

end_time datetime no 结束生效日期