CODEWITHSUNDEEP
phpmysqldatabase
Carl Dun
Carl Dun

Reputation: 235

Best way to create a history table for CRUD operations

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

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

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.

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?

Upvotes: 4

Views: 2167

Answers (3)

halfer
halfer

Reputation: 20429

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.

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).

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

Upvotes: 1

Noman Ur Rehman
Noman Ur Rehman

Reputation: 6957

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:

  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.

  2. 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.

  3. 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.

  4. 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.

Upvotes: 3

stepozer
stepozer

Reputation: 1191

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.

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

Upvotes: 1

Related Questions