在sql中跟踪记录更新的一些最佳实践是什么？(What are some of the best practices for tracking updates to a record over tim

在sql中跟踪记录更新的一些最佳实践是什么？(What are some of the best practices for tracking updates to a record over time in sql? [closed])

我有一个带有主键@productid（bigint），产品号（int）和版本（int）的产品表

每当有人仅对产品记录进行更改时，我计划在数据库中插入一个具有相同产品编号和版本号+1的新行。这将为我提供记录所需的历史跟踪，因为我可以看到版本随时间变化。

/* Selecting the current version is simple */ Select top 1 * from products where productnumber = @productnumber order by version desc

但是，我的问题来自外键一对多或多对多关系表。 此表指向还需要跟踪的许多其他（即具有日期范围，产品类别等的产品定价）。

/* Product categories, pricing */ /* Should I use @productnumber here? How do I track changes to these records? */ select name from productcategories where productid = @productid select price from productpricing where productid = @productid and StartDate > @StartDate and EndDate <@Enddate

所以现在任何时候都有版本更改，我打算重新插入新的类别和定价记录以及生成的新主键产品ID。这将导致大量重复，特别是如果没有更改对这些记录做了。

此问题也随之而来 - 如果删除某个类别但产品记录没有变化会怎样？ 我想看看谁删除了这个类别。 基本上，每个表都需要进行全面审核。

我已经看到了一些不同的例子，但是大多数例子似乎只处理一个表中的记录而不是一对多或多对多关系的记录。 我希望这可以在不需要额外表格的情况下完成。

有没有更好的方法或做法？ 这会成为一场表演噩梦吗？

I have a product table with a primary key @productid (bigint), a product number (int), and a version (int)

Any time someone makes changes to the product record ONLY, I plan on inserting a new row in the database with the same product number and version number + 1. This will provide me with the historical tracking I need for the record because I can see the version changes throughout time.

/* Selecting the current version is simple */ Select top 1 * from products where productnumber = @productnumber order by version desc

However, my problem comes in with the Foreign key one-to-many or many-to-many relationship tables. This table points to many others (i.e. product pricing with date ranges, product categories, etc.) which also need to be tracked.

/* Product categories, pricing */ /* Should I use @productnumber here? How do I track changes to these records? */ select name from productcategories where productid = @productid select price from productpricing where productid = @productid and StartDate > @StartDate and EndDate <@Enddate

So now any time there is a version change, I plan to to re-insert new category and pricing records with the new Primary Key product id that was generated..This is going to lead to a ton of duplicates, especially if no changes were made to these records.

Also the issue comes in with - what happens if a category is removed but there were no changes to the product record? I would want to see who removed the category. Essentially, a full audit is needed on each table.

I have seen some different examples but most of them only seem to deal with a record in one table and not a record that is a part of one-to-many or many-to-many relationships. I was hoping this could be done without the need of additional tables.

Are there any better methods or practices? Is this going to be a performance nightmare?

最满意答案

如果您使用的是较新版本的SQL Server，则可以查看时态表，因为这可能是您的最佳选择。

如果你需要支持旧版本，我首选的方法是让历史表包含一个新的PK列，更改标志（I，U，D），修改日期，进行更改的用户以及来自的所有列主表。 然后，我将与非历史记录表的PK相关的列编入索引。 如果不在其中放置逻辑，触发器不会对性能产生太大影响。 示例（伪代码）：

Table: Car Column: CarID INT IDENTITY(1,1) Primary Key Column: Name varchar Table: Car_hist Column: Car_histID INT IDENTITY(1,1) Primary Key Column: Change char(1) Column: DateOfChange DateTime2 Column: ChangedByUser (varchar or int) Column: CarID <-add a unique non-clustered index Column: Name varchar

您可以在SQL中编写生成器来生成脚本以创建历史表，索引等。如果您有一致的表设计实践，它会有所帮助。

现在的原因是：我很少查询历史表，但是当我这样做时，几乎总是只有一条记录才能看到发生了什么以及谁更改了它。 此方法允许您快速从父表的PK值中选择历史记录，并轻松地将其读取为历史更改日志（谁更改了什么以及何时更改）。 我不知道你在设计中如何做到这一点。 如果你真的很光滑，你可以找到或写一个为你区分行的网格，你可以很快看到改变了什么。

If you are using a newer version of SQL Server as you are, you can should look into temporal tables as this might be your best option.

If you need to support older versions, my preferred method is to have a history table with a new PK column, change flag (I,U,D), a date modified, user that made the change, and all of the columns from the primary table. I then index the column related to the PK of the non-history table. Triggers don't impact performance too much if you don't put logic in them. Example (pseudocode):

Table: Car Column: CarID INT IDENTITY(1,1) Primary Key Column: Name varchar Table: Car_hist Column: Car_histID INT IDENTITY(1,1) Primary Key Column: Change char(1) Column: DateOfChange DateTime2 Column: ChangedByUser (varchar or int) Column: CarID <-add a unique non-clustered index Column: Name varchar

You can write a generator in SQL that generates the script to create the history table, indexes, etc. It helps if you have a consistent table design practice.

Now the reason: I rarely have to query history tables, but when I do, it is almost always for a single record to see what happened and who changed it. This method allows you to select from the history on the parent table's PK value quickly and read it as a historical change log easily (who changed what and when). I don't see how you can do that in your design. If you are really slick, you can find or write a grid that diffs rows for you and you can quickly see what changed.