审计日志记录产品数据？ [英] Audit logging for products data?
问题描述
当工作人员更改产品名称，选项名称或价格的信息时。
When the staff change the information of product name, option name or prices. It should insert the data into history log and who done it.
项目表：
item_id (PK)
item_name
item_description
项目价格位于item_options表中
Note: item prices are in the item_options table
item_options表格：
item_options table:
option_id (PK)
item_id (FK)
option_name
option_price
项目可以有1个或多个选项。
A item can have 1 or more options.
如果我要更改名称 items.item_name ，它应该将当前记录复制到历史记录表，从 items 表中删除当前记录，然后在 items 表中插入新记录，并添加新信息？
If I want to change the name items.item_name, It should copy the current record to the history table, delete current record from items table and then insert a new record with the new information in the items table?
item_options 会怎么样？如果有特定item_id的多个选项，这是否意味着我需要重复选项到历史表？
What about the item_options, how would that work? If there are multiple options from specific item_id, do that mean I need to duplicate options to history table?
<$ c $
感谢
推荐答案
您的审计数据应存储在每个表中，而不是全部存储在一个地方。您要做的是为要跟踪的每个表创建一个审计表，并创建触发器以在审计表中为审计表上的任何数据操作操作创建记录。
Your audit data should be stored per-table, rather than all in one place. What you'd do is create an audit table for each of the tables you want to track, and create triggers to create a record in the audit table for any data-manipulation operation on the audited table.
在项目和上禁止表 - 添加 DELETE item_options item_active 和 item_option_active 的标志，以便您可以软删除它们。这是正常的做法，如果你正在做的事情，例如存储引用过去订购的产品的发票，并且需要数据用于历史报告目的，但不是日常使用。
It's definitely advisable to disallow DELETE operations on the items and item_options tables - add flags like item_active and item_option_active so that you can softdelete them instead. This is normal practice in situations where you're doing things like storing invoices that reference products ordered in the past, and need the data for historical reporting purposes, but not for day-to-day use.
您的审计表不是用于引用旧数据的，您的正常数据模型应该支持简单地隐藏旧数据，其中可能仍然是
Your audit tables aren't something you should use for referencing old data, your normal data model should support simply "hiding" old data where it's likely that it's still going to be used, and storing multiple versions of data that will change over time.
对于审计，存储最后一个用户修改给定记录的用户名也是有用的 - 当从Web应用程序使用时，您不能使用MySQL的 USER（）函数来获取有关谁登录的任何有用的信息。添加列并填充它意味着您可以在审核触发器中使用该信息。
For auditing, it's also useful to store the username of the last user to modify a given record - when used from a web application, you can't use MySQL's USER() function to get any useful information about who's logged on. Adding a column and populating it means you can use that information in your audit triggers.
注意：将不允许在正常情况下更改项目ID - 这将使您的审计系统更复杂。
NB: I'll assume that you won't allow item IDs to be changed under normal conditions - that would make your auditing system more complex.
如果添加活动标记，修改过的数据到您的表中，它们将类似于：
If you add active flags, and last-modified-by data to your tables, they'll look something like:
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 | |
+------------------+--------------+------+-----+---------+----------------+
项目选项表：
Item options table:
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 | |
+---------------+--------------+------+-----+---------+----------------+
您的审计表需要存储四个额外的信息：
Your audit tables need to store four extra pieces of information:
- 审核ID - 此ID仅对历史记录是唯一的 更改日期/时间
- 操作类型 -
INSERT或UPDATEc $ c> DELETE ，如果您允许）
- Audit ID - this ID is only unique for the history of this table, it's not a global value
- Change made by - the database user who made the change
- Change date/time
- Action type -
INSERTorUPDATE(orDELETEif you were allowing it)
您的审计表应如下所示： / p>
项目审核表：
Your audit tables should look something like:
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 | |
+------------------+--------------+------+-----+---------+----------------+
项目选项审计表：
Item options audit table:
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 | |
+---------------+--------------+------+-----+---------+----------------+
不要在审计表上使用外键;审核表中的行不是他们正在审核的记录的子行，因此外键不起任何作用。
Don't use foreign keys on your audit tables; the rows in the audit tables aren't child rows of the records they're auditing, so foreign keys aren't of any use.
注意： MySQL不支持多语句类型触发器，因此您需要为 INSERT ， UPDATE 和 DELETE （如果适用）。
NB: MySQL doesn't support multi-statement-type triggers, so you need one for each of INSERT, UPDATE and DELETE (if applicable).
您的触发器只需将 INSERT 所有 NEW 值插入审计表。 项表的触发器定义可能是：
Your triggers simply need to INSERT all the NEW values into the audit table. The trigger definitions for the items table might be:
/* 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 表创建类似的触发器。
Create similar triggers for the item_options table.
我们在上面进行的审计将允许您保留历史记录
The auditing we did above will allow you to keep a history of any given database table, but creates a data store that isn't suitable for use for data that needs to be accessed regularly.
在一个电子商务系统中，保持一个数据库表，
In an e-commerce system, keeping usable historical data is important, so that you can change attributes while still presenting old values in certain situations.
这应该是完全的与您的审核解决方案分开
存储历史记录的最佳方法是为每个属性创建一个历史记录表存储历史。 此Stackoverflow问题有一些关于保持给定属性的历史的好信息。
The best way to store history is to create a history table for each attribute that needs to be stored historically. This Stackoverflow question has some good information about keeping a history of a given attribute.
在你的情况下，如果你只关心价格和标题， d创建一个价格表和一个 item_titles 表。每一个都有一个外键对 item_options 表或 items 表（主表仍然存储当前价格或标题），并且具有价格或标题及其生效日期。这些表应该具有细粒度（可能基于列的）权限，以避免更新 effective_from 日期以及插入记录后的实际值。
In your situation, if you're only concerned about price and title, you'd create a prices table, and an item_titles table. Each one would have a foreign key to either the item_options table or the items table (the master tables would still store the current price, or title), and would have the price or title, with its effective dates. These tables should have fine-grained (possibly column-based) permissions to avoid updating the effective_from dates, and the actual values once the record is inserted.
您也应该使用上面的审计解决方案。
You should use the auditing solution above on these tables also.
这篇关于审计日志记录产品数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
