数据库模型设计——历史与版本设计
原文地址 https://www.cnblogs.com/studyzy/p/3310266.html
在企业数据库设计中，经常会遇到一个需求，就是希望把操作之前的数据保留下来，能够看到操作之前是什么数据，操作之后是什么数据。对于这种需求，我们可以使用保留历史数据或者使用版本来实现。
为了能够保留历史数据，在版本设计时有以下方案：
一、使用版本号
版本号是一种常见的版本设计方案，就是在要进行历史数据保留的表上面增加一个版本号字段，该字段可以是 DateTime 类型，也可以是 int 类型，每进行数据操作时，都是创建一个新的版本，版本是只增不减的，所以只需要拿到最大一个版本号，就能得到最新的业务数据。
版本号除了能够用于留存历史数据外，还有一个功能就是避免并发编辑操作。比如我们有一个对象 A，当前的版本是 1，两个用户同时打开了该对象的编辑页面，进行数据更改。先是甲用户提交更改，这个时候系统把对象的 ID 和版本进行查询，发现要修改的数据最新版本是 1，所以成功修改，保存了对象 A 的新版本 2。这个时候用户乙也提交了修改。系统把对象的 ID 和版本 1 进行查询，发现要修改的数据最新版本是 2，不符合要求，所以拒绝用户乙的修改。用户乙只有刷新界面，拿到最新的版本 2，再进行修改。
| ID | 单号 | 金额 | 版本号 |
| 1 | EXP123 | 100 | 1 |
在使用版本号的情况下，对单据的金额进行修改，修改后创建新的版本号 2：
| ID | 单号 | 金额 | 版本号 |
| 1 | EXP123 | 100 | 1 |
| 2 | EXP123 | 120 | 2 |
二、使用生效、失效时间
保存历史数据的第二办法是使用生效失效时间来表示一个版本。要进行历史数据记录的表增加 “生效时间”“失效时间” 两个字段，两个字段不允许为空。对于刚创建的数据，生效时间是创建该数据的时间，失效时间是 9999-12-31。现在对这条数据进行了修改，那么我们只需要将当前时间设置为上一个版本的失效时间，同时创建一条新数据，生效时间是当前时间，失效时间是 9999-12-31 即可。
| ID | 单号 | 金额 | 生效时间 | 失效时间 |
| 1 | EXP123 | 100 | 2013/9/1 15:30:00 | 9999/12/31 23:59:59 |
比如上面一条单据，是 2013-9-1 创建的，后来在 2013-9-9 15:00:00 对该单据进行修改，将金额从 100 修改为 120，保存时创建的新数据如下：
| ID | 单号 | 金额 | 生效时间 | 失效时间 |
| 1 | EXP123 | 100 | 2013/9/1 15:30:00 | 2013/9/9 15:00:00 |
| 2 | EXP123 | 120 | 2013/9/9 15:00:00 | 9999/12/31 23:59:59 |
使用了生效、失效时间后，我们可以查询任意时刻数据库中数据的值，只需要把要查询的时刻传入，然后 between 生效时间 and 失效时间即可。
使用前两种方案都需要一个业务主键来标识具体的一个业务数据。如果我们要记录的实体没有明确的 “单号”、“订单号” 这类的业务主键该怎么办？我们可以使用创建数据时的数据库主键作为业务主键。
| 员工 ID | 姓名 | 生日 | 业务 ID | 版本号 |
| 1 | 张三 | 1984/12/29 | 1 | 1 |
比如我们有个员工表，记录员工基本信息，在创建张三这个员工的数据时，其在数据库的 ID 为 1，那么可以将其业务 ID 也设置为 1。接下来对张三的属性进行更改，记录了版本，那么就会创建新的版本，其主键 “员工 ID” 会变化，但是其业务主键 “业务 ID” 始终是 1，不会变化的。
| 员工 ID | 姓名 | 生日 | 业务 ID | 版本号 |
| 1 | 张三 | 1984/12/29 | 1 | 1 |
| 2 | 张三 | 1985/1/9 | 1 | 2 |
使用前面两个方案虽然能够很好的记录历史数据，但是每次修改数据都会导致新版本生成保存，所以每个版本的 ID 都是新的，所以必须有一个业务主键来标识一个实体，这里的两个例子 “单号” 就是其业务主键。主键的变动使得所有关联的对象都得变动，从而形成连锁效应，使得各个关联的对象也生成新的版本。比如我们有个订单系统，里面有订单表和订单明细表。现在我们要对订单的修改记录历史版本，所以增加了生效时间和实效时间，并使用订单号作为业务主键。现在有一个订单 A，下面有 100 条明细，如果要对订单进行修改，将某一条明细的属性进行修改，从而导致整个订单的变化，那么我们就需要创建新的订单数据行，由于主键变动，所以订单明细都需要变动，所以 100 条明细都需要创建新的版本，新版本的订单明细中，“订单 ID”指向了新的版本的订单数据的 ID。
这样的设计造成的问题就是订单明细表会极速膨胀，如果一个订单有 1000 条明细，我们只是修改了订单本身的属性，并不修改订单明细，也会造成对这 1000 条明细做 Copy，然后保存。那怎么办呢？我们可以使用以下办法：
- 对订单明细建立版本字段，将版本的粒度细化到订单明细，而不是订单。订单与订单明细不存在数据库级的外键关系，只存在业务级的外键关系。也就是说订单明细表中增加生效时间、失效时间之外，还需要增加 “订单号” 这个字段，用于表名该明细是属于哪个订单的。
我们这么修改后，如果订单对象进行了修改，订单明细没有修改（比如改了一下收件人信息），那么只需要在订单表中生成新的一行数据，订单明细不会 Copy 生成新的数据。如果我们对某一条订单明细进行了更改（比调整了单价、数量）那么只需要对具体修改的那条订单明细进行更改，而不需要对整个订单的所有明细进行更改。
使用这种设计后，查询订单及其明细，需要对两个表执行生效失效时间的过滤，而且明细的获取是通过订单号去取，而不是通过订单 ID 去取。
将版本控制的粒度细化到订单明细时，后台程序的逻辑也会更加复杂。用户在界面上操作的是订单对象，系统会将整个修改后的订单对象传到后台，后台程序需要对每个订单项进行对比，如果发现订单项进行了修改，那么就会调用生成新版本订单明细的方法。
- 使用单独的历史表
这是另外一种实现历史版本记录的方法：
三、使用单独的历史表
使用历史表其实就是建立完全相同 Schema 的表（当然，也可以添加更多的字段用于记录额外的历史版本信息），该表只保留历史版本的数据。这有点像一个归档逻辑，所有历史版本我们认为都应该是不经常访问的，所有可以扔到单独的表，对于现有生效的版本，仍然保留在原表中，如果需要查询历史版本，那么就从历史表中查询。
使用单独的历史表有以下好处：
- 业务数据表的数据量不会因为历史版本记录而膨胀。因为历史数据都记录到了另外一个表中，所以业务数据表只记录了一份数据。
- 业务数据表的 Schema 不需要调整，增加额外的版本字段。由于对原有数据表不做 Schema 变更，所以原有查询逻辑也不用更改。对于一个现有的数据库设计，在增加历史数据记录功能时更简单。
- 业务数据表可以直接进行 update 操作，不会生成新的 ID。由于 ID 不会变，所以我们并需要业务主键应用到程序逻辑中。
使用历史表记录历史版本主要是要对数据操作方法（增加、删除、修改）进行修改，使得每次数据操作时，先在历史表中留痕，然后再进行数据操作。另外就是对查询历史版本功能进行修改，因为历史数据在另外一个表中，所以对于的 SQL 是不一样的。当然，我们也可以创建历史版本数据库，里面保存了所有的历史表。
