Save Database field changes - best practices? Versioning, Loggable?

Question

i am using Symfony 2 with Doctrine as ORM Framework. I am searching for the best way to save changes done to database fields. I will have about 100 Tables each with about 50 fields and some thousand rows. Now i would like to save all changes done to the fields.

Possibilities i thought about: Doctrine extension "Loggable" - saves changes in a different Table, but don't know if it can afford this amount of entries.

a MySQL Trigger for each Table that saves changes in a new Table?

But what is the best practice to save changes?

Answer 1

You can use either MySQL triggers or the mentioned DoctrineExtension Loggable feature. Both works, both has cons and pros. MySQL trigger can write into a separate table (see mysql trigger FAQ).

triggers:

• ++ framework, programming language independent
• ++ works when you want to modify the data by hand or by a script.
• -- You have to write the triggers for every table or have to figure out some generic solution in SQL (I can't help on that).
• -- If you are not familiar with stored procedures and PL/SQL, well, there is learning curve

doctrine extensions:

• ++ Just put your annotation on classes and you're done.
• ++ You can query the history, revert changes through the Repository API
• -- you lock yourself to a vendor, this sometimes is, sometimes isn't a problem
• -- doesn't works when you modify the data by hand or with a 3rd party scripts.

If the chance of switching doctrine to something else is low, I would start with doctrine extensions. It's a tool with the exact purpose to help dealing with SQL after all.