审核表中的字段（CreatedBy、UpdatedBy）。这是个好主意吗？
我正在使用一种产品，几乎每个表都有这些列。作为开发人员，我们经常必须加入用户表来获取创建记录的 ID，这只是代码中的一团糟。
我正在设计新产品并再次考虑这个问题。一定要这样吗？显然，知道谁创造了记录以及何时创造了记录是件好事。但是让 300 多个表引用同一个 User 表似乎不太好。
您如何处理这样的事情？我是否应该仅在 UI 上最有可能需要的主要实体上创建 CreatedBy 列，而不是处理加入？还是我应该把它到处放？或者也许有另一个“审核”表，我在其中存储所有这些内容并仅按需查找（不是每次在 UI 上显示实体时）
我只是担心每个 UI 查询都会命中用户表的性能方面。
编辑：这个将是 SQL Server 2008 R2 数据库
I was working with one product where almost every table had those columns. As developers we constantly had to join to Users table to get Id of who created record and it's just a mess in a code.
I'm designing new product and thinking about this again. Does it have to be like this? Obviously, it is good to know who created record and when. But having 300+ tables reference same User table doesn't seem to be very good..
How do you handle things like this? Should I create CreatedBy column only on major entities where it's most likely needed on UI and than deal with joining? Or should I go and put it everywhere? Or maybe have another "Audit" table where I store all this and look it up only on demand(not every time entity displayed on UI)
I'm just worrying about performance aspect where every UI query will hit User table..
EDIT: This is going to be SQL Server 2008 R2 database
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这种方法的问题在于您只知道谁创建了该行以及谁最后更改了该行。如果最后更新该行的人正在纠正前一个更新者的错误怎么办？
如果您有兴趣出于合规性或责任原因进行全面审核，您可能应该查看 SQL Server 审计。您可以指定要审计的表，可以动态更改这些表，而不必弄乱您的架构，并且您可以专门针对这些数据编写查询，而不是将审计逻辑与正常的应用程序查询逻辑混合在一起（不用介意扩大每个表）表本身的行）。这还允许您审核 SELECT 查询，而其他潜在的解决方案（触发器、CDC、更改跟踪 - 所有这些对于真正的审核目的要么需要更多工作，要么不完整）不会让您这样做那。
The problem with that approach is that you only know who created the row and who changed the row last. What if the last person to update the row was correcting the previous updater's mistake?
If you're interested in doing full auditing for compliance or accountability reasons, you should probably look into SQL Server Audit. You can dictate which tables you're auditing, can change those on the fly without having to mess with your schema, and you can write queries against this data specifically instead of mixing the auditing logic with your normal application query logic (never mind widening every row of the table itself). This will also allow you to audit
SELECTqueries, which other potential solutions (triggers, CDC, Change Tracking - all of which are either more work or not complete for true auditing purposes) won't let you do that.我知道这是一篇较旧的帖子，但避免查找用户表的一种方法是对审计字段进行非规范化。
因此，您可以在 CreatedBy 字段中插入用户名本身，而不是用户 ID。这将允许在不查看用户的情况下查看表，并且还允许用户表中的任何更改不反映在审核字段中。比如删除用户。
我通常将以下内容添加到表格末尾
I know that this is an older post, but one way to avoid the lookup on the user table is to de-normalize the audit fields.
So instead of a userid in the CreatedBy field you insert a username itself. This will allow for a review of the table without the user look and also allow for any changes in your user table not reflect in the audit fields. Such as deleted users.
I usually add the following to the end of a table