用于审计日志记录的数据库设计

Question

我的问题是：是否有我可以使用的参考，也许是一本书或诸如决策树之类的东西，我可以参考这些参考来基于一些输入变量来决定应该走的路，例如：

• 数据库架构的成熟度
• 如何查询日志
• 需要重新创建记录的概率
• 更重要的是：写入或读取性能
• 所记录的值的性质（字符串，数字，blob）
• 可用的存储空间

我知道的方法是：

1.添加创建和修改日期及用户的列

表格示例：

• id
• value_1
• value_2
• value_3
• created_date
• modified_date
• created_by
• modified_by

主要缺点：我们丢失了修改的历史记录。提交后无法回滚。

2.仅插入表格

表示例：

• id
• value_1
• value_2
• value_3
• from
• to
• deleted (Boolean)
• user
  主要缺点：如何使外键保持最新状态？需要巨大的空间
  

3.为每个表创建一个单独的历史记录表

历史记录表示例：

• id
• value_1
• value_2
• value_3
• value_4
• user
• deleted (Boolean)
• timestamp
  主要缺点：需要复制所有审核表。如果架构更改，则也需要迁移所有日志。
  

4.为所有表创建一个合并的历史表

历史记录表示例：

• table_name
• field
• user
• new_value
• deleted (Boolean)
• timestamp

主要缺点：如果需要，我是否可以重新创建记录（回滚）？new_value列必须是一个巨大的字符串，以便它可以支持所有不同的列类型。

Answer 1

某些Wiki平台使用的一种方法是将标识数据和要审核的内容分开。它增加了复杂性，但是最终您将获得完整记录的审核记录，而不仅仅是经过编辑的字段列表，然后您必须将其混搭以使用户了解旧记录的外观。

因此，例如，如果您有一个称为“机会”的表来跟踪销售交易，则实际上将创建两个单独的表：

机会
Opportunities_Content（或类似的内容）

在机遇表会让你原本是用来唯一标识的记录，将能容纳你会为你的外键关系引用的主键信息。该Opportunities_Content表将持有你的用户可以更改所有字段，并且您想为其保留审计线索。“内容”表中的每个记录都将包含其自己的PK以及“修改者”和“修改日期”数据。在机遇表将包括在最初创建的主记录以及由谁来到当前版本的参考和信息。

这是一个简单的例子：

CREATE TABLE dbo.Page(  
    ID int PRIMARY KEY,  
    Name nvarchar(200) NOT NULL,  
    CreatedByName nvarchar(100) NOT NULL, 
    CurrentRevision int NOT NULL, 
    CreatedDateTime datetime NOT NULL

内容：

CREATE TABLE dbo.PageContent(
    PageID int NOT NULL,
    Revision int NOT NULL,
    Title nvarchar(200) NOT NULL,
    User nvarchar(100) NOT NULL,
    LastModified datetime NOT NULL,
    Comment nvarchar(300) NULL,
    Content nvarchar(max) NOT NULL,
    Description nvarchar(200) NULL

如果Revision是一种身份类型，我可能会将内容表的PK设为PageID和Revision中的多列键。您可以将“修订”列用作FK。然后，您可以像这样通过JOINing提取合并记录：

SELECT * FROM Page
JOIN PageContent ON CurrentRevision = Revision AND ID = PageID

那里可能有一些错误…这简直就是我的头上。不过，它应该给您一个替代模式的想法。