In the project I'm working on, audit log also started from the very minimalistic design, like the one you described:
event ID
event date/time
event type
user ID
description
The idea was the same: to keep things simple.
However, it quickly became obvious that this minimalistic design was not sufficient. The typical audit was boiling down to questions like this:
Who the heck created/updated/deleted a record
with ID=X in the table Foo and when?
So, in order to be able to answer such questions quickly (using SQL), we ended up having two additional columns in the audit table
object type (or table name)
object ID
That's when design of our audit log really stabilized (for a few years now).
Of course, the last "improvement" would work only for tables that had surrogate keys. But guess what? All our tables that are worth auditing do have such a key!
We also log old and new values and the column they are from as well as the primary key of the table being audited in an audit detail table. Think what you need the audit table for? Not only do you want to know who made a change and when, but when a bad change happens, you want a fast way to put the data back.
While you are designing, you should write the code to recover data. When you need to recover, it is usually in a hurry, best to already be prepared.
There are several more things you might want to audit, such as table/column names, computer/application from which an update was made, and more.
Now, this depends on how detailed auditing you really need and at what level.
We started building our own trigger-based auditing solution, and we wanted to audit everything and also have a recovery option at hand. This turned out to be too complex, so we ended up reverse engineering the trigger-based, third-party tool ApexSQL Audit to create our own custom solution.
Tips:
Include before/after values
Include 3-4 columns for storing the primary key (in case it’s a composite key)
Store data outside the main database as already suggested by Robert
Spend a decent amount of time on preparing reports – especially those you might need for recovery
Plan for storing host/application name – this might come very useful for tracking suspicious activities
There are a lot of interesting answers here and in similar questions. The only things that I can add from personal experience are:
Put your audit table in another database. Ideally, you want separation from the original data. If you need to restore your database, you don't really want to restore the audit trail.
Denormalize as much as reasonably possible. You want the table to have as few dependencies as possible to the original data. The audit table should be simple and lightning fast to retrieve data from. No fancy joins or lookups across other tables to get to the data.
In general custom audit (creating various tables) is a bad option. Database/table triggers can be disabled to skip some log activities. Custom audit tables can be tampered. Exceptions can take place that will bring down application. Not to mentions difficulties designing a robust solution. So far I see a very simple cases in this discussion. You need a complete separation from current database and from any privileged users(DBA, Developers). Every mainstream RDBMSs provide audit facilities that even DBA not able to disable, tamper in secrecy. Therefore, provided audit capability by RDBMS vendor must be the first option. Other option would be 3rd party transaction log reader or custom log reader that pushes decomposed information into messaging system that ends up in some forms of Audit Data Warehouse or real time event handler. In summary: Solution Architect/"Hands on Data Architect" needs to involve in destining such a system based on requirements. It is usually too serious stuff just to hand over to a developers for solution.
Auditing data tables need to be separate from the main database. Because audit databases can have a lot of historical data, it makes sense from a memory utilization standpoint to keep them separate.
Do not use triggers to audit the whole database, because you will end up with a mess of different databases to support. You will have to write one for DB2, SQLServer, Mysql, etc.
Primary Key
Event type (e.g. "UPDATED", "APPROVED")
Description ("Frisbar was added to blong")
User Id
User Id of second authoriser
Amount
Date/time
Generic Id
Table Name
通用 id 指向表中已更新的行，表名称是该表的字符串名称。 不是一个好的数据库设计，但是非常有用。 我们所有的表都有一个代理键列，因此效果很好。
What we have in our table:-
Primary Key
Event type (e.g. "UPDATED", "APPROVED")
Description ("Frisbar was added to blong")
User Id
User Id of second authoriser
Amount
Date/time
Generic Id
Table Name
The generic id points at a row in the table that was updated and the table name is the name of that table as a string. Not a good DB design, but very usable. All our tables have a single surrogate key column so this works well.
发布评论
评论（8）
在我正在从事的项目中，审计日志也是从非常简约的设计开始的，就像您所描述的那样：
想法是相同的：让事情变得简单。
然而，很快我们就发现这种简约设计还不够。 典型的审计归结为这样的问题：
因此，为了能够快速回答此类问题（使用 SQL），我们最终在审计表中添加了两列，
此时我们的审计日志的设计才真正稳定下来（对于几年了）。
当然，最后一个“改进”仅适用于具有代理键的表。 但猜猜怎么了？ 我们所有值得审计的表都确实有这样的键！
In the project I'm working on, audit log also started from the very minimalistic design, like the one you described:
The idea was the same: to keep things simple.
However, it quickly became obvious that this minimalistic design was not sufficient. The typical audit was boiling down to questions like this:
So, in order to be able to answer such questions quickly (using SQL), we ended up having two additional columns in the audit table
That's when design of our audit log really stabilized (for a few years now).
Of course, the last "improvement" would work only for tables that had surrogate keys. But guess what? All our tables that are worth auditing do have such a key!
我们还在审计详细信息表中记录旧值和新值以及它们所在的列以及正在审计的表的主键。 想想你需要审计表做什么？ 您不仅想知道谁在何时进行了更改，而且当发生错误的更改时，您还需要一种快速的方法来恢复数据。
在设计时，您应该编写代码来恢复数据。 当您需要恢复时，通常会很匆忙，最好已经做好准备。
We also log old and new values and the column they are from as well as the primary key of the table being audited in an audit detail table. Think what you need the audit table for? Not only do you want to know who made a change and when, but when a bad change happens, you want a fast way to put the data back.
While you are designing, you should write the code to recover data. When you need to recover, it is usually in a hurry, best to already be prepared.
您可能还需要审核其他一些内容，例如表/列名称、进行更新的计算机/应用程序等等。
现在，这取决于您真正需要的审核详细程度以及级别。
我们开始构建自己的基于触发器的审核解决方案，我们希望审核所有内容，并且手头还有一个恢复选项。 事实证明这太复杂了，因此我们最终对基于触发器的第三方工具ApexSQL Audit 进行了逆向工程 创建我们自己的自定义解决方案。
提示：
包含之前/之后的值
按照 Robert 的建议将数据存储在主数据库之外
花费相当多的时间准备报告 - 特别是那些您可能需要恢复的报告
计划存储主机/应用程序名称 - 这对于跟踪可能非常有用可疑活动
There are several more things you might want to audit, such as table/column names, computer/application from which an update was made, and more.
Now, this depends on how detailed auditing you really need and at what level.
We started building our own trigger-based auditing solution, and we wanted to audit everything and also have a recovery option at hand. This turned out to be too complex, so we ended up reverse engineering the trigger-based, third-party tool ApexSQL Audit to create our own custom solution.
Tips:
Include before/after values
Include 3-4 columns for storing the primary key (in case it’s a composite key)
Store data outside the main database as already suggested by Robert
Spend a decent amount of time on preparing reports – especially those you might need for recovery
Plan for storing host/application name – this might come very useful for tracking suspicious activities
这里和类似的问题有很多有趣的答案。 我可以从个人经验中添加的唯一内容是：
将您的审计表放入另一个数据库中。 理想情况下，您希望与原始数据分离。 如果您需要恢复数据库，您实际上并不想恢复审计跟踪。
尽可能合理地反规范化。 您希望表对原始数据的依赖性尽可能少。 审计表应该简单且能够快速检索数据。 无需通过其他表进行花哨的联接或查找来获取数据。
There are a lot of interesting answers here and in similar questions. The only things that I can add from personal experience are:
Put your audit table in another database. Ideally, you want separation from the original data. If you need to restore your database, you don't really want to restore the audit trail.
Denormalize as much as reasonably possible. You want the table to have as few dependencies as possible to the original data. The audit table should be simple and lightning fast to retrieve data from. No fancy joins or lookups across other tables to get to the data.
一般来说，自定义审计（创建各种表）是一个糟糕的选择。 可以禁用数据库/表触发器以跳过某些日志活动。 自定义审核表可以被篡改。 可能会发生导致应用程序瘫痪的异常情况。 更不用说设计强大的解决方案的困难了。 到目前为止，我在这次讨论中看到了一个非常简单的案例。 您需要与当前数据库和任何特权用户（DBA、开发人员）完全分离。
每个主流 RDBMS 都提供审计功能，即使 DBA 也无法秘密禁用、篡改。 因此，RDBMS供应商提供的审计能力必须是首选。 其他选项是第三方事务日志阅读器或自定义日志阅读器，将分解的信息推送到消息传递系统中，最终形成某种形式的审计数据仓库或实时事件处理程序。
总之：解决方案架构师/“数据架构师实践”需要根据需求参与设计这样的系统。 仅仅将其交给开发人员来解决通常是过于严肃的事情。
In general custom audit (creating various tables) is a bad option. Database/table triggers can be disabled to skip some log activities. Custom audit tables can be tampered. Exceptions can take place that will bring down application. Not to mentions difficulties designing a robust solution. So far I see a very simple cases in this discussion. You need a complete separation from current database and from any privileged users(DBA, Developers).
Every mainstream RDBMSs provide audit facilities that even DBA not able to disable, tamper in secrecy. Therefore, provided audit capability by RDBMS vendor must be the first option. Other option would be 3rd party transaction log reader or custom log reader that pushes decomposed information into messaging system that ends up in some forms of Audit Data Warehouse or real time event handler.
In summary: Solution Architect/"Hands on Data Architect" needs to involve in destining such a system based on requirements. It is usually too serious stuff just to hand over to a developers for solution.
根据分离原则：
审计数据表需要与主数据库分离。 由于审计数据库可能包含大量历史数据，因此从内存利用率的角度来看，将它们分开是有意义的。
不要使用触发器来审计整个数据库，因为您最终会得到一堆不同的数据库需要支持。 您必须为 DB2、SQLServer、Mysql 等编写一个触发器。
According to the principle of separation:
Auditing data tables need to be separate from the main database. Because audit databases can have a lot of historical data, it makes sense from a memory utilization standpoint to keep them separate.
Do not use triggers to audit the whole database, because you will end up with a mess of different databases to support. You will have to write one for DB2, SQLServer, Mysql, etc.
表中的内容：-
通用 id 指向表中已更新的行，表名称是该表的字符串名称。 不是一个好的数据库设计，但是非常有用。 我们所有的表都有一个代理键列，因此效果很好。
What we have in our table:-
The generic id points at a row in the table that was updated and the table name is the name of that table as a string. Not a good DB design, but very usable. All our tables have a single surrogate key column so this works well.
有很多方法可以做到这一点。 我最喜欢的方法是：
向源表（您要记录的表）添加一个
mod_user字段。创建一个日志表，其中包含要记录的字段，以及
log_datetime和seq_num字段。seq_num是主键。在源表上构建一个触发器，每当任何监控字段发生更改时，都会将当前记录插入到日志表中。
在源表上构建一个触发器，每当任何监控字段发生变化
现在您已经记录了每项更改以及更改者。
There are many ways to do this. My favorite way is:
Add a
mod_userfield to your source table (the one you want to log).Create a log table that contains the fields you want to log, plus a
log_datetimeandseq_numfield.seq_numis the primary key.Build a trigger on the source table that inserts the current record into the log table whenever any monitored field is changed.
Now you've got a record of every change and who made it.