审计日志的数据库设计 [英] Database design for audit logging
问题描述
每次我需要设计一个新的数据库时，我都会花费相当多的时间思考我应该如何设置数据库架构来保留审计日志变化.
这里已经提出了一些关于此的问题，但我不同意所有场景都有一个最佳方法:
我还偶然发现了这篇关于维护数据库更改日志的有趣文章 试图列出每种方法的优缺点.它写得很好，包含有趣的信息，但它使我的决定更加困难.
我的问题是:是否有我可以使用的参考资料，可能是一本书或决策树之类的东西，我可以参考它来决定我应该根据一些输入变量，例如:
- 数据库架构的成熟度
- 如何查询日志
- 需要重新创建记录的概率
- 更重要的是:写入或读取性能
- 记录的值的性质(字符串、数字、blob)
- 可用存储空间
我所知道的方法是:
1.为创建和修改日期和用户添加列
表格示例:
- id
- value_1
- value_2
- value_3
- 创建日期
- 修改日期
- created_by
- modified_by
主要缺点:我们丢失了修改历史.提交后无法回滚.
2.仅插入表格
表格示例::>
- id
- value_1
- value_2
- value_3
- 来自
- 到
- 已删除(布尔值)
- 用户
主要缺点:如何使外键保持最新?需要巨大的空间
3.为每个表创建一个单独的历史表
历史表示例:
- id
- value_1
- value_2
- value_3
- value_4
- 用户
- 已删除(布尔值)
- 时间戳
主要缺点:需要复制所有审计表.如果架构发生变化，也需要迁移所有日志.
4.为所有表创建一个合并的历史表
历史表示例:
- 表名
- 字段
- 用户
- new_value
- 已删除(布尔值)
- 时间戳
主要缺点:如果需要，我能否轻松地重新创建记录(回滚)?new_value 列需要是一个巨大的字符串，以便它可以支持所有不同的列类型.
少数 wiki 平台使用的一种方法是将识别数据和您正在审核的内容分开.它增加了复杂性，但您最终会得到完整记录的审计跟踪，而不仅仅是经过编辑的字段列表，然后您必须将其混搭以让用户了解旧记录的样子.
例如，如果您有一个名为机会的表来跟踪销售交易，您实际上会创建两个单独的表:
机会
Opportunities_Content(或类似的东西)
Opportunities 表将包含您用来唯一标识记录的信息，并包含您为外键关系引用的主键.Opportunities_Content 表将包含您的用户可以更改的所有字段，并且您希望保留审计跟踪.Content 表中的每条记录都将包括其自己的 PK 以及修改日期和修改日期数据.机会表将包含对当前版本的引用以及有关主记录最初创建时间和创建者的信息.
这是一个简单的例子:
创建表 dbo.Page(ID int PRIMARY KEY,名称 nvarchar(200) 非空，CreatedByName nvarchar(100) NOT NULL,CurrentRevision int NOT NULL,CreatedDateTime 日期时间 NOT NULL内容:
创建表 dbo.PageContent(PageID int NOT NULL,修订 int NOT NULL，标题 nvarchar(200) 非空，用户 nvarchar(100) 非空，LastModified 日期时间 NOT NULL，注释 nvarchar(300) NULL，内容 nvarchar(max) NOT NULL,说明 nvarchar(200) NULL如果 Revision 是一种身份类型，我可能会将内容表的 PK 设为来自 PageID 和 Revision 的多列键.您将使用 Revision 列作为 FK.然后通过 JOINing 来拉取合并记录，如下所示:
SELECT * FROM PageJOIN PageContent ON CurrentRevision = Revision AND ID = PageID上面可能有一些错误......这超出了我的脑海.不过，它应该能让您了解另一种模式.
Every time I need to design a new database I spend quite some time thinking on how I should set up the database schema to keep an audit log of the changes.
Some questions have already been asked here about this, but I don't agree that there is a single best approach for all scenarios:
- Database Design For Revisions
- Best design for a change log auditing database table
- Ideas on database design for capturing audit trails
I have also stumbled upon this interesting article on Maintaining a Log of Database Changes that tries to list the pro and cons of each approach. It's very well written and has interesting information, but it has made my decisions even harder.
My question is: Is there a reference that I can use, maybe a book or something like a decision tree that I can refer to decide which way should I go based on some input variables, like:
- The maturity of the database schema
- How the logs will be queried
- The probability that it will be need to recreate records
- What's more important: write or read performance
- Nature of the values that are being logged (string, numbers, blobs)
- Storage space available
The approaches that I know are:
1. Add columns for created and modified date and user
Table example:
- id
- value_1
- value_2
- value_3
- created_date
- modified_date
- created_by
- modified_by
Major cons: We lose the history of the modifications. Can't rollback after commit.
2. Insert only tables
- id
- value_1
- value_2
- value_3
- from
- to
- deleted (Boolean)
- user
Major cons: How to keep foreign keys up to date? Huge space needed
3. Create a Separate history table for each table
History table example:
- id
- value_1
- value_2
- value_3
- value_4
- user
- deleted (Boolean)
- timestamp
Major cons: Needs to duplicate all audited tables. If the schema changes it will be needed to the migrate all the logs too.
4. Create a Consolidated history Table for All Tables
History table example:
- table_name
- field
- user
- new_value
- deleted (Boolean)
- timestamp
Major cons: Will I be able to recreate the records (rollback) if needed easily? The new_value column needs to be a huge string so it can support all different column types.
One method that is used by a few wiki platforms is to separate the identifying data and the content you're auditing. It adds complexity, but you end up with an audit trail of complete records, not just listings of fields that were edited that you then have to mash up to give the user an idea of what the old record looked like.
So for example, if you had a table called Opportunities to track sales deals, you would actually create two separate tables:
Opportunities
Opportunities_Content (or something like that)
The Opportunities table would have information you'd use to uniquely identify the record and would house the primary key you'd reference for your foreign key relationships. The Opportunities_Content table would hold all the fields your users can change and for which you'd like to keep an audit trail. Each record in the Content table would include its own PK and the modified-by and modified-date data. The Opportunities table would include a reference to the current version as well as information on when the main record was originally created and by whom.
Here's a simple example:
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
And the contents:
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
I would probably make the PK of the contents table a multi-column key from PageID and Revision provided Revision was an identity type. You would use the Revision column as the FK. You then pull the consolidated record by JOINing like this:
SELECT * FROM Page
JOIN PageContent ON CurrentRevision = Revision AND ID = PageID
There might be some errors up there...this is off the top of my head. It should give you an idea of an alternative pattern, though.
这篇关于审计日志的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
