我正在为应用程序的一个新部分设置mysql中事务表的历史数据需求。最初在旧版本中，交易不是历史性的，结构如下：
id|buyerid|prodid|price|status
和其他字段，在访问"事务详细信息"页面的链接中引用id，并在应用程序的其他表中用作外键，以引用特定事务用于各种目的。
现在的要求是回答像"Show all transaction that had particular status Feb 2014"和"What did a transaction look like in Feb 2014"这样的报告问题。
我目前正在测试的新设计如下：
id|buyerid|prodid|price|status|active|start_date|end_date
其中active用于指示最新记录，start是创建时，没有要修改的记录，而是填充结束日期，并创建具有相同详细信息的新记录加上修改。
现在的问题是，如何处理事务id字段？因为在这个新设计中，它更像是一个历史id，不能用于整个应用程序的外键，因为它会随着每次更新而更改。
我能想到两种选择：
创建一个单独的表
transaction_ids，其中只有一列，主键自动递增tid，并在tid的主事务表中创建一个外键列-每次创建全新的事务时，插入id表，并将该id用于tid，以在整个系统中跟踪该特定事务。buyerid和prodid的组合在我的应用程序中总是唯一的，没有买家可以两次获得相同的产品。
第二种解决方案更好吗？有人知道处理这个问题的更好方法吗？
您试图实现的目标称为事件源
考虑改变事务状态的事件，而不是及时跟踪状态本身
您仍然拥有具有自己主键的事务，并且应用每个事件重建当前(或过去(状态。
我还建议您开始对业务模型进行编码，然后再考虑持久性以及将其映射到数据库的最佳方式。
第二个解决方案看起来更好，尽管我会说你的问题有很多歧义。
我的意思是，第二个解决方案更好，因为您在解决方案1中谈论的transaction_ids表基本上是REDUNDANT。它没有解决任何目的。即使事务id在事务表中重复，也不意味着需要有一个单独的表来生成id并使其成为PK-FK关系。最有可能的是，您仍然会按照用户id和产品id查询数据，而不是按照事务id 查询数据
基本上，您需要的是某种审计历史记录表，在其中您为完成的每一项操作/事务/修改插入一条记录，并捕获一些基本细节，如用户名、日期/时间、旧值、新值等。您不需要状态或开始日期和结束日期列。一旦一条记录被插入到这个审计历史表中，那么它就再也不会被触碰了。
你必须仔细设计你的报告。
考虑到前面的两个答案，以下是我将采用的解决方案：我的应用程序中的所有数据更新都通过一个单独的函数进行，该函数已经设置为审核我选择的特定字段，因此我将在其他函数中标记要审核的事务status。审计表的表结构类似于此：
|id|table|table_id|column|old_val|new_val|who|when|
只是通过对象id而不是简单的表名，有一个更高级的对象映射。然后，我可以在Join to the main，normal not historical transactions表中使用这些数据来提供所需的报告。