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

Question

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

项目表：

item_id (PK)
item_name
item_description

注意:项目价格在item_options表中

item_options表：

option_id (PK)
item_id (FK)
option_name
option_price

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

如果我想更改名称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()函数来获取任何有关登录用户的有用信息。添加列并填充它意味着您可以在审计触发器中使用该信息。

NB:我假设您不会允许在正常情况下更改项目IDs这会使您的审计系统更加复杂。

如果将活动标志和最后修改者数据添加到表中，它们将如下所示：

项目表：

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仅在此表的历史记录中是唯一的，它不是创建change
• Change date/time
• Action类型的数据库用户所做的
• 更改的全局值- 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    |                |
+---------------+--------------+------+-----+---------+----------------+

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

触发器

NB: 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表创建类似的触发器。

更新:电子商务中的数据历史

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

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

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

存储历史的最好方法是为每个需要以历史方式存储的属性创建一个历史表。This Stackoverflow question has some good information about keeping a history of a given attribute。

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

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

Answer 2

如果你没有一堆约束-那么当你通过删除选项条目和visaversa来孤立项目条目时，你的数据会在匆忙中变得混乱。

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

如果你有一个有两个选项的物品，那就成像吧。

现在您更改了项目名称，该项目将被开发(并移动到历史记录中)-您有不可链接的选项...这就是你想要的吗？

那么order或其他引用这些项目的东西呢？同样的问题。

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

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