关系型数据库中MyEntity撤销操作的数据库级设计方案咨询

针对你需要跟踪MyEntity更新并实现单次撤销（无法撤销已执行的撤销操作）的需求，以下是三种适合的关系型数据库存储模型：

1. 全量历史快照模型 #

这是你初步考虑方案的优化版，核心为每个实体的每次变更保存完整状态快照。

表结构设计 #

• MyEntity表（存储最新状态）：保留原有的entity_id（主键）、StartDate、EndDate、Color字段
• MyEntityHistory表（存储历史快照）：
  • history_id（主键）
  • entity_id（外键关联MyEntity.entity_id）
  • StartDate、EndDate、Color（完整复制当前实体状态）
  • operation_type（枚举：CREATE/UPDATE）
  • operation_timestamp（操作时间）
  • is_reverted（布尔值：标记该快照是否已被用于撤销，默认false）

操作流程 #

• 创建实体：插入MyEntity表，同时将相同状态插入MyEntityHistory（operation_type=CREATE）
• 更新实体：先将MyEntity当前状态插入MyEntityHistory（operation_type=UPDATE），再更新MyEntity的最新状态
• 撤销操作：
  1. 按operation_timestamp倒序查询MyEntityHistory中entity_id匹配且is_reverted=false的第一条记录
  2. 将MyEntity的状态更新为该快照的字段值
  3. 标记该快照的is_reverted=true（避免被重复用于撤销）

优缺点 #

• 优点：逻辑简单，撤销时直接读取完整快照，无需计算变更；历史状态查询直观
• 缺点：存储空间占用较大，尤其是实体字段较多或变更频繁时，会产生大量重复数据

2. 增量变更日志模型 #

只记录每次更新中实际变更的字段及新旧值，而非完整状态，适合字段多、每次仅修改少量字段的场景。

表结构设计 #

• MyEntity表（存储最新状态）：同全量模型
• MyEntityChangeLog表（存储变更细节）：
  • log_id（主键）
  • entity_id（外键关联MyEntity.entity_id）
  • changed_field（字符串：标记变更的字段名，如"StartDate"/"EndDate"/"Color"）
  • old_value（存储字段变更前的值）
  • new_value（存储字段变更后的值）
  • operation_timestamp（操作时间）
  • version（整数：同一次更新的多条变更记录共享同一版本号）
  • is_reverted（布尔值：标记该版本是否已被撤销，默认false）

操作流程 #

• 更新实体：
  1. 生成唯一的version号
  2. 对每个被修改的字段，在MyEntityChangeLog中插入一条记录，记录字段名、新旧值及当前version
  3. 更新MyEntity的最新状态
• 撤销操作：
  1. 按operation_timestamp倒序查询MyEntityChangeLog中entity_id匹配且is_reverted=false的最大version
  2. 批量查询该version下的所有变更记录，将MyEntity的对应字段改回old_value
  3. 标记该version下的所有记录is_reverted=true

优缺点 #

• 优点：存储空间占用小，仅记录变更内容
• 缺点：撤销逻辑复杂，需按版本号聚合变更字段；查询历史状态需拼接多次变更结果，性能较差

3. 版本化实体模型 #

将实体的所有版本直接存储在主表中，通过标记区分当前有效版本，无需额外历史表。

表结构设计 #

• MyEntity表（存储所有版本）：
  • entity_record_id（主键，自动生成）
  • business_id（业务主键，标识同一实体的不同版本）
  • version（整数：版本号，自增）
  • StartDate、EndDate、Color
  • is_current（布尔值：标记是否为当前有效版本，默认false）
  • operation_type（枚举：CREATE/UPDATE）
  • operation_timestamp（操作时间）
  • is_reverted（布尔值：标记该版本是否被撤销过，默认false）

操作流程 #

• 创建实体：插入一条记录，business_id为业务唯一标识，version=1，is_current=true，operation_type=CREATE
• 更新实体：
  1. 查询当前business_id对应的is_current=true的记录，获取最新version号
  2. 插入新记录，version=最新version+1，is_current=true，operation_type=UPDATE，同时将原记录的is_current=false
• 撤销操作：
  1. 查询当前business_id对应的is_current=true的记录，获取其version号
  2. 找到version=当前version-1且is_reverted=false的记录，将其is_current=true
  3. 标记当前版本的is_current=false和is_reverted=true

优缺点 #

• 优点：无需额外历史表，数据集中；查询历史版本直接过滤version即可
• 缺点：主表数据量会随变更次数增长，需定期归档旧版本；查询最新状态需加is_current=true条件

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