为CRUD操作创建历史表的最佳方法

I asked myself what would be the best way to create a history table for a website, I'm only aware of two choices:

我问自己,为网站创建历史表的最佳方法是什么,我只知道两个选择:

Use triggers
Add an extra insert statement to the code when an insert/update/delete statement related to it is used

当使用与其相关的插入/更新/删除语句时,向代码添加额外的insert语句

They said that triggers would be a better way since the load would be on the database and not in the program. But since my website has multiple admins, I would also need to track who modified the content, which I think would only be possible by creating a modified_by column in the history table and manually insert values to it by inserting the session user to that column using the second option together with the time_modified, modified_from, modified_tovalues.

他们说触发器是一种更好的方法,因为负载将在数据库而不是程序中。但由于我的网站有多个管理员,我还需要跟踪谁修改了内容,我认为这只能通过在历史记录表中创建modified_by列并通过将会话用户插入该列来手动插入值来实现。第二个选项与time_modified,modified_from,modified_tovalues一起使用。

I need to find out if would this be an acceptable reason to use the second option? Are there any more options? Will the second option create any problems in the future?

我需要弄清楚这是否是使用第二种选择的可接受理由?还有其他选择吗?第二种选择是否会在未来产生任何问题?

3 个解决方案

#1

There is a Rails gem https://github.com/airblade/paper_trail that implements your mentioned functionality comprehensively. I would assume it is doing it purely at the application code level and not the database(triggers) level. This could further indicate that option 2 is better. My thoughts are:

有一个Rails gem https://github.com/airblade/paper_trail可以全面实现您提到的功能。我认为它纯粹是在应用程序代码级别而不是数据库(触发器)级别。这可以进一步表明选项2更好。我的想法是:

If you use triggers, you will have a serious performance trade off as your site traffic grows and CRUD operations go up. A lot of triggers will go off then.

如果您使用触发器,随着站点流量的增加和CRUD操作的增加,您将会有严重的性能折衷。那么很多触发器都会消失。
Implementing some part of the business logic at the database level might be tempting but I would like to keep it all in one place that is in my application code.

在数据库级别实现业务逻辑的某些部分可能很诱人,但我想将它全部保存在我的应用程序代码中的一个位置。
You will have some serious thinking to do if you wish to keep your application database agnostic. You will need to re-implement the triggers if you use a different database server.

如果您希望保持应用程序数据库不可知,您将有一些认真的想法。如果使用其他数据库服务器,则需要重新实现触发器。
History keeping increases table size and might become a bottleneck for database performance. You have the option of keeping history for a limited time interval and then archive it to keep the database table nice and clean. Also, you can have a separate database server responsible for history related tables only. These things will be complex to do with triggers.

历史记录会增加表的大小,并可能成为数据库性能的瓶颈。您可以选择在有限的时间间隔内保留历史记录,然后将其存档以使数据库表保持良好和干净。此外,您可以拥有一个单独的数据库服务器,仅负责历史记录相关的表。这些事情与触发器很复杂。

#2

I can suggest solution # 2. Because i like when all business logic contained in one place (in PHP backend). This code will be more supportable and reusable. And you can save only changed fields into some JSON format. So it keep your HDD place and will work fast. I think triggers is very bad stuff, because you don't know what happens in next time in DB (you must always remember all your triggers):) So i don't use it.

我可以建议解决方案#2。因为我喜欢所有业务逻辑包含在一个地方(在PHP后端)。此代码将更具可支持性和可重用性。并且您只能将更改的字段保存为某种JSON格式。所以它保持你的硬盘位置,并将快速工作。我认为触发器是非常糟糕的东西,因为你不知道下次在DB中会发生什么(你必须永远记住你所有的触发器):)所以我不使用它。

Only one problem you will have - a lot of records in history table. So, i recommend remove records oldest that 3 month

你只会遇到一个问题 - 历史表中有很多记录。因此,我建议删除最早3个月的记录

#3

I'd go with option 2. If you are comfortable with ORMs, I'd recommend using one here: a good deal of the history collection code is already written and tested for you.

我选择选项2.如果您对ORM感到满意,我建议您在这里使用一个:大量的历史收集代码已经为您编写和测试。

For example, if you were to use Propel, it comes with a versionable behaviour, which manages a separate versions table, and version numbers per row. (Aside: I believe version 2 hasn't been released as stable yet, though it is linked from the project home page. I use version 1.7, which is still excellent. Both versions have this feature as far as I know).

例如,如果您要使用Propel,它会附带一个版本化行为,它管理一个单独的版本表,以及每行的版本号。 (旁白:我认为版本2还没有稳定发布,虽然它是从项目主页链接的。我使用版本1.7,这仍然很棒。据我所知,这两个版本都有这个功能)。

Doctrine has a similar feature, Versionable, though it looks like that is deprecated in favour of EntityAudit.

Doctrine有一个类似的功能,Versionable,虽然它看起来像是弃用了EntityAudit。

#1

If you use triggers, you will have a serious performance trade off as your site traffic grows and CRUD operations go up. A lot of triggers will go off then.

如果您使用触发器,随着站点流量的增加和CRUD操作的增加,您将会有严重的性能折衷。那么很多触发器都会消失。
Implementing some part of the business logic at the database level might be tempting but I would like to keep it all in one place that is in my application code.

在数据库级别实现业务逻辑的某些部分可能很诱人,但我想将它全部保存在我的应用程序代码中的一个位置。
You will have some serious thinking to do if you wish to keep your application database agnostic. You will need to re-implement the triggers if you use a different database server.

如果您希望保持应用程序数据库不可知,您将有一些认真的想法。如果使用其他数据库服务器,则需要重新实现触发器。
History keeping increases table size and might become a bottleneck for database performance. You have the option of keeping history for a limited time interval and then archive it to keep the database table nice and clean. Also, you can have a separate database server responsible for history related tables only. These things will be complex to do with triggers.

历史记录会增加表的大小,并可能成为数据库性能的瓶颈。您可以选择在有限的时间间隔内保留历史记录,然后将其存档以使数据库表保持良好和干净。此外,您可以拥有一个单独的数据库服务器,仅负责历史记录相关的表。这些事情与触发器很复杂。

#2