修订的数据库设计？
我们在项目中有一个要求，以存储数据库中的实体的所有修订(更改历史)。目前，我们为此设计了两个方案：
例如，用于"Employee“实体
设计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, .., ..)"有没有其他的方法来做这件事？
“设计1”的问题在于，每次需要访问数据时，我们都必须解析XML。这会减慢进程，还会增加一些限制，比如我们不能在修订数据字段上添加连接。
而“设计2”的问题是，我们必须复制所有实体上的每个字段(我们大约有70-80个实体，我们想要维护其修订)。
回答 15
Stack Overflow用户
发布于 2008-09-02 12:13:47
- Do not将所有内容放在一个具有IsCurrent鉴别器属性的表中。这只会导致问题，需要代理键，以及各种其他problems.
- Design 2在模式更改方面确实存在问题。如果更改Employees表，则必须更改EmployeeHistories表以及与之相关的所有存储过程。
- Design1运行良好，如果处理得当，不会对性能造成太大影响。您可以使用xml模式甚至索引来克服可能的性能问题。您关于解析xml的评论是有效的，但是您可以很容易地使用xquery创建视图-您可以将xquery包含在查询和连接到中。就像这样……
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 Stack Overflow用户
发布于 2008-09-02 11:46:45
我认为这里要问的关键问题是‘谁/什么将使用历史’？
如果它主要用于报告/人类可读的历史，我们已经在过去实现了这个方案……
创建一个名为'AuditTrail‘的表，或者创建一个包含以下字段的表...
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[EventDate] [datetime] NOT NULL,
[TableName] [varchar](50) NOT NULL,
[RecordID] [varchar](20) NOT NULL,
[FieldName] [varchar](50) NULL,
[OldValue] [varchar](5000) NULL,
[NewValue] [varchar](5000) NULL然后，您可以向所有表添加一个'LastUpdatedByUserID‘列，该列应在每次对表执行更新/插入操作时进行设置。
然后，您可以向每个表添加触发器，以捕获发生的任何插入/更新，并在此表中为每个已更改的字段创建一个条目。因为每次更新/插入都会为表提供'LastUpdateByUserID‘，所以您可以在触发器中访问此值，并在添加到audit表时使用它。
我们使用RecordID字段来存储正在更新的表的key字段的值。如果它是一个组合键，我们只需在字段之间使用'~‘进行字符串连接。
我敢肯定这个系统可能有缺点--对于频繁更新的数据库，性能可能会受到影响，但对于我的web应用程序，我们获得的读操作比写操作多得多，而且它似乎表现得相当好。我们甚至编写了一个小的VB.NET实用程序来根据表定义自动编写触发器。
这只是一个想法！
Stack Overflow用户
发布于 2008-09-24 10:53:31
Database Programmer博客中的History Tables文章可能会很有用-涵盖了这里提出的一些观点，并讨论了增量的存储。
编辑
在History Tables的文章中，作者(Kenneth Downs)建议维护一个至少包含七列的历史表：
- 更改的时间戳，进行更改的
- 用户，
- 标识已更改记录的标记(其中历史记录与当前状态分开维护)，
- 更改是插入、更新还是删除，
<代码>H115旧值，<代码>H216<代码>H117新值，<代码>H218<代码>H119增量(用于数值更改)。<代码>H220<代码>G221
不应在历史表中跟踪从不更改或不需要历史记录的列，以避免膨胀。存储数值的增量可以使后续查询更容易，即使它可以从旧值和新值派生。
历史表必须是安全的，禁止非系统用户插入、更新或删除行。应该只支持定期清除，以减少整体大小(如果用例允许)。
https://stackoverflow.com/questions/39281
复制相似问题