常见数据库设计(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      结束生效日期


今天就先总结到这，关于多记录变更，下次再总结。欢迎大家拍砖讨论。。。