三个关联数据库表设计重构及历史追踪场景下外键约束设置咨询

前提术语修正 #

你描述的「带历史追踪字段、业务属性组合重复」的表设计属于**Type 2 慢速变化维度（SCD）**设计，核心逻辑是同一条业务实体的多次变更会生成多条行记录，每张表的自增ID是唯一行级主键，ProjectID/RiskID/EventID属于业务主键，用来标记同一业务实体的所有版本。

具体实现步骤 #

1. 基础表结构调整 #

所有表新增两个通用版本控制字段，用来区分历史版本和当前生效版本：

• is_valid TINYINT(1)：标记当前记录是否为生效版本，生效版本取值为1，历史版本取值为0
• effective_time DATETIME：记录当前版本的生效时间

2. 外键约束设置 #

外键统一引用关联表的唯一行主键ID，完全满足关系型数据库的外键参照要求：

• Event表新增risk_row_id BIGINT字段，添加外键约束关联Risk表的ID字段
• Risk表新增project_row_id BIGINT字段，添加外键约束关联Project表的ID字段
• Event表新增project_row_id BIGINT字段，添加外键约束关联Project表的ID字段

外键关联的是行级唯一的ID字段，不会受业务主键重复的影响，参照完整性约束可以正常生效。

3. 保留业务组合键的使用 #

给各表添加带版本标识的联合唯一约束，既允许同业务主键存在多个历史版本，又不会出现业务数据重复冲突，原有基于{ProjectID, EventID}、{ProjectID, RiskID}的业务查询无需改动：

• Project表添加联合唯一约束 UNIQUE KEY uk_project (ProjectID, effective_time)
• Risk表添加联合唯一约束 UNIQUE KEY uk_risk (ProjectID, RiskID, effective_time)
• Event表添加联合唯一约束 UNIQUE KEY uk_event (ProjectID, EventID, effective_time)

日常查询需要取当前生效数据时，只需要增加is_valid = 1的过滤条件即可。

通用方案 #

所有带版本追踪的表的外键设计都遵循统一逻辑：

• 行级自增ID作为唯一主键，作为外键引用的唯一目标
• 外键存储关联表的行ID，而非业务主键
• 业务主键与版本标识（生效时间/版本号）组成联合唯一约束，保证业务数据合法性
• 版本控制字段独立存储，不侵入原有业务查询逻辑

内容的提问来源于stack exchange，提问作者Vahe