存储所有细节的所有数据更改(例如Stackoverflow) [英] Store all data changes with every details (like Stackoverflow)

问题描述

我有使用Codeigniter编写的系统，并使用MySQL作为数据库.系统具有用户，具有不同特权的用户组等.具有许多具有许多对许多关系的mysql表.

I have system written using Codeigniter and as a database using MySQL. System have user, usergroups with different privileges and etc. Have lots of mysql tables which have many to many relationships.

我有一些桌子:

• 项目
• 合同
• 客户
• 产品
• product_features
• 订单
• order_features
• order_products
• 等...

当前，我正在记录用户对这些表进行的数据更改.用户可以根据自己的权限更改这些数据.存储日志更改仅是简单的形式，例如

Currently I am logging every change on data for these tables which made by users. Users can change these datas due to their privilege. Storing change of logs only simple form like

A user changed product features with id of A8767
B user added new customer with id 56
C user edited content of orderlist
A user added new product (id: A8767) to order (id: or67)
...

我想保留对每个细节所做的所有更改，例如问题Stackoverflow的编辑历史记录.我可以考虑log_table设计，以保留来自各个表的所有数据更改.有没有办法，教程，引擎，插件来做到这一点?只有我能想到使每个表都重复并继续在它们上存储更改，但是我不认为它是一种好方法.

I want keep all changes which made with every detail, like edit history of question Stackoverflow. I can think about log_table design to keep all data changes from various tables. Is there any way, tutorial, engine , plugin to do that ? Only i can think make duplicate of every table and keep storing changes on them, but i dont think its good way.

推荐答案

我已经考虑了一段时间了，只能想到两种方法来做到这一点.当制作成抽象数据层/模型时，两者都可以完全透明地工作.

I've been thinking about that for a while now and can only think of two ways to do this. Both can work fully transparent when crafted into an abstract data layer / model.

通过这种方式，在ORM映射器原则中实现了可版本化"表数据的实现.请参阅其文档中的示例.也许可以满足您的需求，但是不适合我的需求.似乎在删除原始记录时会删除所有历史记录数据，从而使其实际上并不是安全的修订版本.

By the way there is an implementation for "versionable" table data in the ORM mapper doctrine. See this example in their docs. Maybe that fits your needs, but it doesn't fit mine. It seems to delete all history data when the original record is deleted, making it not really revision safe.

选项A:每个表都有一个副本以保存修订数据

让我们说您有一个简单的联系表:

Lets say you have a simple contact table:

CREATE TABLE contact (
    id INT NOT NULL auto_increment,
    name VARCHAR(255),
    firstname VARCHAR(255),
    lastname VARCHAR(255),
    PRIMARY KEY (id)
)

您将创建该表的副本并添加修订数据:

You would create a copy of that table and add revision data:

CREATE TABLE contact_revisions (
    id INT NOT NULL,
    name VARCHAR(255),
    firstname VARCHAR(255),
    lastname VARCHAR(255),
    revision_id INT auto_increment,
    type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    change_time DEFAULT current_timestamp,
    PRIMARY KEY(revision_id)
)

使用AFTER触发器跟踪INSERT和UPDATE.在原始的每个新数据修订中，在修订表中插入新数据的副本，并正确设置修改type.

Keep track of INSERT and UPDATE using AFTER triggers. On each new data revision in the original, insert a copy of the new data in the revision table and set the modification type properly.

要记录DELETE版本安全，还必须在历史记录表中插入新行！为此，您应该使用BEFORE DELETE触发器并存储最新值，然后再将其删除.否则，您还必须删除历史记录表中的每个NOT NULL约束.

To log a DELETE revisionally safe you must also insert a new row in the history table! For this you should use a BEFORE DELETE trigger and store the latest values before they are deleted. Otherwise you will have to remove every NOT NULL constraint in the history table as well.

有关此实现的一些重要说明

• 对于历史记录表，您必须从修订表中删除每个UNIQUE KEY(此处为PRIMARY KEY)，因为每个数据修订将多次具有相同的键.
• 通过更新(例如软件更新)ALTER原始表中的架构和数据时，必须确保将相同的数据或架构更正也应用于历史表及其数据.否则，在还原到记录集的较旧版本时，您会遇到麻烦.
• 在现实世界中，您可能想知道哪个用户修改了数据.为了获得修订安全性，永远不要从用户表中删除用户记录.您应该仅通过标记将帐户设置为禁用.
• 通常，一个用户操作涉及多个表.在实际的实现中，您还必须跟踪多个表中的哪些更改属于单个用户事务，并且还应跟踪其顺序.在实际用例中，您可能希望以相反的顺序一起还原单个事务的所有更改.这就需要一个附加的修订表，该表可以跟踪用户和交易，并与历史表中的所有这些单独的修订保持松散的关系.

• For the history table you must drop every UNIQUE KEY (here: the PRIMARY KEY) from the revision table because you will have the same key multiple times for each data revision.
• When you ALTER the schema and data in the original table via an update (e.g. software update) you must ensure the same data or schema corrections are applied to the history table and its data, too. Otherwise you will run into trouble when reverting to an older revision of a record set.
• In a real world implementation you would want to know which user modified the data. To have that revisionally safe a user record should be never deleted from the users table. You should just set the account disabled with a flag.
• Usually, a single user action involves more than one table. In a real world implementation, you would also have to keep track which changes in multiple tables belong to a single user transaction and also in which order. In a real use case you would want to revert all changes of a single transaction together, in a reverse order. That would require an additional revision table which keeps track on the users and transactions and holds a loose relationship to all those individual revisions in the history tables.

好处:

• 完全在数据库中，与应用程序代码无关. (嗯，在跟踪用户交易时并不重要.这需要在单个查询范围之外的某些逻辑)
• 所有数据均采用其原始格式，没有隐式类型转换.
• 修订版中的搜索效果良好
• 轻松回滚.只需使用要回滚的修订版中的数据在原始表上执行简单的INSERT .. ON DUPLICATE KEY UPDATE ..语句即可.

• completely in database, independent from application code. (well, not when tracking user transactions is important. that would require some logic outside the scope of the single query)
• all data is in their original format, no implicit type conversions.
• good performance on search in the revisions
• easy rollback. Just do a simple INSERT .. ON DUPLICATE KEY UPDATE .. statement on the original table, using the data from the revision you want to roll back.

优点:

• 难以手动实施.
• 涉及数据库迁移/应用程序更新时，很难(但并非不可能)实现自动化.

如上所述， doctrines versionable 做类似的事情.

As already stated above, doctrines versionable does something similiar.

选项B:有一个集中的更改日志表

前言:不好的做法，仅供参考.

这种方法在很大程度上依赖于应用程序逻辑，应将其隐藏在数据层/模型中.

This approach does heavily rely on application logic, which should be hidden in a data layer / model.

您有一个中央历史记录表，可以对其进行跟踪

You have a central history table that keeps track on

• 谁做了
• 何时
• 修改，插入或删除
• 什么数据
• 在哪个字段
• 哪个表

就像在另一种方法中一样，您可能还希望跟踪哪些数据更改属于单个用户操作/事务以及以什么顺序进行.

Like in the other approach, you may also want to track which individual data changes belong to a single user action / transaction and in which order.

好处:

• 在向表中添加字段或创建新表时，无需与原始表保持同步.它可以透明缩放.

优点:

• 使用简单值=密钥存储在数据库中的错误做法
• 由于隐式类型转换，搜索性能不佳
• 当中央历史记录表由于写锁而成为瓶颈时，它可能会降低应用程序/数据库的整体性能(这仅适用于具有表锁的特定引擎，即MyISAM)
• 实施回滚要困难得多
• 由于隐式类型转换而导致的数据转换错误/精度损失
当您直接在代码中的某个位置而不是使用模型/数据层访问数据库时，
• 不会跟踪更改，并且会忘记在这种情况下您必须手动写入修订日志.与其他程序员一起工作时，这可能是一个大问题.

• bad practice using a simple value = key store in database
• bad search performance, because of implicit type conversions
• may slowdown overall performance of the application/database, when the central history table becomes a bottleneck because of write locks (this only applies for specific engines with table locks, i.e. MyISAM)
• It's much harder to implement rollbacks
• possible data conversion errors / precision loss because of implicit type conversion
• doesn't keep track of changes when you directly access the database somewhere in your code instead of using your model / data layer and forget that in this case you must write to the revision log manually. May be a big issue when working in a team with other programmers.

结论:

• 选项B 对于小型应用程序非常方便，因为当它仅用于记录更改时，它是一个简单的插入".
• 如果您想回到过去，并且能够轻松比较历史修订版 123 与修订版 125 和/或还原为旧数据之间的差异，那么选项A 是很难的选择.

• Option B can be very handy for small apps as a simple "drop in" when its just for logging changes.
• If you want to go back in time and be able to easily compare the differences between historic revison 123 to revision 125 and/or revert to the old data, then Option A is the hard way to go.

这篇关于存储所有细节的所有数据更改(例如Stackoverflow)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！