So I have a php web application that allows updating of records. I would like to track the changes of only the fields that have changed. There are plenty of how to's that involve recreating the original row in a separate table with an additional time/date stamp column but I think this is a big waste of space.
What I would like to achieve is a changelog table:
id - INT - NOT NULL - AUTO INCREMENT
FK_id -INT - NOT NULL //PK of the row in the original table
changed_field_name - VARCHAR - NOT NULL
old_value - VARCHAR - NULL //maybe an empty field
new_values - VARCHAR - NOT NULL
timestamp - YYYY-MM-DD:HH:MM:SS
FK_user_id - INT - NOT NULL //PK from the user table
The data in the original table consists of various formats including text, varchar, dates, integers - several fields may be updated at the same time, in which case several rows will need to be added to the changelog table. The changelog table will only be used to display the changed data in a form.
I think the best way to achieve this is with a trigger?
any help would be welcome.
Thanks