数据库设计修订？ [英] 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:

例如。 Employee实体

e.g. for "Employee" Entity

设计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?

Design 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. 设计2确实有模式更改的问题。如果更改Employees表，则必须更改EmployeeHistories表和与其相关的所有相关sprocs。


3. 设计1效果良好，如果完成得当，性能影响不会很高。您可以使用xml架构甚至索引来克服可能的性能问题。有关解析xml的注释是有效的，但您可以使用xquery轻松创建视图 - 您可以将其包含在查询中并加入。这样的东西...

1. Do not put it all in one table with an IsCurrent descriminator 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屋！