变更日志/审计数据库表的最佳设计? [英] Best design for a changelog / auditing database table?

问题描述

我需要创建一个数据库表来存储不同的变更日志/审计(添加、删除、修改等内容时).我不需要存储特别详细的信息，所以我在思考:

I need to create a database table to store different changelog/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(用于事件)
• 触发它的用户
• 活动名称
• 事件描述
• 事件时间戳

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

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

推荐答案

在我正在做的项目中，审计日志也是从非常简约的设计开始的，就像你描述的那样:

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?

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

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!

这篇关于变更日志/审计数据库表的最佳设计?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！