Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In my current project I use about 10 MySQL tables (Doctrine as DBAL). I'd like to keep track of all changes in the databases (insertions, updates and deletions).

At the moment there are two approaches which might be the solution:

  • Create a table named history which stores all changes. Each time there is a change insert a new row in this table. It might have this fields:

    id

    target_table (name of the table)

    target_id (ID of the saved entry)

    created_at

    data (serialized data of the saved row)

  • Add to each the table the following columns:

    history_id (NULL: most recent entry, history_id = b.id: an old state of b, history.id pointing to nowhere: deleted object)

Are there any other approaches to keep track of all changes? Would you recommend one of my approaches?

share|improve this question
    
The second one appears to me as more effective if yo want to access history often. –  ipip Apr 6 '12 at 16:33
    
It would be good to know you RDMS, it may already support auditing. –  Jon of All Trades Apr 6 '12 at 17:06
    
@JonofAllTrades: Sorry, I forgot to mention that I'd like to use MySQL. I updated the question accordingly. –  SecStone Apr 6 '12 at 17:18
add comment

1 Answer 1

The answer depends on what you intend to do with the data. Are you going to be doing frequent queries against it or do you only need it for the occasional lookup of what happened when, to rollback bad changes, or for regulatory reasons?

In the first case above, use the second process you descibed although I would add a column to denote the active record. I would also add a record created date. I wouild also create a view of just the active records and use that for allof my code that needs to see the current data.

In the second case above, I would have audit tables that are populated through triggers (the offective way to create audit records for any change to the db) and that also include the data of the change and the user or application that made the change as well as the old and new values.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.