A project I'm working on requires that a record be digitally "signed" and after that any modifications would create a new "version" of the row. The "signed" record can't be modified for regulatory reasons and new versions shouldn't be modified very often. In the past, done so by creating a separate logging table with the same schema as the main table with some extra columns for tracking who modified it and when.
我正在进行的一个项目要求对记录进行数字签名，在此之后，任何修改都将创建一个新的行“版本”。由于监管原因，“签名”记录不能修改，新版本不应该经常修改。在过去，通过创建一个与主表具有相同模式的单独日志表，并使用一些额外的列来跟踪谁修改了它以及何时修改了它，就可以做到这一点。
However, after doing some work with SharePoint where ALL data (including different versions) is put into the same table I thought of a different approach which I can't find any examples of people doing: I could put the new version of the row right in the same table and increment the version number. Then add the version number to the PK.
然而,与SharePoint做一些工作后,所有数据(包括不同版本)投入相同的表我想到不同的方法,我找不到任何的人做例子:我可以把新版本的相同的表行,增加版本号。然后将版本号添加到PK中。
PROS:
优点:
- Implementation is easy, just create an "Instead of update" trigger which performs an insert instead of an update of the row is "signed". I could easily add a IsCurrentVersion column to be updated in the trigger.
- 实现很简单，只需创建一个“而不是更新”触发器，该触发器执行插入操作，而不是对行进行“签名”更新。我可以很容易地在触发器中添加一个要更新的IsCurrentVersion列。
- Querying for older versions is easy, just get all the records with the ID I want let the user choose from the list.
- 查询旧版本很容易，只需用我想让用户从列表中选择的ID获取所有的记录。
- A trigger is nice because it guarantees that a row CAN'T be updated if signed (for regulatory and audit purposes).
- 触发器很好，因为它保证如果签名(用于管理和审核目的)，就不能更新行。
- Schema changes to the table don't have to be replicated to the mirror "logging" table.
- 对表的模式更改不必复制到镜像“日志”表中。
CONS:
缺点:
- The table could get a bit larger but most of the time the record won't be changed after "signing" it. The client estimated around 100,000 rows/year max at current usage levels. SQL Server can handle hundreds of millions of rows so this doesn't seem too bad.
- 这个表可能会变得更大一些，但大多数情况下，在“签名”后记录不会被更改。客户端估计在当前使用级别上大约有100,000行/年最大值。SQL Server可以处理数亿行，所以这看起来不算太糟糕。
- Indexing and performance could be an issue. SharePoint adds a tp_CalculatedVersion int to the PK where the calculated number is always 0 for the latest version. I could do the same and calculate it based off the Version number. Would that help performance?
- 索引和性能可能是一个问题。SharePoint向PK添加了一个tp_CalculatedVersion int，其中最新版本的计算值总是为0。我也可以这样做，然后根据版本号计算。这会帮助提高性能吗?
-
There is an extra step in querying the data to make sure you get the latest version but that could be handled in a SP.
查询数据还需要额外的步骤，以确保得到最新的版本，但这可以在SP中处理。
-
What other cons are there in this scenario. Am I missing anything??
在这个场景中还有什么其他的坏处。我漏掉了什么? ?
3 个解决方案
#1
2
If the requirement is that the signed data not be changed, then you should move it to another table. In fact, I might suggest moving it to another database/schema, where the only operation allowed on the table is inserting and reading records. You can use both permissions and triggers, if you really want to prevent updates.
如果要求不更改已签名的数据，则应该将其移动到另一个表。实际上，我可能建议将其移到另一个数据库/模式，其中唯一允许的操作是插入和读取记录。如果您真的想要防止更新，您可以同时使用权限和触发器。
You don't want to mess around with regulatory requirements. A complex schema that uses a combination of primary key with version, along with triggers, is a sign that there might be a simpler way.
你不希望把监管要求搞得一团糟。使用主键和版本的组合以及触发器的复杂模式表明可能存在更简单的方法。
The historical records can affect performance of the current records. If you end up in a situation where every record has changed 100 times, then keeping them in the same table is just going to slow down queries. Of course, you can embark on more complexity, in the form of partitioning the data. In the end, the solution is simpler: keep the data that cannot be changed in another table where it cannot be changed. You don't want to have to upgrade the hardware just because lots of history has accumulated.
历史记录可以影响当前记录的性能。如果您的情况是每条记录都更改了100次，那么将它们保存在相同的表中只会降低查询的速度。当然，您可以采用对数据进行分区的方式来进行更复杂的操作。最后，解决方案更简单:将不能更改的数据保存在另一个不能更改的表中。您不希望仅仅因为积累了大量的历史，就必须对硬件进行升级。
I would also suggest including an effective and end date in the history records. This will allow you to reconstruct all the data as of a particular date, something that users might find useful in the future.
我还建议在历史记录中列入一个有效的和结束的日期。这将允许您重构特定日期的所有数据，用户将来可能会发现这些数据很有用。
#2
2
I've seen this pattern used in an enterprise system before,and IMO it wasn't successful.
我以前在企业系统中看到过这种模式，而IMO并不成功。
- You are Mixing two different concerns here, viz storage of live and audit data. Queries to this table will always need to keep in mind whether they are seeking leaf or audit data (e.g. reports) - new team members may find this non intuitive. You would likely need to encapsulate this complexity with views etc.
- 您在这里混合了两个不同的关注点，即实时存储和审计数据。对该表的查询将始终需要记住它们是在查找leaf数据还是审计数据(例如报告)——新团队成员可能会发现这不是直观的。您可能需要用视图等来封装这种复杂性。
- As you mentioned performance will always be a concern. Inserting a new record will also need to update the previous record to mark it as inactive.You may also need to consider changing your clustered index to keep all versions on the same page.
- 正如你所提到的，性能将永远是一个问题。插入新记录还需要更新以前的记录，将其标记为非活动记录。您可能还需要考虑更改集群索引，以使所有版本保持在同一页面上。
- Foreign keys to this table are going to be problematic. Do you reference an exact version record? Do you then fix up the foreign keys to point to the new live leaf record?
- 这个表的外键将会有问题。您是否引用了一个确切的版本记录?然后，你会把外键设置为指向新的live leaf记录吗?
The one benefit I can think of doing this is that the audit table DDL will always be in synch with the live table - often with the 2 table strategy changes are made to the live, and the audit and trigger DDL isn't updated accordingly.
我认为这样做的一个好处是，审计表DDL始终与活动表保持同步——通常对活动表进行2个表策略更改，审计和触发器DDL不会相应地更新。
Overall, I would still recommend keeping your audit table separate.
总的来说，我仍然建议保持您的审计表独立。
#3
1
That's right. Audit trails can stay in an application for internal reporting/audit but infosec best practice mandates getting audit logs off the system where they are generated into your log management / SIEM solution.
这是正确的。审计跟踪可以保留在内部报告/审计的应用程序中，但infosec的最佳实践要求将审计日志从系统中取出，并将它们生成到日志管理/ SIEM解决方案中。
#1
2
If the requirement is that the signed data not be changed, then you should move it to another table. In fact, I might suggest moving it to another database/schema, where the only operation allowed on the table is inserting and reading records. You can use both permissions and triggers, if you really want to prevent updates.
如果要求不更改已签名的数据，则应该将其移动到另一个表。实际上，我可能建议将其移到另一个数据库/模式，其中唯一允许的操作是插入和读取记录。如果您真的想要防止更新，您可以同时使用权限和触发器。
You don't want to mess around with regulatory requirements. A complex schema that uses a combination of primary key with version, along with triggers, is a sign that there might be a simpler way.
你不希望把监管要求搞得一团糟。使用主键和版本的组合以及触发器的复杂模式表明可能存在更简单的方法。
The historical records can affect performance of the current records. If you end up in a situation where every record has changed 100 times, then keeping them in the same table is just going to slow down queries. Of course, you can embark on more complexity, in the form of partitioning the data. In the end, the solution is simpler: keep the data that cannot be changed in another table where it cannot be changed. You don't want to have to upgrade the hardware just because lots of history has accumulated.
历史记录可以影响当前记录的性能。如果您的情况是每条记录都更改了100次，那么将它们保存在相同的表中只会降低查询的速度。当然，您可以采用对数据进行分区的方式来进行更复杂的操作。最后，解决方案更简单:将不能更改的数据保存在另一个不能更改的表中。您不希望仅仅因为积累了大量的历史，就必须对硬件进行升级。
I would also suggest including an effective and end date in the history records. This will allow you to reconstruct all the data as of a particular date, something that users might find useful in the future.
我还建议在历史记录中列入一个有效的和结束的日期。这将允许您重构特定日期的所有数据，用户将来可能会发现这些数据很有用。
#2
2
I've seen this pattern used in an enterprise system before,and IMO it wasn't successful.
我以前在企业系统中看到过这种模式，而IMO并不成功。
- You are Mixing two different concerns here, viz storage of live and audit data. Queries to this table will always need to keep in mind whether they are seeking leaf or audit data (e.g. reports) - new team members may find this non intuitive. You would likely need to encapsulate this complexity with views etc.
- 您在这里混合了两个不同的关注点，即实时存储和审计数据。对该表的查询将始终需要记住它们是在查找leaf数据还是审计数据(例如报告)——新团队成员可能会发现这不是直观的。您可能需要用视图等来封装这种复杂性。
- As you mentioned performance will always be a concern. Inserting a new record will also need to update the previous record to mark it as inactive.You may also need to consider changing your clustered index to keep all versions on the same page.
- 正如你所提到的，性能将永远是一个问题。插入新记录还需要更新以前的记录，将其标记为非活动记录。您可能还需要考虑更改集群索引，以使所有版本保持在同一页面上。
- Foreign keys to this table are going to be problematic. Do you reference an exact version record? Do you then fix up the foreign keys to point to the new live leaf record?
- 这个表的外键将会有问题。您是否引用了一个确切的版本记录?然后，你会把外键设置为指向新的live leaf记录吗?
The one benefit I can think of doing this is that the audit table DDL will always be in synch with the live table - often with the 2 table strategy changes are made to the live, and the audit and trigger DDL isn't updated accordingly.
我认为这样做的一个好处是，审计表DDL始终与活动表保持同步——通常对活动表进行2个表策略更改，审计和触发器DDL不会相应地更新。
Overall, I would still recommend keeping your audit table separate.
总的来说，我仍然建议保持您的审计表独立。
#3
1
That's right. Audit trails can stay in an application for internal reporting/audit but infosec best practice mandates getting audit logs off the system where they are generated into your log management / SIEM solution.
这是正确的。审计跟踪可以保留在内部报告/审计的应用程序中，但infosec的最佳实践要求将审计日志从系统中取出，并将它们生成到日志管理/ SIEM解决方案中。