借助IsActive、EffectiveDate实现维度表历史数据留存的方案咨询

规则校验提前确认 #

• 你当前使用Name+Surname+Age作为员工唯一识别键，需提前确认业务侧可接受该规则的潜在冲突风险：重名且同年龄的不同员工会被误识别为同一人，后续如果能拿到工号、身份证号这类唯一业务ID建议优先替换
• 确认IsActive仅保留0（失效）、1（生效）两个枚举值，EffectiveDate默认取数据写入当天日期，无需业务侧手动传入

核心写入逻辑实现 #

每次有员工数据需要写入时，按以下顺序执行操作，建议两个步骤放在同一个数据库事务中，避免中间出错导致数据不一致：

1. 标记旧生效记录失效 #

单条更新场景SQL示例：

UPDATE 员工维度表
SET IsActive = 0
WHERE 
  Name = '目标员工名'
  AND Surname = '目标员工姓'
  AND IsActive = 1;

批量更新场景SQL示例：

UPDATE 员工维度表 a
JOIN 待写入员工临时表 b
  ON a.Name = b.Name
  AND a.Surname = b.Surname
SET a.IsActive = 0
WHERE a.IsActive = 1;

2. 插入新的生效记录 #

INSERT INTO 员工维度表 (Name, Surname, Age, IsActive, EffectiveDate)
SELECT 
  Name,
  Surname,
  Age,
  1 AS IsActive,
  CURDATE() AS EffectiveDate -- 不同数据库语法略有差异：SQL Server用GETDATE()，Oracle用SYSDATE
FROM 待写入员工临时表;

常用查询逻辑 #

• 查询当前所有生效员工：

SELECT * FROM 员工维度表 WHERE IsActive = 1;

• 查询单个员工的所有历史变更记录：

SELECT * FROM 员工维度表
WHERE Name = 'John' AND Surname = 'Doe'
ORDER BY EffectiveDate ASC;

• 查询指定历史日期的员工快照：

SELECT * FROM 员工维度表
WHERE 
  Name = 'John' 
  AND Surname = 'Doe'
  AND EffectiveDate <= '2021-06-01'
ORDER BY EffectiveDate DESC
LIMIT 1;

可选优化建议 #

• 新增ExpiredDate字段，旧记录失效时写入失效日期，新记录默认写入9999-12-31，后续历史快照查询无需排序，执行效率更高
• 给Name+Surname+IsActive加联合索引，大幅提升更新、查询的执行效率
• 业务侧补充员工唯一业务ID，从根源避免重名导致的数据混淆问题

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