基于SCD2/4实现物料清单管理器组件版本控制的设计疑问

两种方案都符合SCD Type 2的规范，具体选哪种取决于你的业务场景：

• 方案1：直接在components表新增字段
  适合默认查询需要携带版本信息的场景。需要注意原表的id不能再作为唯一主键，因为同一个逻辑组件会存在多条历史版本记录，建议新增revision_id作为主键，原id改为component_code用来关联同一组件的所有版本。
  改造后的表结构示例：

  CREATE TABLE components (
    revision_id  INTEGER PRIMARY KEY AUTOINCREMENT,
    component_id INTEGER NOT NULL, -- 原components表的id，用于标识同一逻辑组件
    name         TEXT,
    project_id   INTEGER,
    start_date   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    end_date     TIMESTAMP,
    is_active    BOOLEAN NOT NULL DEFAULT 1,
    CONSTRAINT fk_project FOREIGN KEY (project_id) REFERENCES projects (id)
  )
  

  组件更新时，先将旧版本的end_date设为当前时间、is_active设为0，再插入新的版本记录即可。
• 方案2：额外新增独立的component_revisions表
  适合默认查询最新版本组件的场景，兼容性更强，不需要改动原有业务的查询逻辑。原components表只存最新版本数据，所有历史版本都存在component_revisions表，结构和原表一致，额外增加SCD2需要的时间、状态字段即可。
  新增表结构示例：

  -- 原components表保持不变，仅存储最新版本数据
  CREATE TABLE component_revisions (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    component_id INTEGER NOT NULL,
    name         TEXT,
    project_id   INTEGER,
    start_date   TIMESTAMP NOT NULL,
    end_date     TIMESTAMP NOT NULL,
    CONSTRAINT fk_component FOREIGN KEY (component_id) REFERENCES components (id)
  )
  

  组件更新时，先把components表当前的最新数据插入component_revisions表，设置对应的时间区间，再更新components表的内容即可。

SCD Type 4的核心设计就是主表+历史快照表的双表结构，你提到的需要维护两张表的说法是正确的，但不需要revisions+revisions_history的嵌套结构，只需要保留两层即可：

• 主表（即你现有的components表）只存最新的当前版本数据，结构无需改动
• 历史快照表存储所有历史变更的完整快照，不需要维护时间区间，仅需增加变更时间字段即可，查询时直接按时间排序取对应快照就行。
  历史表结构示例：

  -- 原components表保持不变，仅存储最新版本数据
  CREATE TABLE component_history (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    component_id INTEGER NOT NULL,
    name         TEXT,
    project_id   INTEGER,
    change_time  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
  )
  

  组件更新时，直接把变更前的主表数据整条写入历史表，再更新主表内容即可，实现逻辑非常简单，适合组件变更频率极高的场景。

选型建议 #

如果组件变更频率较低，且经常需要查询某段时间范围内的版本有效性，选SCD Type 2的双表方案即可；如果组件变更非常频繁，仅需要按时间点查询历史快照，选SCD Type 4的实现成本更低、查询效率更高。

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