最好设计一个changelog /审计数据库表？ [英] Best design for a changelog / auditing database table?
问题描述
我需要创建一个数据库表来存储不同的更改日志/审计
（当添加，删除，修改等）。我不需要存储特别详细的信息，所以我在想的东西如下：
- id（用于事件）
- 事件描述
- 事件的时间戳记
我在这里缺少什么？显然，我可以继续改进设计，虽然我不打算使它复杂（为事件类型创建其他表或类似的东西是没有问题，因为它是一个复杂的我的需要）。
在我正在开发的项目中，审计日志也从非常简约的设计开始，像你所描述的那样：
事件ID
事件日期/时间
事件类型
用户ID
描述
这个想法是一样的：保持简单。
然而，很快显而易见的是，这种简约的设计是不够的。典型的审计是这样的问题：
谁创建/更新/删除记录
ID = X在表中Foo和什么时候？
因此，为了能够快速回答这些问题（使用SQL）审计表中的两个附加列
对象类型（或表名）
对象ID
这是我们的审计日志的设计真的稳定了（几年了）。
当然，最后的改进只适用于具有代理键的表。但猜猜什么？我们所有值得审计的表都有这样的钥匙！
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
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?
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!
这篇关于最好设计一个changelog /审计数据库表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
