History of data changes with audit tables: grouping of changes

Question

Suppose I want to store users and groups in a MySQL database. They have an n: m ratio. To track all changes, each table has an audit table user_journal, group_journal and user_group_journal. MySQL starts copying the current record to the log table on each INSERT or UPDATE (DELETES are not supported, since I need information that the user of the application deleted, so there is an active flag that will be set to 0 instead of deleting).

My question / problem: I assume that I add 10 users to the group right away. When I later move on to the history of this group in the user interface of the application, I want to add these 10 users as one step , and not as 10 independent steps. Is there a good solution to group such changes? Perhaps there is a counter that increments every time a trigger fires? I have never worked with triggers.

The best solution would be to collect all the changes made as part of the transaction. Therefore, when a user updates the group name and adds 10 users in one step (one call to the form controller), it will be one step in the history. Maybe you can determine a random hash or graft a global counter every time you start a transaction and access this value in a trigger?

I do not want the table design to be more complex than one log table for each "real" table. I do not want to add a transaction hash to each database table (which means "real" tables, not audit tables - that would be fine there, of course). Also, I would like to have the solution in the database, not in the application.

Answer 1

I played a little and now I have found a very good solution:

Database setup

 # First of all I create the database and the basic table: DROP DATABASE `mytest`; CREATE DATABASE `mytest`; USE `mytest`; CREATE TABLE `test` ( `id` INT PRIMARY KEY AUTO_INCREMENT, `something` VARCHAR(255) NOT NULL ); # Then I add an audit table to the database: CREATE TABLE `audit_trail_test` ( `_id` INT PRIMARY KEY AUTO_INCREMENT, `_revision_id` VARCHAR(255) NOT NULL, `id` INT NOT NULL, `something` VARCHAR(255) NOT NULL ); # I added a field _revision_id to it. This is # the ID that groups together all changes a # user made within a request of that web # application (written in PHP). So we need a # third table to store the time and the user # that made the changes of that revision: CREATE TABLE `audit_trail_revisions` ( `id` INT PRIMARY KEY AUTO_INCREMENT, `user_id` INT NOT NULL, `time` DATETIME NOT NULL ); # Now we need a procedure that creates a # record in the revisions table each time an # insert or update trigger will be called. DELIMITER $$ CREATE PROCEDURE create_revision_record() BEGIN IF @revision_id IS NULL THEN INSERT INTO `audit_trail_revisions` (user_id, `time`) VALUES (@user_id, @time); SET @revision_id = LAST_INSERT_ID(); END IF; END; # It checks if a user defined variable # @revision_id is set and if not it creates # the row and stores the generated ID (auto # increment) into that variable. # # Next I wrote the two triggers: CREATE TRIGGER `test_insert` AFTER INSERT ON `test` FOR EACH ROW BEGIN CALL create_revision_record(); INSERT INTO `audit_trail_test` ( id, something, _revision_id ) VALUES ( NEW.id, NEW.something, @revision_id ); END; $$ CREATE TRIGGER `test_update` AFTER UPDATE ON `test` FOR EACH ROW BEGIN CALL create_revision_record(); INSERT INTO `audit_trail_test` ( id, something, _revision_id ) VALUES ( NEW.id, NEW.something, @revision_id ); END; $$ 

Application Code (PHP)

 $iUserId = 42; $Database = new \mysqli('localhost', 'root', 'root', 'mytest'); if (!$Database->query('SET @user_id = ' . $iUserId . ', @time = NOW()')) die($Database->error); if (!$Database->query('INSERT INTO `test` VALUES (NULL, "foo")')) die($Database->error); if (!$Database->query('UPDATE `test` SET `something` = "bar"')) die($Database->error); // To simulate a second request we close the connection, // sleep 2 seconds and create a second connection. $Database->close(); sleep(2); $Database = new \mysqli('localhost', 'root', 'root', 'mytest'); if (!$Database->query('SET @user_id = ' . $iUserId . ', @time = NOW()')) die($Database->error); if (!$Database->query('UPDATE `test` SET `something` = "baz"')) die($Database->error); 

And ... the result

 mysql> select * from test; +----+-----------+ | id | something | +----+-----------+ | 1 | baz | +----+-----------+ 1 row in set (0.00 sec) mysql> select * from audit_trail_test; +-----+--------------+----+-----------+ | _id | _revision_id | id | something | +-----+--------------+----+-----------+ | 1 | 1 | 1 | foo | | 2 | 1 | 1 | bar | | 3 | 2 | 1 | baz | +-----+--------------+----+-----------+ 3 rows in set (0.00 sec) mysql> select * from audit_trail_revisions; +----+---------+---------------------+ | id | user_id | time | +----+---------+---------------------+ | 1 | 42 | 2013-02-03 17:13:20 | | 2 | 42 | 2013-02-03 17:13:22 | +----+---------+---------------------+ 2 rows in set (0.00 sec) 

Please let me know if there is a point that I missed. I will need to add an action column to the audit tables in order to be able to record deletions.

Answer 2

Assuming you add a batch of users to a group less than once per second ....

I would suggest just adding a column of type timestamp, named as added_timestamp , to user_group and user_group_journal . DO NOT DO THIS AUTOMATIC TIME UPDATE OR REFER TO CURRENT_TIMESTAMP , instead, in your code, when you insert the package into user_group, calculate the current date and time, then manually set this for all new user_group entries.

You may need to tweak your setting to add a field to copy the rest of the new user_group entry to the user_group table.

Then, when you can create a query / view that groups in group_id and a new column added_timestamp .

If more fidelity is required, then in 1 second you can use a column of rows and fill it with a string representation of more granular time (which you will need to generate, but the libraries used by your language of use).