每个数据库表的常见审核列有哪些？
我打算将“status”、“date_created”、“date_updated”包含到数据库中的每个表中。 “status”用于软删除行。
然后，我看到很少有人还在每个表中添加“user_created”、“user_updated”列。
如果我也添加这些列，那么每个表至少有 5 列。 这会带来太多的开销吗？
您认为拥有这五列是个好主意吗？
另外，“user_created”中的“user”是否意味着数据库用户？或应用程序用户？
I was going to include 'status', 'date_created', 'date_updated' to every table in database.
'status' is for soft deletion of rows.
Then, I've seen few people also add 'user_created', 'user_updated' columns to each table.
If I add those columns too, then I will have at least 5 columns for every table.
Will this be too much overhead?
Do you think it's a good idea to have those five columns?
Also, does the 'user' in 'user_created' mean database user? or application user?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
根据上面的评论，建议仅向那些实际需要的表添加审计。
您通常希望审核应用程序用户 - 在许多情况下，应用程序（例如 Web 或 SOA）可能会使用相同的凭据连接所有用户，因此存储数据库登录名是没有意义的。
恕我直言，
创建日期/最后更新日期/lastupdateby模式永远不会给出完整的图片，因为您只能看到谁制作了最后一次更改，但看不到更改了什么。如果您正在进行审核，我建议您使用审核等模式进行完整的更改审核 触发。如果您对表的插入/更新/删除操作是通过存储过程封装的，您还可以避免使用触发器。确实，审计表会变得非常大，但它们通常不会被太多查询（通常只是在政治迫害中），并且可以存档，可以轻松地按日期分区（并且可以设为只读）。使用单独的审核表，您不需要DateCreated或LastDateUpdated列，因为可以派生这些列。然而，您通常仍然需要最后一次更改的用户，因为 SQL 将无法派生应用程序用户。如果您确实决定逻辑删除，我会避免使用“状态”作为指示逻辑删除的字段，因为您可能有对流程状态进行建模的表（例如付款状态等）。 code> 或
char字段（例如ActiveYN或IsActive）对于逻辑删除很常见。逻辑删除可能很麻烦，因为您的所有查询都需要过滤掉
Active=N记录，并且在事务表中保留已删除的记录可能会使这些表变得比必要的更大，尤其是在Many 上：许多/连接表。性能也会受到影响，因为 2 状态字段不太可能具有足够的选择性以在索引中有用。在这种情况下，进行完整审核的物理删除可能更有意义。As per comments above, would advise adding auditing only to those tables actually requiring it.
You generally want to audit the application user - in many instances, applications (such as Web or SOA) may be connecting all users with the same credential, so storing the DB login is pointless.
IMHO, the
date created/last date updated/lastupdatebypatterns never give the full picture, as you will only be able to see who made the last change and not see what was changed. If you are doing auditing, I would suggest that instead you do a full change audit using patterns such as an audit trigger. You can also avoid using triggers if your inserts / updates / deletes to your tables are encapsulated e.g. viaStored Procedures. True, the audit tables will grow very large, but they will generally not be queried much (generally just in witch-hunts), and can be archived, easily partitioned by date (and can be made readonly). With a separated audit table, you won't need aDateCreatedorLastDateUpdatedcolumn, as this can be derived. You will generally still need the last change user however, as SQL will not be able to derive the application user.If you do decide on logical deletes, I would avoid using 'status' as an field indicating logical deletes, as it is likely you have tables which do model a process state (e.g. Payment Status etc.) Using a
bitorcharfield such asActiveYNorIsActiveare common for logical deletes.Logical deletes can be cumbersome, as all your queries will need to filter out
Active=Nrecords, and by keeping deleted records in your transaction tables can make these tables larger than necessary, especially onMany : Many / junctiontables. Performance can also be impacted, as a 2-state field is unlikely to be selective enough to be useful in indexes. In this case, physical deletes with the full audit might make better sense.简单的答案 - 只将数据库中需要的内容放入数据库中。
Simple answer - only put it in your database what you need in your database.
当然，我以前用过这五个。当我想通过网络应用程序跟踪谁正在创建和（最后）编辑记录时，当发生这种情况时，我会包含时间戳和登录用户（但不是数据库用户，这不是我的系统的设置方式；我们使用一个帐户来进行所有数据库交互）。
同样，如果用户要更改记录的状态，状态也很有用。如果它从“在线”到“离线”再到“存档”，该记录可以反映这一点。
然而，我不会在每张桌子上使用这些，你也不应该。有时，我的表仅用于存储部分记录（规范化），或者只是不存储就需要谁创造的地位或时间而言，没有价值。
对于每个表，您应该考虑的是主键字段。除非你的方法比听起来更复杂，否则你几乎总是想要一种。有些东西不一定需要一个（例如，状态列表可以使缩写唯一）。但这对于大多数表来说比一系列时间戳和状态字段更重要。
I've used all five before, sure. When I want to track who, through a web app, is creating and (last) editing records, and when that happens, I include timestamps and the logged-in user (but not the DB user, that's not how my system is setup; we use one account for all DB interaction).
Likewise, status can also be useful if users are changing a record's, well, status. If it goes from being "Online" to "Offline" to "Archive", that record can reflect that.
However, I don't use these for every table, nor should you. Sometimes I have tables that are meant only to store parts of a record (normalized), or just don't have a value as far as needing a status or time created by who.
What you should be considering for every table is a Primary Key field. Unless you are more sophisticated in your approach than you sound, you will almost always want one. Some things don't necessarily need one (a states list, for instance, could Unique the abbreviation). But this is more important to most of your tables than a series of timestamp and status fields.