database 变更日志/审计数据库表的最佳设计？

Best design for a changelog / auditing database table?

提问by rcphq

I need to create a database table to store different change log/auditing (when something was added, deleted, modified, etc). I don't need to store particularly detailed info, so I was thinking something along the lines of:

我需要创建一个数据库表来存储不同的更改日志/审计（添加、删除、修改等时）。我不需要存储特别详细的信息，所以我在考虑以下方面的事情：

• id (for event)
• user that triggered it
• event name
• event description
• timestamp of the event

• id（用于事件）
• 触发它的用户
• 事件名称
• 事件描述
• 事件的时间戳

Am I missing something here? Obviously I can keep improving the design, although I don't plan on making it complicated (creating other tables for event types or stuff like that is out of the question since it's a complication for my need).

我在这里错过了什么吗？显然我可以继续改进设计，尽管我不打算让它变得复杂（为事件类型或类似的东西创建其他表是不可能的，因为它是我需要的复杂性）。

采纳答案by Yarik

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

因此，为了能够快速回答这些问题（使用 SQL），我们最终在审计表中增加了两个列

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!

当然，最后的“改进”仅适用于具有代理键的表。但猜猜怎么了？我们所有值得审计的表都有这样的键！

回答by HLGEM

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.

在设计时，您应该编写代码来恢复数据。当您需要恢复时，通常很匆忙，最好已经做好准备。

回答by Kenneth Hampton

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 Auditto create our own custom solution.

我们开始构建我们自己的基于触发器的审计解决方案，我们希望审计所有内容，并且手头还有一个恢复选项。结果证明这太复杂了，所以我们最终对基于触发器的第三方工具ApexSQL Audit进行了逆向工程，以创建我们自己的自定义解决方案。

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

• 包括之前/之后的值

• 包含 3-4 列用于存储主键（如果它是复合键）

• 按照 Robert 的建议将数据存储在主数据库之外

• 花大量时间准备报告——尤其是那些您可能需要恢复的报告

• 计划存储主机/应用程序名称 - 这对于跟踪可疑活动可能非常有用

回答by Robert4Real

There are a lot of interesting answers here and in similar questions. The only things that I can add from personal experience are:

这里和类似的问题中有很多有趣的答案。我可以从个人经验中补充的唯一内容是：

1. 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.

2. 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.

1. 将您的审计表放在另一个数据库中。理想情况下，您希望与原始数据分离。如果您需要恢复您的数据库，您并不真的想要恢复审计跟踪。

2. 尽可能合理地进行非规范化。您希望该表对原始数据的依赖尽可能少。审计表应该简单且快速，以便从中检索数据。没有花哨的连接或跨其他表查找来获取数据。

回答by WW.

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.

通用 id 指向表中已更新的行，表名是该表的名称作为字符串。不是一个好的数据库设计，但非常有用。我们所有的表都有一个代理键列，所以这很有效。

回答by Joel Mamedov

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.

一般来说，自定义审计（创建各种表）是一个糟糕的选择。可以禁用数据库/表触发器以跳过某些日志活动。自定义审计表可以被篡改。可能会发生异常，从而导致应用程序失败。更不用说设计一个强大的解决方案的困难了。到目前为止，我在本次讨论中看到了一个非常简单的案例。您需要与当前数据库和任何特权用户（DBA、开发人员）完全分离。每个主流 RDBMS 都提供审计工具，即使是 DBA 也无法禁用、秘密篡改。因此，RDBMS 供应商提供的审计功能必须是第一选择。其他选项是 3rd 方事务日志阅读器或自定义日志阅读器，它将分解的信息推送到消息传递系统中，最终以某种形式的审计数据仓库或实时事件处理程序结束。总之：解决方案架构师/“动手数据架构师”需要根据需求参与确定这样的系统。将问题交给开发人员寻求解决方案通常太严肃了。

回答by JosephStyons

There are many ways to do this. My favorite way is:

有很多方法可以做到这一点。我最喜欢的方式是：

1. Add a mod_userfield to your source table (the one you want to log).

2. Create a log table that contains the fields you want to log, plus a log_datetimeand seq_numfield. seq_numis the primary key.

3. Build a trigger on the source table that inserts the current record into the log table whenever any monitored field is changed.

1. mod_user向源表（要记录的表）添加一个字段。

2. 创建一个日志表，其中包含要记录的字段以及一个log_datetime和seq_num字段。 seq_num是主键。

3. 在源表上构建一个触发器，每当任何受监控的字段发生更改时，该触发器都会将当前记录插入到日志表中。

Now you've got a record of every change and who made it.

现在，您已经获得了每次更改的记录以及做出更改的人员。

回答by Bhagat007

According to the principle of separation:

根据分离原理：

1. 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.

2. 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.

1. 审计数据表需要与主数据库分开。因为审计数据库可能有大量历史数据，所以从内存利用率的角度来看，将它们分开是有意义的。

2. 不要使用触发器来审计整个数据库，因为你最终会得到一堆不同的数据库来支持。您必须为 DB2、SQLServer、Mysql 等编写一个。