审计表结构
我正在为我的数据库构建审计表，并且需要选择要实现的样式。我目前正在考虑三个选项，所有这些选项都将使用触发器填充：
- 带有字段 id | 的单个表。表|专栏 |行|旧值 |新值 |时间戳|用户身份。这将在一个位置跟踪所有表的所有更改，并具有最大限度减少表数量的好处。它确实使查询有点困难，但并非不可能。
- 多个表如 #1，但没有表列。这会将每个表的更改分离到它们自己的历史表中。
- 镜像要跟踪的原始表的架构的多个表。这将使触发器更容易编写，如果有人想要恢复到特定记录，将使数据恢复更容易，但会以存储为代价，因为每个字段，即使它没有改变，也会被重复，可能多次。此外，很难具体了解哪些字段从一个版本更改为下一版本。
这三个选项中的每一个都是可行的，据我所知，没有一个选项提供的功能是另一个选项不可能提供的。所以肯定有一些我没有考虑的东西或者一些更标准的模式。如果有什么区别的话，这个解决方案必须适用于 mysql 和 sql server（尽管我可以稍后弄清楚代码的细节）。
I'm building audit tables for my database and need to choose what style to implement. I'm currently considering three options, all of which would be populated using triggers:
- A single table with the fields id | table | column | row | old_value | new_value | timestamp | userid. This would track all changes to all tables in a single place and has the benefit of minimizing the number of tables. It does make querying a little difficult, but not impossible.
- Multiple tables like #1 except without the table column. This would separate the changes from each table into their own history table.
- Multiple tables that mirror the schema of the original tables to track. This would make the triggers a lot easier to write, would make restoration of the data easier if someone wanted to revert to a specific record, but would come at the expense of storage, as every field, even if it hadn't changed, would be duplicated, possibly multiple times. Also, it would make it difficult to know specifically which fields changed from one version to the next.
Each of these three options is do-able, and as far as I can tell there isn't functionality that one offers that is impossible in another. So there must be something I'm not considering or some pattern that is more standard. If it makes any difference, this solution must work for both mysql and sql server (though I can work out the specifics of the code later).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
审计表受到的打击非常严重，您不希望只使用一张表来进行所有审计，否则您会遇到阻塞。
我们做了类似于第二点的事情，只是每个表有两个表（一个存储更改实例，另一个存储实际数据。这使得很容易找到存储在表中的一百万条记录导入中的所有记录，例如，因为它们都是相同的实例，这意味着我们可以在添加新表时轻松编写创建新审计表的脚本，
在第二个情况下，我建议编写一个过程来恢复特定记录，这样恢复就很容易。不必每次都弄清楚。
Audit tables are hit very heavily, you do not want only one table for all auditing or you will get blocking.
We do something like number two except we have two tables per table (one that stores the instances of changes and one that stores the actual data. This makes it easy to find all the records stored in amillion record import to a table for instance since they are all inteh same instance. This means we can easily script creating new audit tables as new tables are added.
In the case of second one, I'd suggest writing a proc to restore a specific record so that restoring is easy and you don't have to figure it out each time.
不是答案，只是进一步的问题：审计表的目的是什么？你为什么想要它们、需要它们或必须拥有它们？它们将如何使用，它们将回答哪些问题或解决哪些情况？它们的使用频率如何？您必须保留这些数据多长时间？到期后您将如何清除或存档它？
前面的两个答案 [theChrisKen，HLGEM] 并不同意，但根据他们之前所做的工作，我敢打赌它们都是正确的。如果您考虑如何使用它们以及该用途的性能要求，它们可能会帮助您确定哪种模型最适合您的情况。
Not an answer, just further questions: What is the purpose of your audit tables? Why do you want them, need them, or have to have them? How will they be used, what questions will they answer or situations will they address? How frequently or infrequently will they be used? How long must you keep this data available, and how will you purge or archive it after the expiration date?
The two preceding answers [theChrisKen, HLGEM] do not agree, yet--based on what they've worked on before--I'd bet they are both correct. If you contemplate how they will be used and the performance requirements of that usage, thay may help you determine which model is best for your situation.
我毫无疑问会选择1号。如果您决定向跟踪添加其他字段，并且除了消除 WHERE table=? 的必要性之外，添加的内容很少，那么第二个字段将很难维护。条款。 3号太杀伤力了。这就是备份的用途。
I would choose number 1 hands down. Number 2 would be hard to maintain if you decide to add additional fields to your tracking and adds very little besides removing the necessity of a WHERE table=? clause. Number 3 is overkill. That's what backups are for.