带时间维度的自引用地址表MySQL设计与EER模型相关问题

一、单表自引用方案相关问题 #

1. 自引用关系EER模型绘制方法 #

• 首先创建实体address，包含字段：key（主键，非空）、address、city、state、zip、start_date、end_date、ref_id（外键，可空）
• 在EER图中绘制一条从address.ref_id指向address.key的关联线，关联类型设置为零对一（一个地址最多关联一个上一版本地址，历史版本可以没有对应的新版本关联），关系标注为「关联上一版本地址」即可。

2. 删除历史记录的影响与删除限制 #

删除历史记录的影响完全由外键约束规则决定，常见场景如下：

• 外键规则为RESTRICT/NO ACTION：删除示例中的行1时数据库会直接报错，不允许删除，因为行2的ref_id仍指向行1的主键
• 外键规则为CASCADE：删除行1时，关联的行2会被同步删除，完全不适用该业务场景，会丢失当前有效地址
• 外键规则为SET NULL：删除行1时，行2的ref_id会被自动置为null，丢失地址的历史关联关系，也不推荐

业务层面不建议物理删除历史地址记录，设计生命周期字段的核心目的就是留存地址变更轨迹，删除历史会直接违背需求初衷。如果需要清理无效数据，建议新增逻辑删除字段标记作废，不要直接删除物理记录。

二、拆分方案EER模型绘制方法 #

拆分方案的核心是将地址基础属性和生命周期/版本关联逻辑解耦，绘制步骤如下：

1. 创建两个实体：
   • 第一个实体address_base（地址基础信息表），字段：key（主键，非空）、address、city、state、zip
   • 第二个实体address_lifecycle（地址生命周期关联表），字段：key（主键，非空）、address_id（外键，非空，对应用户示例中的id字段）、start_date、end_date、ref_id（外键，可空，关联上一版本生命周期记录）
2. 绘制第一条关联线：从address_lifecycle.address_id指向address_base.key，关联类型为多对一，一个地址基础信息可以对应多条生命周期记录
3. 绘制第二条自引用关联线：从address_lifecycle.ref_id指向address_lifecycle.key，关联类型为零对一，标注为「关联上一版本生命周期记录」即可。

补充选型建议 #

• 单表方案适合地址变更频率低、查询逻辑简单的场景，表结构简洁，维护成本低
• 拆分方案适合地址变更频繁、需要灵活扩展生命周期规则的场景，数据冗余更低，后续扩展字段不会影响地址基础信息表

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