跟踪数据库中的更改

Question

我的数据库需要跟踪一些列，但不是所有列。我已经研究了几种实现跟踪的设计，比如(Ideas on database design for capturing audit trails)。

然而，这看起来非常浪费，因为我只需要跟踪几个关键列，我觉得这对我来说不是最好的解决方案。

现在我想出了一种方法来解决我的情况，但我不确定我是否忽略了这种方法的设计缺陷。

User
----
ID PK        INT
Username     VARCHAR(MAX)    

Employee
-----
ID PK        INT
Name         VARCHAR(MAX)

PensionScheme
-------------
ID PK         INT
EmpID FK      INT (References Employee)
IsActive      BOOLEAN
ModifiedBy FK INT (References User)
EffectiveFrom DATETIME

上面的模式只是一个高度简化的示例，但抓住了本质。

从本质上讲，员工可以在养老金计划中，也可以不在养老金计划中，必须跟踪此属性的更改。当需要对该属性进行更改时，会插入一个带有时间戳的新行。

如果您想要确定该员工是否在使用养老金方案，则必须找到具有最近时间戳的行。

我目前看到的唯一缺陷是，如果插入了一个Employee，那么PensionScheme表中就没有匹配的行。虽然我正在考虑用插入触发器来添加一个缺省行来解决这个问题。

我真的只是在寻找关于这个设计的想法。我对数据库中的更改跟踪非常缺乏经验。

Answer 1

您可能会对MariaDB中从10.3.4-beta版本开始提供的system versioning functionality感兴趣。

基本的想法是这样的(尽管你当然需要根据你的实际需求来调整结构)：

MariaDB [test]> CREATE TABLE PensionScheme (
                  ID INT PRIMARY KEY, 
                  EmpID INT, 
                  IsActive BOOLEAN WITH SYSTEM VERSIONING, 
                  ModifiedBy INT, 
                  EffectiveFrom DATETIME
                );
Query OK, 0 rows affected (0.17 sec)

MariaDB [test]> INSERT INTO PensionScheme VALUES (1,2,0,1,NULL);
Query OK, 1 row affected (0.05 sec)

MariaDB [test]> SELECT * FROM PensionScheme WHERE EmpID = 2;
+----+-------+----------+------------+---------------+
| ID | EmpID | IsActive | ModifiedBy | EffectiveFrom |
+----+-------+----------+------------+---------------+
|  1 |     2 |        0 |          1 | NULL          |
+----+-------+----------+------------+---------------+
1 row in set (0.00 sec)

MariaDB [test]> UPDATE PensionScheme 
                  SET IsActive = 1, ModifiedBy = 2 WHERE EmpID = 2;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Inserted: 1  Warnings: 0

MariaDB [test]> SELECT * FROM PensionScheme WHERE EmpID = 2;
+----+-------+----------+------------+---------------+
| ID | EmpID | IsActive | ModifiedBy | EffectiveFrom |
+----+-------+----------+------------+---------------+
|  1 |     2 |        1 |          2 | NULL          |
+----+-------+----------+------------+---------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT ID, IsActive, ModifiedBy, row_start, row_end 
                  FROM PensionScheme FOR system_time ALL WHERE EmpID = 2;
+----+----------+------------+----------------------------+----------------------------+
| ID | IsActive | ModifiedBy | row_start                  | row_end                    |
+----+----------+------------+----------------------------+----------------------------+
|  1 |        0 |          1 | 2018-01-28 14:59:54.955159 | 2018-01-28 15:00:56.430942 |
|  1 |        1 |          2 | 2018-01-28 15:00:56.430942 | 2038-01-19 05:14:07.999999 |
+----+----------+------------+----------------------------+----------------------------+
2 rows in set, 3 warnings (0.00 sec)

MariaDB [test]> UPDATE PensionScheme SET EffectiveFrom = NOW() WHERE EmpID = 2;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Inserted: 0  Warnings: 0

MariaDB [test]> SELECT ID, IsActive, ModifiedBy, row_start, row_end 
                  FROM PensionScheme FOR system_time ALL WHERE EmpID = 2;
+----+----------+------------+----------------------------+----------------------------+
| ID | IsActive | ModifiedBy | row_start                  | row_end                    |
+----+----------+------------+----------------------------+----------------------------+
|  1 |        0 |          1 | 2018-01-28 14:59:54.955159 | 2018-01-28 15:00:56.430942 |
|  1 |        1 |          2 | 2018-01-28 15:00:56.430942 | 2038-01-19 05:14:07.999999 |
+----+----------+------------+----------------------------+----------------------------+
2 rows in set, 3 warnings (0.00 sec)

MariaDB [test]> SELECT * FROM PensionScheme WHERE EmpID = 2;
+----+-------+----------+------------+---------------------+
| ID | EmpID | IsActive | ModifiedBy | EffectiveFrom       |
+----+-------+----------+------------+---------------------+
|  1 |     2 |        1 |          2 | 2018-01-28 15:03:19 |
+----+-------+----------+------------+---------------------+
1 row in set (0.00 sec)