如何高效存储一对一会员与订阅表的订阅历史记录？

现有member与subscription两张一对一关联的表，表结构如下：

member表 #

TABLE member {
  member_id integer [not null, pk]
  phone_number varchar(15) [not null, UNIQUE]
  membership enum(['STUDENT', 'STAFF', 'STAFF_SON']) [not null]
  name varchar(100) [not null]
  faculty varchar(64)
  email varchar(100)
}

subscription表 #

TABLE subscription {
  member_id integer [not null, pk, ref: - member.member_id]
  active bool [not null]
  invoice varchar(256)
  subscription_date datetime [not null]
  expiration_date datetime [not null]
}

现需存储subscription表的历史记录，以跟踪会员订阅的变更（如续费）。我尝试设计了subscription_records表，与subscription表为一对多关系，结构如下：

TABLE subscription_records {
  id integer [pk]
  member_id integer [fk, ref: > subscription.member_id]
  invoice varchar(256) [fk, ref: > subscription.member_id]
  subscription_date datetime [fk, ref: > subscription.member_id]
  expiration_date datetime [fk, ref: > subscription.member_id]
}

请问该方案是否合理？恳请提供反馈及改进建议。注：数据库最多包含20000名会员。

你的现有设计存在明显问题，并不合理，主要问题如下：

• 外键定义完全错误：invoice、subscription_date、expiration_date这些是订阅的属性字段，把它们设为外键关联subscription.member_id既不符合数据库语法（外键只能关联主键/唯一键），也完全不符合业务逻辑，会直接导致表创建失败。
• 关联逻辑矛盾：你想让subscription_records跟踪subscription的变更，但却把member_id关联到subscription.member_id——而subscription的主键本身就是member_id，这相当于历史表直接关联会员，而非订阅的历史状态，和你“跟踪订阅变更”的目标不匹配。
• 缺少关键追溯信息：没有记录变更时间、变更类型（比如续费、激活、停用）等必要字段，就算表结构正确，也无法完整追溯订阅的变化过程。

针对你的需求，推荐两种成熟的历史记录存储方案，适配20000会员的规模完全没问题：

方案一：快照式历史表（完整记录每一次状态） #

这种方案会在订阅状态变更时，把当前subscription的完整状态复制到历史表，同时记录变更的关键信息，优点是查询历史状态非常直观。

表结构设计 #

TABLE subscription_records {
  record_id integer [pk, auto_increment]
  member_id integer [not null, fk, ref: > member.member_id]
  active bool [not null]
  invoice varchar(256)
  subscription_date datetime [not null]
  expiration_date datetime [not null]
  change_type enum(['CREATE', 'RENEW', 'DEACTIVATE', 'UPDATE']) [not null] -- 标记变更类型
  change_timestamp datetime [not null, default: current_timestamp] -- 变更发生时间
  operator varchar(100) -- 操作人（可选，根据实际业务需求添加）
}

操作逻辑 #

• 创建新订阅：插入subscription表的同时，往subscription_records插入一条change_type为CREATE的记录。
• 续费/修改订阅：先更新subscription表的最新状态，再往subscription_records插入对应类型的记录（比如续费就填RENEW）。
• 查询历史：直接按member_id过滤subscription_records，按change_timestamp排序就能看到完整的订阅变更历程。

方案二：变更日志表（仅记录变化字段） #

如果不需要存储完整快照，只想记录每个字段的变更细节，可以用这种方案，优点是节省存储空间（不过20000会员的规模，这点差异几乎可以忽略）。

表结构设计 #

TABLE subscription_changes {
  change_id integer [pk, auto_increment]
  member_id integer [not null, fk, ref: > member.member_id]
  field_name varchar(64) [not null] -- 变更的字段名，比如'expiration_date'、'active'
  old_value varchar(256) -- 字段旧值（如果是日期/布尔类型，可以转成字符串存储，也可以用JSON字段兼容多种类型）
  new_value varchar(256) -- 字段新值
  change_type enum(['UPDATE', 'RENEW']) [not null]
  change_timestamp datetime [not null, default: current_timestamp]
  operator varchar(100) -- 操作人（可选）
}

操作逻辑 #

• 每次修改subscription的字段时，针对每个发生变化的字段插入一条记录，明确记录字段名、新旧值。
• 这种方案适合需要精确追溯单个字段变更历史的场景，但查询某一时间点的完整订阅状态时，需要拼接所有相关变更记录，相对麻烦。

额外优化建议 #

1. 保留subscription作为当前状态表：不管用哪种方案，subscription表始终存储会员的最新订阅状态，历史表只存过往记录，这样查询当前状态的效率最高。
2. 添加索引：在历史表的member_id和change_timestamp字段上建立联合索引，能大幅提升按会员查询历史记录的速度。
3. 保证数据一致性：建议用数据库事务或者触发器来绑定subscription和历史表的操作，避免出现“更新了订阅表但没插入历史记录”的不一致情况。

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