sql - 跟踪对象字段变化的最佳架构是什么？

Question

我们有一个基于 SQL 数据库构建的 Web 应用程序。几种不同类型的对象可以添加注释，其中一些对象需要字段级跟踪，类似于在大多数问题跟踪系统(例如状态、分配、优先级)上跟踪字段更改的方式。我们想显示更改是谁，以前的值是什么，以及新的值是什么。

在纯粹的设计级别，最简单的方法是跟踪通用表中任何对象的每个更改，其中列有对象类型、对象主键、进行更改的用户的主键、字段名称和新旧值(value)观。在我们的例子中，如果用户在进行更改时输入了评论，这些也可以选择有一个评论 ID。

然而，随着这些数据的增长速度有多快，这是最好的架构吗？将这种类型的功能添加到已经很大规模的应用程序中通常采用哪些方法？

[ 编辑 ] 我在这个问题上开始悬赏，主要是因为我想特别找出在处理规模方面最好的架构是什么。汤姆 H. 的回答是有用的，但推荐的解决方案似乎是相当低效的(对象的每个新状态都有一个新行，即使许多列没有改变)并且不可能考虑到我们必须也能够跟踪对用户创建的字段的更改。特别是，我可能会接受一个可以解释常见问题跟踪系统(JIRA 或类似系统)如何实现这一点的答案。

Answer 1

为此，您有多种选择。您可以拥有基本上反射(reflect)基表的审计表，但还包括更改日期/时间、更改类型和用户。这些可以通过触发器更新。但是，此解决方案通常更适合幕后审计 (IMO)，而不是解决特定于应用程序的要求。

第二个选项正如你所描述的。您可以有一个通用表来保存每个单独的更改，并带有一个类型代码来显示哪个属性发生了更改。我个人不喜欢这个解决方案，因为它可以防止在列上使用检查约束，还可以防止外键约束。

第三个选项(这将是我根据给定信息的初始选择)将拥有一个单独的历史更改表，该表通过应用程序更新，并包括每个表的 PK 以及您要跟踪的列.它与第一个选项略有不同，因为应用程序将负责根据需要更新表。在你的情况下，我更喜欢这个而不是第一个选项，因为你确实有一个你想要解决的业务需求，而不是像审计这样的后端技术需求。通过将逻辑放在应用程序中，您可以获得更大的灵活性。也许您不想跟踪某些更改，因为它们是维护更新等。

使用第三个选项，您可以在基表中拥有“当前”数据，也可以在历史表中保留历史记录中的每一列。然后，您需要查看最新行以获取对象的当前状态。我更喜欢这样，因为它避免了数据库中重复数据或必须查看多个表以获取相同数据的问题。

所以，你可能有:

Problem_Ticket(ticket_id，ticket_name)
Problem_Ticket_History(ticket_id、change_datetime、描述、评论、用户名)

或者，您可以使用:

Problem_Ticket(ticket_id，ticket_name)
Problem_Ticket_Comments (ticket_id, change_datetime, comment, username)
Problem_Ticket_Statuses(ticket_id、change_datetime、status_id、用户名)