Отслеживание изменений в базе данных

Моя база данных требует отслеживания некоторых столбцов, но не всех. Я рассмотрел несколько вариантов реализации отслеживания, например (Идеи по проектированию базы данных для записи контрольных журналов).

Однако это кажется очень расточительным, и, поскольку мне действительно нужно отслеживать только пару критических столбцов, я чувствую, что это не было бы оптимальным решением для меня.

Теперь я подумал о способе решения моей ситуации, но я не уверен, могу ли я упустить из виду недостаток дизайна при таком подходе.

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

Приведенная выше схема представляет собой очень упрощенный пример, но отражает суть.

По сути, Сотрудник может быть на пенсионной схеме или нет, изменения этого атрибута необходимо отслеживать. Когда должно произойти изменение этого атрибута, вставляется новая строка с отметкой времени.

Если вы хотите выяснить, участвует ли сотрудник в пенсионной программе или нет, вам нужно будет найти строку с самой последней меткой времени.

Единственный недостаток, который я сейчас вижу, заключается в том, что если вставлен сотрудник, в таблице PensionScheme нет соответствующей строки. Хотя я думаю решить эту проблему с помощью триггера INSERT, чтобы добавить строку по умолчанию.

Я действительно просто ищу мысли по поводу этого дизайна. У меня нет опыта в отслеживании изменений в базах данных.


mysql sql database mariadb change-tracking
person bbartels    schedule 28.01.2018    source источник
comment
использовать триггер   -  person I wrestled a bear once.    schedule 28.01.2018

Ответы (1)


arrow_upward
2
arrow_downward

Возможно, вас заинтересуют функции управления версиями системы, доступные в MariaDB, начиная с версия 10.3.4-beta.

Основная идея заключается в следующем (хотя вам, конечно, нужно будет настроить структуру в соответствии с вашими реальными потребностями):

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)
person elenst    schedule 28.01.2018
comment
Это именно то, что я искал. Я предполагаю, что у меня возникнут проблемы с ORM. - person bbartels; 28.01.2018