entityMap|blocks|key|3284327|text|我不知道有什么参考资料，但我肯定有人写了些东西。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3284328|然而，如果目的仅仅是记录所发生的事情-审计日志最典型的用法-那么为什么不简单地保留所有内容：|3284329|timestamp
username
ip_address
procedureName+(if+called+from+a+stored+procedure)
database
table
field
accesstype+(insert,+delete,+modify)
oldvalue
newvalue|code-block|syntax|javascript|3284330|这大概是由触发器维护的。|3284331^0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|M|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|N|9|@]|A|@]|B|$]]|$2|E|4|F|6|G|8|O|9|@]|A|@]|B|$H|I]]|$2|J|4|K|6|7|8|P|9|@]|A|@]|B|$]]|$2|L|4|-4|6|7|8|Q|9|@]|A|@]|B|$]]]]

I don't know of any reference, but I'm sure someone has written something.

However, if the purpose is simply to have a record of what happened—the most typical use of an audit log—then why not simply keep everything:

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

Presumably this is maintained by a trigger.

entityMap|blocks|key|321932|text|如果您使用的是SQL+Server2008，那么您可能应该考虑使用变更数据捕获。这是2008年的新特性，可以为您节省大量工作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|321933^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|D|9|@]|A|@]|B|$]]|$2|C|4|-4|6|7|8|E|9|@]|A|@]|B|$]]]]

If you are using SQL Server 2008, you probably should consider Change Data Capture. This is new for 2008 and could save you a considerable amount of work.

entityMap|blocks|key|319340|text|我认为没有什么比决策树更好的了。因为一些利弊(或要求)并不是真正可以计算的。例如，你如何衡量成熟度？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|319341|因此，只需为您的审计日志记录列出您的业务需求。尝试预测这些需求在未来可能发生的变化，并生成您的技术需求。现在，您可以比较它的优缺点，并选择正确/最佳的选项。|319342|请放心，无论你如何决定，总会有人认为你做了错误的决定。然而，你做了你的功课，你证明了你的决定是正确的。|319343^0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|I|9|@]|A|@]|B|$]]|$2|E|4|F|6|7|8|J|9|@]|A|@]|B|$]]|$2|G|4|-4|6|7|8|K|9|@]|A|@]|B|$]]]]

I think there is nothing like a decision tree. Since some of the pros and cons (or the requirements) are not really countable. How do you measure Maturity for instance?

So just line up your business requirements for your audit logging. Try to predict how these requirements might change in the future and generate your technical requirements. Now you can compare it to the pros and cons and choose the right/best option.

And be assured, it doesn't matter how you decide, there will always someone who think you made the wrong decision. However, you did your homework and you justify your decision.

entityMap|blocks|key|319374|text|一些wiki平台使用的一种方法是将识别数据和您正在审核的内容分开。它增加了复杂性，但您最终得到了完整记录的审计跟踪，而不仅仅是编辑过的字段列表，然后您必须将这些字段混合在一起，以便让用户了解旧记录的外观。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|319375|因此，例如，如果您有一个名为Opportunities的表来跟踪销售交易，您实际上将创建两个单独的表：|offset|length|style|BOLD|319376|Opportunities|319377|Opportunities_Content+(或类似的东西)|319378|Opportunities表将包含您将用来唯一标识记录的信息，并将包含您将为外键关系引用的主键。Opportunities_Content表将保存您的用户可以更改的所有字段，以及您希望保留审计跟踪的所有字段。Content表中的每个记录将包括其自己的PK以及modified-by和modified-date数据。Opportunities表将包括对当前版本的引用，以及关于主记录最初创建的时间和创建者的信息。|319379|下面是一个简单的例子：|319380|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|code-block|syntax|javascript|319381|内容如下：|319382|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|319383|我可能会将contents表的PK设置为来自PageID的多列键，而Revision提供的Revision是一种标识类型。您将使用Revision列作为FK。然后通过JOINing拉取合并后的记录，如下所示：|319384|SELECT+*+FROM+Page
JOIN+PageContent+ON+CurrentRevision+=+Revision+AND+ID+=+PageID|319385|可能有一些错误，there...this不在我的脑海中。不过，它应该会让您对另一种模式有一个概念。|319386^0|0|E|D|0|0|D|0|0|L|0|0|D|1C|L|2W|7|4D|D|0|0|0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|16|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|17|9|@$E|18|F|19|G|H]]|A|@]|B|$]]|$2|I|4|J|6|7|8|1A|9|@$E|1B|F|1C|G|H]]|A|@]|B|$]]|$2|K|4|L|6|7|8|1D|9|@$E|1E|F|1F|G|H]]|A|@]|B|$]]|$2|M|4|N|6|7|8|1G|9|@$E|1H|F|1I|G|H]|$E|1J|F|1K|G|H]|$E|1L|F|1M|G|H]|$E|1N|F|1O|G|H]]|A|@]|B|$]]|$2|O|4|P|6|7|8|1P|9|@]|A|@]|B|$]]|$2|Q|4|R|6|S|8|1Q|9|@]|A|@]|B|$T|U]]|$2|V|4|W|6|7|8|1R|9|@]|A|@]|B|$]]|$2|X|4|Y|6|S|8|1S|9|@]|A|@]|B|$T|U]]|$2|Z|4|10|6|7|8|1T|9|@]|A|@]|B|$]]|$2|11|4|12|6|S|8|1U|9|@]|A|@]|B|$T|U]]|$2|13|4|14|6|7|8|1V|9|@]|A|@]|B|$]]|$2|15|4|-4|6|7|8|1W|9|@]|A|@]|B|$]]]]

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:

<pre><code>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
</code></pre>

And the contents:

<pre><code>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
</code></pre>

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:

<pre><code>SELECT * FROM Page
JOIN PageContent ON CurrentRevision = Revision AND ID = PageID
</code></pre>

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.

entityMap|blocks|key|322032|text|我们将为博客应用程序创建一个小型示例数据库。需要两个表：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|322033|blog：存储惟一的帖子ID、标题、内容和已删除标志。audit：存储一组基本的历史更改，包括记录ID、博客帖子ID、更改类型(新建、编辑或删除)和更改的日期/时间。下面的SQL创建blog并为已删除的列建立索引：|offset|length|style|CODE|322034|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';|code-block|syntax|javascript|322035|下面的SQL创建audit表。所有列都被编入索引，并且为引用blog.id的audit.blog_id定义了一个外键。因此，当我们物理删除一个博客条目时，它的完整审计历史也会被删除。|322036|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;|322037^0|0|0|4|R|5|2J|4|0|0|8|5|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|S|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|T|9|@$E|U|F|V|G|H]|$E|W|F|X|G|H]|$E|Y|F|Z|G|H]]|A|@]|B|$]]|$2|I|4|J|6|K|8|10|9|@]|A|@]|B|$L|M]]|$2|N|4|O|6|7|8|11|9|@$E|12|F|13|G|H]]|A|@]|B|$]]|$2|P|4|Q|6|K|8|14|9|@]|A|@]|B|$L|M]]|$2|R|4|-4|6|7|8|15|9|@]|A|@]|B|$]]]]

We’ll create a small example database for a blogging application. Two tables are required:

<code>blog</code>: stores a unique post ID, the title, content, and a deleted flag.
<code>audit</code>: stores a basic set of historical changes with a record ID, the blog post ID, the change type (NEW, EDIT or DELETE) and the date/time of that change.
The following SQL creates the <code>blog</code> and indexes the deleted column:

<pre><code>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';
</code></pre>

The following SQL creates the <code>audit</code> table. All columns are indexed and a foreign key is defined for audit.blog_id which references blog.id. Therefore, when we physically DELETE a blog entry, it’s full audit history is also removed.

<pre><code>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;
</code></pre>

entityMap|blocks|key|321273|text|我使用了以下结构：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|321274|id++int
user_id+int
system_user_id++int
tenant_id+++int
db_name+varchar
model_name++varchar
model_primary_key+++int
model_attributes++++text
created_at++timestamp
ip++varchar
session_id++varchar
request_id++varchar
comments++++text|code-block|syntax|javascript|321275|到目前为止，它与约3.62亿条记录、多租户、多数据库配合得很好。|321276|model_attributes是最重要的，改变了的是，json字符串的键值格式。|321277^0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|M|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|N|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|O|9|@]|A|@]|B|$]]|$2|J|4|K|6|7|8|P|9|@]|A|@]|B|$]]|$2|L|4|-4|6|7|8|Q|9|@]|A|@]|B|$]]]]

I'm using the following structure:
<pre><code>id int
user_id int
system_user_id int
tenant_id int
db_name varchar
model_name varchar
model_primary_key int
model_attributes text
created_at timestamp
ip varchar
session_id varchar
request_id varchar
comments text
</code></pre>
Works well so far with ~362 million records, multi-tenant, multi-database.
model_attributes is the most important, what changed, as json string in key value format.

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:

<ul>
<li><a href="https://stackoverflow.com/questions/39281/database-design-for-revisions">Database Design For Revisions</a></li>
<li><a href="https://stackoverflow.com/questions/201527/best-design-for-a-changelog-auditing-database-table">Best design for a change log auditing database table</a></li>
<li><a href="https://stackoverflow.com/questions/1051449/ideas-on-database-design-for-capturing-audit-trails">Ideas on database design for capturing audit trails</a></li>
</ul>

I have also stumbled upon this <a href="http://www.4guysfromrolla.com/webtech/041807-1.shtml" rel="noreferrer">interesting article on Maintaining a Log of Database Changes</a> 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:

<ul>
<li>The maturity of the database schema</li>
<li>How the logs will be queried</li>
<li>The probability that it will be need to recreate records</li>
<li>What's more important: write or read performance</li>
<li>Nature of the values that are being logged (string, numbers, blobs)</li>
<li>Storage space available</li>
</ul>

The approaches that I know are:

1. Add columns for created and modified date and user

Table example:

<ul>
<li>id</li>
<li>value_1</li>
<li>value_2</li>
<li>value_3</li>
<li>created_date</li>
<li>modified_date</li>
<li>created_by</li>
<li>modified_by</li>
</ul>

Major cons: We lose the history of the modifications. Can't rollback after commit.

2. Insert only tables

<a href="https://stackoverflow.com/questions/1051449/ideas-on-database-design-for-capturing-audit-trails/1051494#1051494">Table example</a>:

<ul>
<li>id</li>
<li>value_1</li>
<li>value_2</li>
<li>value_3</li>
<li>from</li>
<li>to</li>
<li>deleted (Boolean)</li>
<li>user</li>
</ul>

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:

<ul>
<li>id</li>
<li>value_1</li>
<li>value_2</li>
<li>value_3</li>
<li>value_4</li>
<li>user</li>
<li>deleted (Boolean)</li>
<li>timestamp</li>
</ul>

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:

<ul>
<li>table_name</li>
<li>field</li>
<li>user</li>
<li>new_value</li>
<li>deleted (Boolean)</li>
<li>timestamp</li>
</ul>

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.

Database design for audit logging

SQL Server

数据库

 每次我需要设计一个新的数据库时，我都会花相当多的时间思考我应该如何设置数据库模式来保存更改的审计日志。 这里已经提出了一些问题，但我不同意所有情况下都有一种最好的方法：  修订数据库设计 更改日志审核数据表的最佳设计 用于捕获审计跟踪的数据库设计思路  我还偶然发现了这篇关于维护数据库更改日志的有趣文章，它试图列出每...

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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