修订的数据库设计? [英] Database Design for Revisions?

问题描述

我们在项目中有一个需求，即在数据库中存储实体的所有修订(变更历史).目前我们有 2 个为此设计的提案:

We have a requirement in project to store all the revisions(Change History) for the entities in the database. Currently we have 2 designed proposals for this:

例如对于员工"实体

设计 1:

-- Holds Employee Entity
"Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)"

-- Holds the Employee Revisions in Xml. The RevisionXML will contain
-- all data of that particular EmployeeId
"EmployeeHistories (EmployeeId, DateModified, RevisionXML)"

设计 2:

-- Holds Employee Entity
"Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)"

-- In this approach we have basically duplicated all the fields on Employees 
-- in the EmployeeHistories and storing the revision data.
"EmployeeHistories (EmployeeId, RevisionId, DateModified, FirstName, 
      LastName, DepartmentId, .., ..)"

有没有其他方法可以做这件事?

Is there any other way of doing this thing?

设计 1"的问题在于，每次需要访问数据时，我们都必须解析 XML.这会减慢进程并增加一些限制，例如我们无法在修订数据字段上添加连接.

The problem with the "Design 1" is that we have to parse XML each time when you need to access data. This will slow the process and also add some limitations like we cannot add joins on the revisions data fields.

设计 2"的问题是我们必须复制所有实体上的每一个字段(我们有大约 70-80 个我们想要维护修订的实体).

And the problem with the "Design 2" is that we have to duplicate each and every field on all entities (We have around 70-80 entities for which we want to maintain revisions).

推荐答案

1. 不要不要将所有内容放在一个具有 IsCurrent 鉴别器属性的表中.这只会导致问题，需要代理键和各种其他问题.
2. Design 2 在架构更改方面确实存在问题.如果您更改员工表，您必须更改员工历史表和所有与之相关的 sproc.可能会使您的架构更改工作加倍.
3. 设计 1 运行良好，如果处理得当，不会对性能造成太大影响.您可以使用 xml 模式甚至索引来解决可能的性能问题.您关于解析 xml 的评论是有效的，但您可以使用 xquery 轻松创建视图 - 您可以将其包含在查询中并加入.像这样的东西...

1. Do not put it all in one table with an IsCurrent discriminator attribute. This just causes problems down the line, requires surrogate keys and all sorts of other problems.
2. Design 2 does have problems with schema changes. If you change the Employees table you have to change the EmployeeHistories table and all the related sprocs that go with it. Potentially doubles you schema change effort.
3. Design 1 works well and if done properly does not cost much in terms of a performance hit. You could use an xml schema and even indexes to get over possible performance problems. Your comment about parsing the xml is valid but you could easily create a view using xquery - which you can include in queries and join to. Something like this...

CREATE VIEW EmployeeHistory
AS
, FirstName, , DepartmentId

SELECT EmployeeId, RevisionXML.value('(/employee/FirstName)[1]', 'varchar(50)') AS FirstName,

  RevisionXML.value('(/employee/LastName)[1]', 'varchar(100)') AS LastName,

  RevisionXML.value('(/employee/DepartmentId)[1]', 'integer') AS DepartmentId,

FROM EmployeeHistories 

这篇关于修订的数据库设计?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！