产品数据的审计日志记录？

Question

当工作人员更改产品名称、选项名称或价格信息时。它应该将数据插入历史日志以及是谁做的。

items 表：

item_id (PK)
item_name
item_description

注意：item 价格位于 item_options 表中

item_options 表：

option_id (PK)
item_id (FK)
option_name
option_price

一个项目可以有 1 个或多个选项。

如果我想更改名称items.item_name，应该将当前记录复制到历史表，从items表中删除当前记录，然后插入一条新记录items 表中有新信息？

item_options 怎么样，它是如何工作的？如果特定 item_id 有多个选项，这是否意味着我需要将选项复制到历史表？

items 和 item_options 的审核日志记录/历史表应该是什么样子？

谢谢

Answer 1

您的审计数据应该存储在每个表中，而不是全部存储在一个地方。您要做的就是为每个要跟踪的表创建一个审核表，并创建触发器以在审核表中为审核表上的任何数据操作操作创建记录。

绝对建议禁止对 items 和 item_options 表进行 DELETE 操作 - 添加 item_active 和 等标志item_option_active 以便您可以软删除它们。如果您正在执行诸如存储参考过去订购的产品的发票之类的操作，并且需要数据用于历史报告目的而不是用于日常使用，那么这是正常做法。

您的审计表不应该用于引用旧数据，您的正常数据模型应该支持简单地“隐藏”可能仍将使用的旧数据，并存储随时间变化的多个版本的数据。

对于审核来说，存储最后一个修改给定记录的用户的用户名也很有用 - 当从 Web 应用程序使用时，您不能使用 MySQL 的 USER() 函数来获取任何有用的信息谁登录了。添加列并填充它意味着您可以在审核触发器中使用该信息。

注意： 我假设您不允许在正常情况下更改项目 ID - 这会使您的审核系统更加复杂。

如果您添加活动标志，和最后修改者数据到您的表中，它们看起来像这样：

项目表：

mysql> desc items;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| item_id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| item_name        | varchar(100) | YES  |     | NULL    |                |
| item_description | text         | YES  |     | NULL    |                |
| item_active      | tinyint(4)   | YES  |     | NULL    |                |
| modified_by      | varchar(50)  | YES  |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+

项目选项表：

mysql> desc item_options;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| option_id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| item_id       | int(11)      | YES  | MUL | NULL    |                |
| option_name   | varchar(100) | YES  |     | NULL    |                |
| option_price  | int(11)      | YES  |     | NULL    |                |
| option_active | tinyint(4)   | YES  |     | NULL    |                |
| modified_by   | varchar(50)  | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

您的审核表需要存储四条额外的信息：

• 审核 ID - 此 ID 仅对于 < 的历史记录是唯一的em>这个表，它不是全局的值
• 更改由 - 进行更改的数据库用户
• 更改日期/时间
• 操作类型 - INSERT 或 UPDATE（或 DELETE，如果您允许）它）

您的审核表应该类似于：

项目审核表：

mysql> desc items_audit;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| audit_id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| item_id          | int(11)      | YES  |     | NULL    |                |
| item_name        | varchar(100) | YES  |     | NULL    |                |
| item_description | text         | YES  |     | NULL    |                |
| item_active      | tinyint(4)   | YES  |     | NULL    |                |
| modified_by      | varchar(50)  | YES  |     | NULL    |                |
| change_by        | varchar(50)  | YES  |     | NULL    |                |
| change_date      | datetime     | YES  |     | NULL    |                |
| action           | varchar(10)  | YES  |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+

项目选项审核表：

mysql> desc item_options_audit;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| audit_id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| option_id     | int(11)      | YES  |     | NULL    |                |
| item_id       | int(11)      | YES  |     | NULL    |                |
| option_name   | varchar(100) | YES  |     | NULL    |                |
| option_price  | int(11)      | YES  |     | NULL    |                |
| option_active | tinyint(4)   | YES  |     | NULL    |                |
| modified_by   | varchar(50)  | YES  |     | NULL    |                |
| change_by     | varchar(50)  | YES  |     | NULL    |                |
| change_date   | datetime     | YES  |     | NULL    |                |
| action        | varchar(10)  | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

不要在审核表上使用外键；审计表中的行不是它们正在审计的记录的子行，因此外键没有任何用处。

触发器

注意： MySQL 不支持多语句类型触发器，因此您需要为 INSERT、UPDATE 和DELETE（如果适用）。

您的触发器只需将所有NEW值INSERT到审核表中即可。 items 表的触发器定义可能是：

/* Trigger for INSERT statements on the items table */
CREATE DEFINER=`root`@`localhost` TRIGGER trigger_items_insert_audit 
AFTER INSERT ON items 
  FOR EACH ROW BEGIN
    INSERT INTO items_audit (
                  item_id, item_name, item_description, 
                  item_active, modified_by, change_by,  
                  change_date, action
                ) VALUES (
                  NEW.item_id, NEW.item_name, NEW.item_description,  
                  NEW.item_active, NEW.modified_by, USER(),  
                  NOW(), 'INSERT'
                ); 
  END;

/* Trigger for UPDATE statements on the items table */
CREATE DEFINER=`root`@`localhost` TRIGGER trigger_items_update_audit 
AFTER UPDATE ON items 
  FOR EACH ROW BEGIN
    INSERT INTO items_audit (
                  item_id, item_name, item_description, 
                  item_active, modified_by, change_by,  
                  change_date, action
                ) VALUES (
                  NEW.item_id, NEW.item_name, NEW.item_description,  
                  NEW.item_active, NEW.modified_by, USER(),  
                  NOW(), 'UPDATE'
                ); 
  END;

为 item_options 表创建类似的触发器。

更新：电子商务中的数据历史记录

我们上面所做的审核将允许您保留任何给定数据库表的历史记录，但会创建一个不适合用于需要定期访问的数据的数据存储。

在电子商务系统中，保留可用的历史数据非常重要，这样您就可以更改属性，同时在某些情况下仍显示旧值。

这应该与您的审核解决方案完全分开

存储历史记录的最佳方法是为需要历史存储的每个属性创建一个历史表。 此 Stackoverflow问题有一些关于保留给定属性的历史记录的有用信息。

在您的情况下，如果您只关心价格和标题，则可以创建一个 prices 表和一个 item_titles 表。每个表都有一个指向 item_options 表或 items 表的外键（主表仍会存储当前价格或标题），并有价格或标题及其生效日期。这些表应该具有细粒度（可能基于列）的权限，以避免在插入记录后更新有效_起始日期和实际值。

您还应该在这些表上使用上面的审核解决方案。

Answer 2

如果你没有一堆约束 - 那么当你通过删除选项条目来孤立项目条目时，你的数据会很快变得混乱，反之亦然。

您所要求的可以在触发器中完成，但这可能不是您想要的。

如果您有一个有 2 个选项的商品，请进行成像。

现在您更改项目名称，该项目将被删除（并移至历史记录）-您有不可链接的选项...这是您想要的吗？

订单或引用这些项目的其他内容怎么样？同样的问题。

相反，创建触发器逻辑以仅允许对项目进行“合理”编辑。如果需要，请将记录的副本放入并行历史表中，但不要删除原始记录。

您还可以考虑向该项目或某些日期范围添加状态列，以便考虑该项目当前可用或您可能需要的任何其他状态。