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

Question

每次我需要设计一个新的数据库时，我花了相当一段时间 考虑如何设置数据库模式以保持审计日志 的更改。

有些问题已经在这里关于这个要求，但我不同意， 有适合所有情况的一个最好的方法：

• Database Design For Revisions
• Best design for a changelog auditing database table
• Ideas on database design for capturing audit trails

我也偶然发现了这个interesting article on Maintaining a Log of Database Changes试图列出亲和EAC的利弊h方法。它写得非常好，并且有有趣的信息，但它使我的决定变得更加困难。

我的问题是：是否有一个参考，我可以使用，也许一本书或者像一个决策树可以参考我来决定哪种方式应基于一些 输入变量我走了，喜欢的东西：

• 数据库架构
• 如何日志将被查询
• 的概率，这将需要重新记录
• 什么是更重要的成熟度：写入或读取PE rformance
• 自然可

，我知道的方法正在被记录的值（字符串，数字，斑点）

存储空间为：

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

表例如：

• ID
• _1
• _2
• VALUE_3
• CREATED_DATE
• modifed_date
• CREATED_BY
• modified_by

主要缺点：我们失去了改变的历史。提交后无法回滚。

2。只能插入表格

Table example：

• ID
• _1
• _2
• VALUE_3
• 从
• 到
• 删除（布尔）
• 用户

主要缺点：如何让外键保持最新状态？巨大的空间需要

3.为每个表

历史表的例子创建一个单独的历史表：

• ID
• _1
• _2
• VALUE_3
• VALUE_4
• 用户
• 删除（布尔）
• 时间戳

主要缺点：需要复制所有审计表。如果模式更改，则还需要迁移所有日志。

4.创建一个统一的历史表中所有的表

历史表的例子：

• table_name的
• 场
• 用户
• NEW_VALUE
• 删除（布尔）
• 时间戳

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

Answer 1

少数维基平台使用的一种方法是分隔您正在审核的识别数据和内容。它增加了复杂性，但是最终会得到完整记录的审计跟踪，而不仅仅是已编辑的字段列表，您必须将其混合起来以便让用户了解旧记录的样子。

因此，举例来说，如果你有一台名为机会跟踪销售交易，你实际上创建两个单独的表：

机会
Opportunities_Content（或类似的东西）

机会表中将包含您将用于唯一标识记录的信息，并包含您为f引用的主键外汇关键关系。 Opportunities_Content表将包含用户可以更改的所有字段，并且您希望为其保留审计线索。 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 

我很可能会令内容表的PK从PAGEID多列键和修订提供了修订的身份类型。您将使用修订列作为FK。然后你通过JOINing这样拉取综合记录：

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

有可能有一些错误在那里......这是我的头顶。但它应该给你一个替代模式的想法。

Josh

Answer 2

我不知道有任何参考，但我确定有人写了一些东西。

但是，如果目的只是为了有发生的事情，最典型的使用审计的记录日志，那么为什么不干脆让一切：

timestamp 
username 
ip_address 
procedureName (if called from a stored procedure) 
database 
table 
field 
accesstype (insert, delete, modify) 
oldvalue 
newvalue 

据推测，这是由触发器维护。

Answer 3

如果您使用的是SQL Server 2008，那么您可能应该考虑更改数据捕获。这是2008年的新功能，可以为您节省大量的工作。

Answer 4

我认为没有什么像决策树。由于一些优点和缺点（或要求）不是真正可数的。你如何衡量成熟度？

因此，只需将您的审计日志的业务需求排成一行。尝试预测这些要求将来如何变化并产生技术要求。现在你可以比较它的优点和缺点，并选择正确/最好的选择。

而且可以放心，不管你怎么决定，总有人会认为你做出了错误的决定。然而，你做了功课，你证明了你的决定是正确的。

Answer 5

我们将为博客应用程序创建一个小示例数据库。两个表是必需的：

blog：存储唯一帖子ID，标题，内容，以及删除标志。 audit：存储一组基本的与记录的ID改变的日期/时间的历史变化，博客文章ID，变化类型（NEW，编辑或删除）和。 下面的SQL创建blog和索引中删除列：

CREATE TABLE `blog` (
    `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, 
    `title` text, 
    `content` text, 
    `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0', 
    PRIMARY KEY (`id`), 
    KEY `ix_deleted` (`deleted`) 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='Blog posts'; 

下面的SQL创建audit表。所有列都被编入索引，并为引用blog.id的audit.blog_id定义一个外键。因此，当我们实际删除博客条目时，它的全部审计历史记录也会被删除。

CREATE TABLE `audit` (
    `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, 
    `blog_id` mediumint(8) unsigned NOT NULL, 
    `changetype` enum('NEW','EDIT','DELETE') NOT NULL, 
    `changetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    PRIMARY KEY (`id`), 
    KEY `ix_blog_id` (`blog_id`), 
    KEY `ix_changetype` (`changetype`), 
    KEY `ix_changetime` (`changetime`), 
    CONSTRAINT `FK_audit_blog_id` FOREIGN KEY (`blog_id`) REFERENCES `blog` (`id`) ON DELETE CASCADE ON UPDATE CASCADE 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;