维护跨多个表的实体的审计日志 [英] Maintaining audit log for entities split across multiple tables
问题描述
我们有一个实体分布在 5 个不同的表中.其中 3 个表中的记录是强制性的.其他两个表中的记录是可选的(基于实体的子类型).
We have an entity split across 5 different tables. Records in 3 of those tables are mandatory. Records in the other two tables are optional (based on sub-type of entity).
其中一个表被指定为主实体.其他四个表中的记录以来自 master 的唯一 id 为键.
One of the tables is designated the entity master. Records in the other four tables are keyed by the unique id from master.
在每个表上都存在更新/删除触发器后，记录的更改会将历史记录(从触发器内的已删除表)保存到相关的历史记录表中.每个历史表都包含相关的实体字段 + 时间戳.
After update/delete trigger is present on each table and a change of a record saves off history (from deleted table inside trigger) into a related history table. Each history table contains related entity fields + a timestamp.
因此，实时记录始终在实时表中，而历史/更改则在历史表中.历史记录可以根据时间戳列进行排序.显然，时间戳列在历史表之间没有关联.
So, live records are always in the live tables and history/changes are in history tables. Historical records can be ordered based on the timestamp column. Obviously, timestamp columns are not related across history tables.
现在是更困难的部分.
- 记录最初插入到单个事务中.将在单个事务中写入 3 或 5 条记录.
- 可以对 5 个表中的任何一个或所有表进行单独更新.
- 所有记录都作为单个事务的一部分进行更新.同样，将在单个事务中更新 3 或 5 条记录.
- 数字 2 可以重复多次.
- 数字 3 可以重复多次.
应用程序应该根据仅作为单个事务写入的记录(仅点 1,3 和 5)显示时间历史记录条目列表
The application is supposed to display a list of point in time history entries based on records written as single transactions only (points 1,3 and 5 only)
我目前在使用仅根据时间戳数据检索历史记录的算法时遇到问题.
I'm currently having problems with an algorithm that will retrieve historical records based on timestamp data alone.
添加一个 HISTORYMASTER 表来保存有关交易的额外信息似乎部分解决了这个问题.在每次交易之前，都会在 HISTORYMASTER 中添加一条新记录.新的 HISTORYMASTER.ID 在事务期间保存到每个实体表中.可以通过选择特定 HISTORYMASTER.ID 的第一条记录来检索时间点历史记录(按时间戳排序)
Adding a HISTORYMASTER table to hold the extra information about transactions seems to partially address the problem. A new record is added into HISTORYMASTER before every transaction. New HISTORYMASTER.ID is saved into each entity table during a transaction. Point in time history can be retrieved by selecting the first record for a particular HISTORYMASTER.ID (ordered by timestamp)
对于跨越多个表的实体，是否有更优化的方式来管理基于 AFTER (UPDATE, DELETE) TRIGGER 的审计表?
Is there any more optimal way to manage audit tables based on AFTER (UPDATE, DELETE) TRIGGERs for entities spanning multiple tables?
推荐答案
您的 HistoryMaster 似乎类似于我们在我们的一个系统中处理多个相关项目的历史记录的方式.通过在历史表中用一个点挂起所有相关的更改，然后很容易创建一个使用历史主节点作为中心并附加相关信息的视图.它还允许您不在不需要审计的历史记录中创建记录.
Your HistoryMaster seems similar to how we have addressed history of multiple related items in one of our systems. By having a single point to hang all the related changes from in the history table, it is easy to then create a view that uses the history master as the hub and attached the related information. It also allows you to not create records in the history where an audit is not desired.
在我们的例子中，主表被称为 EntityAudit(其中实体是被保留的主要"项目)并且所有数据都存储在与审计相关的 EntityHistory 表中.在我们的案例中，我们为业务规则使用了数据层，因此很容易将审计规则插入到数据层本身中.我觉得数据层是这种跟踪的最佳点当且仅当所有修改都使用该数据层.如果您有多个应用程序使用不同的数据层(或根本没有)，那么我怀疑创建主记录的触发器几乎是唯一的方法.
In our case the primary tables were called EntityAudit (where entity was the "primary" item being retained) and all data was stored EntityHistory tables related back to the Audit. In our case we were using a data layer for business rules, so it was easy to insert the audit rules into the data layer itself. I feel that the data layer is an optimal point for such tracking if and only if all modifications use that data layer. If you have multiple applications using distinct data layers (or none at all) then I suspect that a trigger than creates the master record is pretty much the only way to go.
如果您没有附加信息要在审计中跟踪(我们跟踪进行更改的用户，例如，不在主表上的内容)，那么我会考虑添加额外的信息主要"记录本身的审计 ID.您的描述似乎并不表明您对单个表的细微更改感兴趣，而只是对更新整个实体集的更改感兴趣(尽管我可能会错过阅读).不过，如果您不关心细微的编辑，我只会这样做.在我们的例子中，我们需要跟踪所有更改，甚至是相关记录.
If you don't have additional information to track in the Audit (we track the user who made the change, for example, something not on the main tables) then I would contemplate putting the extra Audit ID on the "primary" record itself. Your description does not seem to indicate you are interested in the minor changes to individual tables, but only changes that update the entire entity set (although I may be miss reading that). I would only do so if you don't care about the minor edits though. In our case, we needed to track all changes, even to the related records.
请注意，与源表相比，使用审计/主表的优势在于您对历史记录表进行了最小更改:单个审计 ID(在我们的示例中为Guid，尽管在非分布式数据库中自动编号会很好).
Note that the use of an Audit/Master table has an advantage in that you are making minimal changes to the History tables as compared to the source tables: a single AuditID (in our case, a Guid, although autonumbers would be fine in non distributed databases).
这篇关于维护跨多个表的实体的审计日志的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
