用于审计日志记录的数据库设计
每次我需要设计一个新的数据库时，我都会花相当多的时间思考我应该如何设置数据库模式来保存更改的审计日志。
这里已经提出了一些问题，但我不同意所有情况下都有一种最好的方法：
我还偶然发现了这篇关于维护数据库更改日志的有趣文章，它试图列出每种方法的优缺点。它写得很好，有有趣的信息，但它让我的决定变得更加困难。
我的问题是：有没有我可以使用的参考资料，也许是一本书或者像决策树这样的东西，我可以参考它来根据一些输入变量来决定我应该走哪条路，比如：
可用数据库模式的成熟度更重要的是:写入或读取正在记录的值(string，numbers，blobs)
- 数据库模式的成熟度
- 如何查询日志
- 需要重新创建记录的概率
- 更重要的是：写入或读取性能
- 正在记录的值的性质（字符串、数字、blob）
- 可用存储空间
我知道的方法是：
1.为created和modified date和user添加列
表示例：
- id
- 值_1
- 值_2
- 值_3
- 创建日期
- 修改日期
- 创建者
- 修改者
主要缺点:我们丢失了修改的历史记录。提交后无法回滚。
2.仅插入表格
- id
- 值_1
- 值_2
- 值_3
- 从
- 到
- 已删除 (布尔值)
- 用户
主要缺点:如何使外键保持最新？需要巨大的空间
3.为每个表创建一个单独的历史记录表
历史表示例：
- id
- 值_1
- 值_2
- 值_3
- 值_4
- 用户
- 已删除 (布尔值)
- 时间戳
主要缺点:需要复制所有已审核的表。如果架构发生变化，还需要迁移所有日志。
4.为所有表创建一个合并历史表
历史表示例：
- 表名
- 场地
- 用户
- 新值
- 已删除(布尔值)
- 时间戳
主要缺点:如果需要，我可以轻松地重新创建记录(回滚)吗？new_value列需要是一个很长的字符串，这样它才能支持所有不同的列类型。
回答 6
Stack Overflow用户
发布于 2010-01-07 20:55:31
一些wiki平台使用的一种方法是将识别数据和您正在审核的内容分开。它增加了复杂性，但您最终得到了完整记录的审计跟踪，而不仅仅是编辑过的字段列表，然后您必须将这些字段混合在一起，以便让用户了解旧记录的外观。
因此，例如，如果您有一个名为Opportunities的表来跟踪销售交易，您实际上将创建两个单独的表：
Opportunities
Opportunities_Content (或类似的东西)
Opportunities表将包含您将用来唯一标识记录的信息，并将包含您将为外键关系引用的主键。Opportunities_Content表将保存您的用户可以更改的所有字段，以及您希望保留审计跟踪的所有字段。Content表中的每个记录将包括其自己的PK以及modified-by和modified-date数据。Opportunities表将包括对当前版本的引用，以及关于主记录最初创建的时间和创建者的信息。
下面是一个简单的例子：
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我可能会将contents表的PK设置为来自PageID的多列键，而Revision提供的Revision是一种标识类型。您将使用Revision列作为FK。然后通过JOINing拉取合并后的记录，如下所示：
SELECT * FROM Page
JOIN PageContent ON CurrentRevision = Revision AND ID = PageID可能有一些错误，there...this不在我的脑海中。不过，它应该会让您对另一种模式有一个概念。
Stack Overflow用户
发布于 2010-01-07 02:47:01
如果您使用的是SQL Server2008，那么您可能应该考虑使用变更数据捕获。这是2008年的新特性，可以为您节省大量工作。
Stack Overflow用户
发布于 2010-01-07 02:34:14
我不知道有什么参考资料，但我肯定有人写了些东西。
然而，如果目的仅仅是记录所发生的事情-审计日志最典型的用法-那么为什么不简单地保留所有内容：
timestamp
username
ip_address
procedureName (if called from a stored procedure)
database
table
field
accesstype (insert, delete, modify)
oldvalue
newvalue这大概是由触发器维护的。
https://stackoverflow.com/questions/2015232
复制相似问题