如何记录数据库中1 to many关系的结构变更历史（支持时间戳）

针对你需要保留Valve与Channel关联关系及Channel属性变更历史的需求，以下是两种实用的设计方案，均支持时间戳级别的历史跟踪：

一、通用手动维护方案（兼容所有数据库） #

该方案通过拆分当前状态表与历史表，手动维护时间区间，适用于不支持时态表的数据库，灵活性强。

1. 核心表结构 #

ValveTable（阀门主表）

存储阀门的固有基础信息（若阀门属性也需跟踪历史，可参照Channel的历史设计）：

CREATE TABLE ValveTable (
    ValveID INT PRIMARY KEY,
    ValveName VARCHAR(50) NOT NULL
    -- 其他阀门静态属性
);

ChannelTable（通道当前状态表）

仅存储通道的最新状态，方便快速查询当前关联与属性：

CREATE TABLE ChannelTable (
    ChannelID INT PRIMARY KEY,
    CurrentValveID INT NULL, -- 当前关联的阀门ID，NULL表示无关联（如C1被移除后）
    LastUpdated TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    FOREIGN KEY (CurrentValveID) REFERENCES ValveTable(ValveID)
);

ValveChannelAssociationHistory（阀-关关联历史表）

这是关键的连接表，记录每一段关联关系的时间区间：

CREATE TABLE ValveChannelAssociationHistory (
    AssociationID INT PRIMARY KEY AUTO_INCREMENT,
    ValveID INT NOT NULL,
    ChannelID INT NOT NULL,
    StartTimestamp TIMESTAMP(6) NOT NULL, -- 关联生效的精确时间戳
    EndTimestamp TIMESTAMP(6) NULL, -- 关联结束时间戳，NULL表示当前仍生效
    ChangeReason VARCHAR(100), -- 可选：记录变更原因
    Operator VARCHAR(50), -- 可选：记录操作人
    FOREIGN KEY (ValveID) REFERENCES ValveTable(ValveID),
    FOREIGN KEY (ChannelID) REFERENCES ChannelTable(ChannelID),
    -- 约束：同一通道同一时间只能关联一个阀门
    UNIQUE KEY (ChannelID, StartTimestamp),
    CHECK (EndTimestamp IS NULL OR EndTimestamp > StartTimestamp)
);

操作逻辑：

• 新增关联（如Day3新增C4关联V0）：插入一条记录，StartTimestamp设为变更时间，EndTimestamp留空
• 转移关联（如Day1 C2从V0转到V1）：先给V0与C2的关联记录设置EndTimestamp为变更时间，再插入V1与C2的新关联记录
• 移除关联（如Day2 C1从V0移除）：给对应关联记录设置EndTimestamp为移除时间

ChannelAttributeHistory（通道属性变更历史表）

跟踪通道属性的每一次变更，保留全量历史版本：

CREATE TABLE ChannelAttributeHistory (
    ChannelVersionID INT PRIMARY KEY AUTO_INCREMENT,
    ChannelID INT NOT NULL,
    -- 列出所有需要跟踪历史的通道属性，示例如下：
    ChannelName VARCHAR(50) NOT NULL,
    PressureThreshold DECIMAL(10,2) NOT NULL,
    StartTimestamp TIMESTAMP(6) NOT NULL,
    EndTimestamp TIMESTAMP(6) NULL,
    ChangeReason VARCHAR(100),
    Operator VARCHAR(50),
    FOREIGN KEY (ChannelID) REFERENCES ChannelTable(ChannelID),
    CHECK (EndTimestamp IS NULL OR EndTimestamp > StartTimestamp)
);

操作逻辑：

• 属性变更（如Day1 C0属性变更）：先给当前属性版本记录设置EndTimestamp为变更时间，再插入新的属性版本记录
• 新增通道（如Day3新增C4）：在ChannelTable插入记录，同时在ChannelAttributeHistory插入第一条版本记录（EndTimestamp留空）

2. 历史查询示例 #

查询指定时间点的阀-关关联关系

SELECT v.ValveID, v.ValveName, c.ChannelID
FROM ValveTable v
JOIN ValveChannelAssociationHistory va ON v.ValveID = va.ValveID
JOIN ChannelTable c ON va.ChannelID = c.ChannelID
WHERE va.StartTimestamp <= '2024-05-20 10:00:00.123456'
  AND (va.EndTimestamp IS NULL OR va.EndTimestamp > '2024-05-20 10:00:00.123456');

查询某通道的全量属性历史

SELECT ChannelName, PressureThreshold, StartTimestamp, EndTimestamp
FROM ChannelAttributeHistory
WHERE ChannelID = 'C0'
ORDER BY StartTimestamp DESC;

二、数据库时态表方案（依赖数据库特性） #

如果你的数据库支持系统版本时态表（如PostgreSQL 12+、SQL Server 2016+、MySQL 8.0.16+），可直接利用数据库自动维护历史，减少手动开发成本。

示例（SQL Server） #

时态化ChannelTable

CREATE TABLE ChannelTable (
    ChannelID INT PRIMARY KEY,
    ValveID INT NULL,
    ChannelName VARCHAR(50) NOT NULL,
    PressureThreshold DECIMAL(10,2) NOT NULL,
    -- 系统自动维护的时间列
    SysStartTime DATETIME2(6) GENERATED ALWAYS AS ROW START NOT NULL,
    SysEndTime DATETIME2(6) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime),
    FOREIGN KEY (ValveID) REFERENCES ValveTable(ValveID)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ChannelTableHistory));

时态化阀-关关联表

CREATE TABLE ValveChannelAssociation (
    ValveID INT NOT NULL,
    ChannelID INT NOT NULL,
    SysStartTime DATETIME2(6) GENERATED ALWAYS AS ROW START NOT NULL,
    SysEndTime DATETIME2(6) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime),
    PRIMARY KEY (ValveID, ChannelID),
    FOREIGN KEY (ValveID) REFERENCES ValveTable(ValveID),
    FOREIGN KEY (ChannelID) REFERENCES ChannelTable(ChannelID)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ValveChannelAssociationHistory));

优势：数据库自动维护历史记录，无需手动编写更新历史的逻辑；查询历史数据时使用FOR SYSTEM_TIME AS OF '时间戳'语法即可。

方案选择建议 #

• 若需兼容多数据库或数据库不支持时态表，选通用手动维护方案，灵活性最高，适合复杂变更场景
• 若数据库支持时态表，选时态表方案，开发效率高，降低人为错误风险
• 无论哪种方案，都建议使用高精度时间戳（如TIMESTAMP(6)），避免同一时间点的变更冲突

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