为简单起见,假设我们有这两个表:
CREATE TABLE `tbl_companies` (
`id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `tbl_workers` (
`id` int(11) NOT NULL,
`name` int(11) NOT NULL,
PRIMARY KEY (`id`)
);
还有2张表用来存储公司和工人之间的关系
CREATE TABLE `tbl_companies_workers` (
`company_id` int(11) NOT NULL,
`worker_id` int(11) NOT NULL,
PRIMARY KEY (`company_id `, `worker_id `)
);
CREATE TABLE `tbl_companies_workers_history` (
`company_id` int(11) NOT NULL,
`worker_id` int(11) NOT NULL,
`start` date NOT NULL,
`end` date DEFAULT NULL,
PRIMARY KEY (`company_id `, `worker_id `)
)
使用此架构,可以轻松显示类似以下内容:“John Smith 从 2010 年到 2012 年在 MyComp 工作”。但是,如果从 de DB 中删除 MyComp,则该信息也会被删除。即使公司被删除,我也希望能够显示相同的信息。
是否有可能在不失去参照完整性的情况下实现这一目标?最好的方法是什么?我一直在寻找一些信息,我找到了 this和this,但我仍然不清楚。
提前致谢。
这是一个长期存在的问题,有许多笨拙的解决方案,例如您所引用的解决方案。最好的仍然存在参照完整性的问题。你如何引用一个随时间变化的实体并且你想维护所有版本?
解决方案实际上非常简单:归一化。当我们规范化一个实体时,有一个主实体表。这包含实体的 PK 和所有其他属性。来自其他表的所有 FK 都引用此表,因为对于任何给定的实体只有一个条目。
规范化后,该表仍然存在,但(至少)有一个包含某些属性的其他表。任何唯一实体可能有零个、一个或多个条目,但这没关系。其实这张表的PK就是实体的PK(也是回主实体表的FK)和一些其他的属性。
所以现在你实现了 Version Normal Form。获取主要实体的所有属性并分离出那些随时间变化的属性,并且您想跟踪这些变化。您拥有的是主实体表,其中包含实体的唯一 PK(参照完整性正常工作)以及所有不随时间变化或不跟踪变化的属性。然后“版本”表包含所有更改和跟踪的属性。这张表的PK就是实体PK和change date(版本生效的日期和时间)。
该方案的部分优点列表是
可以使用两种“软删除”方法。可以将“已删除”日期时间属性添加到主记录,默认值为 NULL 或(我更喜欢)神奇日期 9999-12-31。这就是我所说的“坚定”删除,因为它不应该是可撤销的。另一种方法是在版本记录中添加一个“isDeleted”属性。删除操作会插入一个“isDeleted”设置为 TRUE(或“Y”或其他)的版本。删除只是另一个版本。因此,要取消删除,关闭“isDeleted”的新版本将使实体重新存在——删除它然后取消删除的时间就像任何其他状态更改一样保持不变。当然,如果需要的话,这两种方法都可以使用。
这是我在技术展览会上多次制作的演示文稿的幻灯片。它涵盖了如何完成上述所有操作,包括查询。这是一份更详细的文件。
在表中添加一个
IsDeleted
位字段并将其设置为TRUE
删除行时,并在存储过程或应用程序代码中设置不显示IsDeleted
行的规则。这样数据实际上并没有从数据库中删除。