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
SELECT * FROM Page
JOIN PageContent ON CurrentRevision = Revision AND ID = PageID
上面可能存在一些错误...这超出了我的想象。不过，它应该能让您了解另一种模式。
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.
如果您使用的是 SQL Server 2008，您可能应该考虑更改数据捕获。这是 2008 年的新功能，可以为您节省大量工作。
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.
timestamp
username
ip_address
procedureName (if called from a stored procedure)
database
table
field
accesstype (insert, delete, modify)
oldvalue
newvalue
大概这是由触发器维护的。
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:
timestamp
username
ip_address
procedureName (if called from a stored procedure)
database
table
field
accesstype (insert, delete, modify)
oldvalue
newvalue
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;
We’ll create a small example database for a blogging application. Two tables are required:
blog: stores a unique post ID, the title, content, and a deleted flag. audit: 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 blog and indexes the deleted column:
The following SQL creates the audit 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.
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;
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.
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
到目前为止，可以很好地处理约 3.62 亿条记录、多租户、多数据库。
model_attributes 是最重要的，发生了变化，作为键值格式的 json 字符串。
I'm using the following structure:
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
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.
发布评论
评论（6）
一些 wiki 平台使用的一种方法是将识别数据和您正在审核的内容分开。它增加了复杂性，但您最终会得到完整记录的审计跟踪，而不仅仅是已编辑的字段列表，然后您必须将其混搭以使用户了解旧记录的样子。
例如，如果您有一个名为机会的表来跟踪销售交易，那么您实际上需要创建两个单独的表：
机会
Opportunities_Content（或类似的内容）
Opportunities 表将包含您用于唯一标识记录的信息，并包含您为外键引用的主键关系。 Opportunities_Content 表将包含您的用户可以更改且您希望保留审核跟踪的所有字段。 内容表中的每条记录都将包含其自己的 PK 以及修改者和修改日期数据。 机会表将包含对当前版本的引用以及有关主记录最初创建时间和创建者的信息。
这是一个简单的例子：
内容：
如果修订是一种身份类型，我可能会将内容表的 PK 设为来自 PageID 和修订的多列键。您可以使用“修订”列作为 FK。然后，您可以通过像这样的 JOINing 来提取合并记录：
上面可能存在一些错误...这超出了我的想象。不过，它应该能让您了解另一种模式。
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:
And the contents:
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:
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.
如果您使用的是 SQL Server 2008，您可能应该考虑更改数据捕获。这是 2008 年的新功能，可以为您节省大量工作。
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.
我不知道有什么参考资料，但我确信有人写过一些东西。
然而，如果目的只是记录所发生的事情（审计日志的最典型用途），那么为什么不简单地保留所有内容：
大概这是由触发器维护的。
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:
Presumably this is maintained by a trigger.
我们将为博客应用程序创建一个小型示例数据库。需要两个表：
blog：存储唯一的帖子 ID、标题、内容和已删除标志。audit：存储一组基本的历史更改，包括记录 ID、博客文章 ID、更改类型（新建、编辑或删除）以及更改的日期/时间。以下 SQL 创建
blog并对已删除的列建立索引：以下 SQL 创建
audit表。所有列均已编入索引，并为引用 blog.id 的audit.blog_id 定义了外键。因此，当我们物理删除博客条目时，它的完整审核历史记录也会被删除。We’ll create a small example database for a blogging application. Two tables are required:
blog: stores a unique post ID, the title, content, and a deleted flag.audit: 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
blogand indexes the deleted column:The following SQL creates the
audittable. 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.我认为没有什么比决策树更好的了。因为一些优点和缺点（或要求）并不是真正可数的。例如，你如何衡量成熟度？
因此，只需排列您的审计日志记录的业务需求即可。尝试预测这些需求将来可能如何变化并生成您的技术需求。现在您可以比较其优缺点并选择正确/最佳选项。
请放心，无论您如何决定，总会有人认为您做出了错误的决定。然而，你做了功课并且证明了你的决定是正确的。
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.
我正在使用以下结构：
到目前为止，可以很好地处理约 3.62 亿条记录、多租户、多数据库。
model_attributes 是最重要的，发生了变化，作为键值格式的 json 字符串。
I'm using the following structure:
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.