日志表中的外键
嘿，这里有一个设计问题：
我正在开发一个项目，要求我跟踪用户何时执行插入、更新或删除。我想要决定的是是否让日志表引用受影响行的主键或其他标识符。为了在使用外键时记录删除，我需要为每个表都有一个已删除列。但是，如果我使用一些没有外键的命名标识符，我最终将在日志表中遇到名称重复，并且该条目所指的内容将变得不清楚。有谁有这方面的实际经验，特别是使用已删除列来维护完整日志的性能影响？
一个相关的问题还在于日志表本身的设计。如果我在单个日志表中使用外键引用，我的第一反应是为每个正在监视更改的表创建一个引用列。这对我来说并不理想，因为我必须添加一个新表，然后必须更改日志表和任何关联的存储过程。我看到的一种替代方案是有两列：TableName 和 RowId。但这不会有固有的外键引用，因为不知道引用的是哪个表。
感谢您的任何意见！
Hey, so a bit of a design question here:
I am working on a project that requires me to keep track of when a user performs an Insert, Update, or Delete. The thing I am trying to decide on is whether to have the Logs table reference the Primary Key of the affected row or some other identifier. In order to log a deletion when using Foreign Keys I would need to have a Deleted column for every table. However, if I was using some named identifier without a Foreign Key I will eventually run into name duplication in the Logs table and it will become unclear what the entry refers to. Does anyone have any practical experience with this, specifically the performance impact of using a Deleted column in order to maintain complete Logs?
A related question also is in the design of the Logs table itself. If I go with Foreign Key references in a single Log table, my first instinct would be to create a reference column for each table that is being monitored for changes. This doesn't feel ideal to me as if I have to add a new table down the line I have to then change the Logs table and any associated sprocs. One alternative I can see is to have two columns, TableName and RowId. But this will have no inherent Foreign Key references since it isn't known what table is being referred to.
Thanks for any input!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
首先，根据行数和您拥有的索引类型，添加已删除的列可能比删除该行执行得更好。
其次，我认为最好的选择是将整行存储在日志表中（这也将允许记录任何更新）。您还可以使用更规范化的日志表来完成此操作 - 如下所示：
不过，这将需要更多工作，而且您不会从中得到太多好处。我建议您只需将所有数据存储在重复的表中 - 使用附加列来显示所采取的操作类型。
至于对行标识符或其他值的引用，无论您如何执行，都会遇到相同的问题。为了确定哪一行受到影响，该行中也必须存在相同的唯一值。一旦该行被删除，那么该唯一值也将被删除（顺便说一下，GUID（t-sql 中的唯一标识符）是一个选项）。
First, depending on the number of rows and what kind of indexes you have, adding the deleted column might perform better then deleting the row.
Second, I think the best option is to store the entire row in the log table (this will allow any updates to be logged as well). You could also do this with a more normalized log table - something like this:
That would be a lot more work, though, and you wouldn't get that much out of it. I would recommend you simply store all data in a duplicate table - with the additional columns to show the kind of action that was taken.
As for the reference to the row's identifier or some other value, would would have the same problem however you did it. In order to identify what row was effected, the same unique value would have to exist in that row as well. As soon as that row is deleted, so then is that unique value (GUID (uniqueidentifier in t-sql) - is an option, by the way).
RE：一张日志表而不是影子表。
我完全不同意。
您的目标是：
如果每个表都有一个影子表，您如何回答以下问题：
您必须仔细检查每一个影子表才能弄清楚这一点。如果您开始记录一个新表，您必须记住更改这些存储过程以同时命中该新的影子表。
如果您像其他人建议的那样有一个日志表，那么您可以使用简单的 SQL 轻松回答所有这些问题...稍微难一点的是 UN-DELETE 或 UN-UPDATE。但你构建它并不是为了这个目的，你构建它是为了跟踪用户编辑，而我列出的查询或类似的查询将是你的主要业务。
关于逻辑删除。
如果该表是您无法从中删除的其他表的父表，那么它们会很有用。就像员工和工资单一样。您无法删除具有法律必须保留的工资单记录的员工。如果这就是您逻辑上删除的原因，那就太好了。但是，如果你从逻辑上删除，以便将 PK 与 FK 保留在日志表中，那么我认为你买的是一个伤害世界，但效果并不好。
如果您有任何代码直接访问这些表，您现在必须将它们全部更改为包含 DELETED = 'F' 谓词。如果从头开始，您可以为每个逻辑删除的表创建一个嵌入 DELETED = 'F' 谓词的视图，并且永远不会在表本身上授予 select。
RE: one log table instead of shadow tables.
I couldn't disagree more.
Your goal is to:
If you have a shadow table for every table how do you answer these questions:
You'll have to go through EVERY SINGLE shadow table to figure that out. And if you ever start logging a new table, you'll have to remember to change those sprocs to also hit that new shadow table.
If you have one log table as others have suggested, you can answer all of those questions very easily with simple SQL... what's a LITTLE harder to do is UN-DELETE or UN-UPDATE. But you're not building it for that, you're building it to track user edits, and the queries I listed, or ones like them will be your bread-and-butter.
Regarding Logical Deletes.
They can be useful if that table is the parent of other tables you CAN'T delete from. Like employee and payroll. You can't delete an employee with payroll records that must be kept by law. If that's why you're logically deleting, great. But if you're logically deleting so that you keep the PK to an FK in a log table then I think you're buying a world of hurt for not a lot good.
If you have ANY code hitting those tables directly, you'll now have to change them ALL to included a DELETED = 'F' predicate. If you start from scratch you can make a view for EVERY logically deleted table that embeds the DELETED = 'F' predicate and never grant select on the table itself.
您不需要日志表中的四列吗？
用户 ID、表名、Id、操作。Action将是“删除”、“更新”或“插入”，Id将是相关表的主键，其余的都是不言自明的。这样您就不必拥有大量列和外键，这只会使插入日志表的速度变慢。无论如何，您都需要使用触发器来执行此操作，因此将表名添加到 Log 表不会有问题。
Wouldn't you just need four columns in the Log table?
UserId,TableName,Id,Action.Actionwould be "delete", "update" or "insert",Idwould be the primary key of the table in question and the rest, well, are self explanatory.This way you don't have to have a lot of columns and foreign keys, which will only make inserting into the Log table slower. You are going to need to do this with triggers anyway, so adding the table name to the Log table will not be a problem.
假设您跟踪的每个表的日志信息都是相同的，我将使用包含 TableID 和 RowPKValue 列的单个日志表。确实，您无法将 RowPKValue FK 返回到每个单独的表，但由于用户（或除 Log SP 之外的任何代码）永远不会触及日志表，我认为它相当安全。
我肯定会使用正在记录的行的 PK 值，而不是任何其他值（即使您没有 FK 引用它），因为这就是 PK 值的用途。
至于删除问题，我认为这取决于 1) 您期望执行的删除次数以及 2) 您必须将“取消删除”作为应用程序一部分的可能性。
如果 DELETE 数量相对较少，则可以将它们保留在带有已删除标志的表中，然后在某个指定时间段结束时将其删除。如果您这样做，我建议您采用一种方案，其中基表称为 CustomerAll 之类的名称，并且为前端程序员提供一个表视图
CREATE VIEW Customer AS SELECT * FROM CustomerAll WHERE Deleted = false使用。如果您有大量 DELETE，我会在 DELETE 时将它们移动到第二个表 (CustomersDeleted) 中，甚至移出数据库，具体取决于您认为需要再次查找它们的可能性。
Assuming the log information for each table you're tracking is identical, I would go with a single log table with TableID and RowPKValue columns. It's true that you cannot FK that RowPKValue back to each individual table, but since the log table will never be touched by users (or any code other than your Log SP) I think it's reasonably safe.
I would definitely use the PK value of the row-being-logged rather than any other value (even if you don't have an FK referencing it) because that's what PK values are for.
As for the DELETE issue, I think it depends on 1) the number of DELETEs you expect to perform and 2) the likelihood that you are going to have to feature "Undelete" as part of the application.
If you have a relatively small number of DELETEs, you can keep them in the table with a deleted flag and then remove them when some specified period is up. If you do this, I recommend a scheme in which the base table is called something like CustomerAll and you have a single table view
CREATE VIEW Customer AS SELECT * FROM CustomerAll WHERE Deleted = falsefor the front end programmers to use.If you have a large number of DELETEs, I would move them upon DELETE into a second table (CustomersDeleted) or even out of the database, depending on how likely you think it is you'll ever need to look them up again.
您也可以使用 2 表日志文件设置。
LogA - Log_id、Tablename、action、date_time
LogB - Log_ID、table_id、columnchanged、old_value、new_value
（编辑以校准，上面的“table_id”指的是该表中的主键。多个 PK 可能需要多个字段）
在 new_value 字段中使用 null 值表示删除，在 old 值中使用 null 表示插入。
如果您希望避免每个表上都有“已删除”列，则可以创建一个已删除表来存储已删除的行，并且十个使用视图仅显示活动记录（IE 记录在已删除的记录中没有唯一的键）桌子）。
将会有很多有效的设计方法......
You can use a 2 table log file setup as well.
LogA - Log_id,Tablename, action, date_time
LogB - Log_ID,table_id,columnchanged,old_value,new_value
(edit to calrify, 'table_id' above refers to whatever is your primary key in that table. Multiple PK's may require more than one field here)
Use a null value in the new_value field to represent a delete and a null in the old value to represent an insert.
If you prefer to avoid a 'deleted' column on each table, you can create a deleted table that stores which rows were deleted and ten use a view to show only active records (IE records that don't have their unqiue key inside the deleted table).
There will be alot of valid ways of designing this...