MySQL Trigger or PHP for data audit?

Question

I am looking to keep the history of every single update of the tables, on my application. I am using Laravel, I know there is some package that can help me but I am looking something clean and fast.

I have two options:

• MySQL Trigger
• Via PHP code

My first question is, which one is faster ?

My second question is: should I use a schema like this and store all my tables into it:

CREATE TABLE revisions (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `revisionable_type` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `revisionable_id` INT(11) NOT NULL,
    `user_id` INT(11) NULL DEFAULT NULL,
    `key` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `old_value` TEXT NULL COLLATE 'utf8_unicode_ci',
    `new_value` TEXT NULL COLLATE 'utf8_unicode_ci',
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL
)

Or should I recreate a "duplicate" of every table like user_history and add the history on the respectives columns

Users: id, name, surname ...
Users_history: id, user_id, name, surname ... 

Answer 1

Given this schema:

CREATE TABLE contacts (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `last_name` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `updated_by` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `updated_at` TIMESTAMP NULL DEFAULT NULL
)

I would create this schema as well:

CREATE TABLE contacts_audit (
    `id` INT(10) UNSIGNED NOT NULL,
    `first_name` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `last_name` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `updated_by` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `action` varchar(255) NOT NULL COLLATE 'utf8_unicode_ci'
)

Then use these triggers (hopefully my trigger syntax isn't too rusty):

CREATE TRIGGER contacts_audit_insert
AFTER INSERT
   ON users FOR EACH ROW
BEGIN
    INSERT INTO contacts_audit (id, first_name, last_name, updated_by, updated_at, action) values (NEW.id, NEW.first_name, NEW.last_name, NEW.updated_by, NEW.updated_at, 'insert');
END;

CREATE TRIGGER contacts_audit_update
AFTER UPDATE
   ON users FOR EACH ROW
BEGIN
    INSERT INTO contacts_audit (id, first_name, last_name, updated_by, updated_at, action) values (NEW.id, NEW.first_name, NEW.last_name, NEW.updated_by, NEW.updated_at, 'update');
END;

CREATE TRIGGER contacts_audit_delete
BEFORE DELETE
   ON users FOR EACH ROW
BEGIN
    INSERT INTO contacts_audit (id, first_name, last_name, updated_by, updated_at, action) values (OLD.id, OLD.first_name, OLD.last_name, OLD.updated_by, OLD.updated_at, 'delete');
END;

Potential drawbacks

If you are using one MySQL username/password for database access and authenticating your users against a users table in MySQL then prior to performing any deletes, you will want to perform an update, within PHP, followed by the delete so that you can capture who deleted your record.

However if you are using MySQL for authentication of all users then you can have this in your triggers: Take notice to the use of USER()

INSERT INTO contacts_audit (id, first_name, last_name, updated_by, updated_at, action) values (NEW.id, NEW.first_name, NEW.last_name, USER(), NEW.updated_at, 'insert');