实现缓慢变化维中的审计表

1. 数据库表结构设计

        我们用一个用户表 Users 为例。

1.1 主表结构（缓慢变化维表）

CREATE TABLE Users (
    UserID INT NOT NULL,              -- 主键
    UserName VARCHAR(255) NOT NULL,  -- 用户名
    Email VARCHAR(255),              -- 邮箱
    Address VARCHAR(255),            -- 地址
    Version INT NOT NULL,            -- 数据版本号
    IsCurrent BOOLEAN NOT NULL,      -- 是否当前版本
    EffectiveStartDate DATETIME NOT NULL, -- 数据生效时间
    EffectiveEndDate DATETIME NULL,  -- 数据失效时间 (NULL 表示当前版本)
    PRIMARY KEY (UserID, Version)
);

解释：

• UserID 是主键，标识用户。
• Version 用于区分每个用户的不同版本记录。
• IsCurrent 标记当前最新版本。
• EffectiveStartDate 和 EffectiveEndDate 表示该记录的生效和失效时间。

1.2 审计表结构

CREATE TABLE AuditTrail (
    AuditID INT AUTO_INCREMENT PRIMARY KEY, -- 审计记录唯一标识
    UserID INT NOT NULL,                    -- 关联主表的 UserID
    OperationType VARCHAR(50) NOT NULL,    -- 操作类型 (INSERT, UPDATE, DELETE)
    OperationTime DATETIME NOT NULL,       -- 操作时间
    OldData JSON NULL,                     -- 操作前的数据
    NewData JSON NULL                      -- 操作后的数据
);

解释：

• AuditID 是审计记录的唯一标识。
• OperationType 指示操作类型。
• OldData 和 NewData 记录变更前后的数据，使用 JSON 格式保存以便存储灵活的结构化信息。

---

2. 核心操作流程

2.1 插入新记录

当新用户插入时：

1. 在 Users 表插入新记录，Version=1。
2. 在 AuditTrail 表中记录插入操作。

SQL 代码：

-- 向主表插入数据
INSERT INTO Users (UserID, UserName, Email, Address, Version, IsCurrent, EffectiveStartDate, EffectiveEndDate)
VALUES (1, 'Alice', 'alice@example.com', '123 Main St', 1, TRUE, NOW(), NULL);

-- 向审计表插入记录
INSERT INTO AuditTrail (UserID, OperationType, OperationTime, OldData, NewData)
VALUES (1, 'INSERT', NOW(), NULL, JSON_OBJECT('UserName', 'Alice', 'Email', 'alice@example.com', 'Address', '123 Main St'));

2.2 更新记录

当用户数据发生变化时：

1. 将主表中当前版本的记录 IsCurrent 置为 FALSE，设置失效时间。
2. 插入新的版本记录到主表。
3. 在 AuditTrail 表中记录更新操作，存储变更前后的数据。

SQL 代码：

-- 更新主表：失效当前记录
UPDATE Users
SET IsCurrent = FALSE, EffectiveEndDate = NOW()
WHERE UserID = 1 AND IsCurrent = TRUE;

-- 插入新版本记录
INSERT INTO Users (UserID, UserName, Email, Address, Version, IsCurrent, EffectiveStartDate, EffectiveEndDate)
VALUES (1, 'Alice', 'alice.new@example.com', '456 Elm St', 2, TRUE, NOW(), NULL);

-- 向审计表插入更新记录
INSERT INTO AuditTrail (UserID, OperationType, OperationTime, OldData, NewData)
VALUES (1, 'UPDATE', NOW(),
        JSON_OBJECT('UserName', 'Alice', 'Email', 'alice@example.com', 'Address', '123 Main St'),
        JSON_OBJECT('UserName', 'Alice', 'Email', 'alice.new@example.com', 'Address', '456 Elm St'));

2.3 删除记录

当用户数据被删除时：

1. 将主表中当前版本的记录 IsCurrent 置为 FALSE，设置失效时间。
2. 在 AuditTrail 表中记录删除操作。

SQL 代码：

-- 更新主表：失效当前记录
UPDATE Users
SET IsCurrent = FALSE, EffectiveEndDate = NOW()
WHERE UserID = 1 AND IsCurrent = TRUE;

-- 向审计表插入删除记录
INSERT INTO AuditTrail (UserID, OperationType, OperationTime, OldData, NewData)
VALUES (1, 'DELETE', NOW(),
        JSON_OBJECT('UserName', 'Alice', 'Email', 'alice.new@example.com', 'Address', '456 Elm St'),
        NULL);

---

3. 查询与验证

3.1 获取用户当前数据

SELECT * FROM Users
WHERE UserID = 1 AND IsCurrent = TRUE;

3.2 获取用户历史记录

SELECT * FROM Users
WHERE UserID = 1
ORDER BY Version;

3.3 获取用户变更日志

SELECT * FROM AuditTrail
WHERE UserID = 1
ORDER BY OperationTime;

---

4. 为什么这样设计？

• 数据完整性：主表记录所有版本，避免覆盖历史数据。
• 灵活性：通过 AuditTrail 提供细粒度的变更信息，便于后续分析。
• 扩展性：JSON 存储灵活支持复杂的数据结构变化。

---

小结

        通过主表 Users 和审计表 AuditTrail 的配合，完整实现了缓慢变化维 的历史管理和审计功能。设计兼顾了性能、历史追踪和数据审计的需求，同时代码简洁易维护。