Checking if a user changed any of their data in multiple tables

Question

In my database, I have several tables. One is a checkpoint table that makes note of a user choosing to finalize one of their projects. This table contains a timestamp that is automatically created. Whenever a user finalizes their project a new row is added to the checkpoint table (that way we can also keep a history of previous times the project was finalized).

I have several other tables with timestamps (or tables that I could add timestamp columns too) that automatically update when their tables change.

Is there a simple way to be able to tell if any of the other tables have updated their data since the project was last finalized? I do not need to know which tables have changed data just that there are tables that have changed data.

For example, if a user changes data in one of their tables I want to be able to display a message indicating that their project has unfinalized data.

There are a couple of ways that I have thought about doing this:

1. Checking every single table to see if any timestamps are newer than the latest timestamp in the checkpoint table.
2. Add an additional timestamp column (I already have a created and updated timestamp column) to the main project table. Most of the other tables are linked directly or indirectly to this main project table. Add triggers to every other table to update this timestamp when their data changes. I am not quite sure yet how to correctly set up a proper trigger for this.
3. Creating a new table with just the project_id and a timestamp column. Add a trigger to the other tables as shown in option 2.

As new modules are added, I will be adding more tables to the project so will need something that is easy to scale as well.

Each of these approaches seems like there would be a lot of steps involved.

Would one of these approaches be more efficient or viable than another? Is there another approach that I am not thinking about? If triggers are the best way to do this how would I go about setting up the trigger?

A simplified overview of my tables looks like this:

main_project_table
    id
    user_id (FK to user_table)
    created_timestamp
    updated_timestamp

checkpoint_group_table (users can choose which group to finalize their project too)
    id
    user_id (FK to user_table)
    group_name

checkpoint_table (the table that records the finalized data and time of finalization)
    id
    checkpoint_group_id (FK to checkpoint_group_table)
    project_id (FK to main_project_table)
    project_finalized_timestamp

 parent_table (several of these)
     id
     project_id (FK to main_project_table)

 child_table (0 or more of these for each parent_table)
     id
     parent_id (FK to parent_table)

Answer 1

You really only have three solutions: Middleware, Triggers, and General Log File.

Middleware solution:

Add a timestamp field to each relevant table, and set the default value is set to "CURRENT_TIMESTAMP". This will update the timestamp field to the current time on every update. Assuming that users are going through some API, you can write a JOIN query where it returns the latest time stamp. It would look like this.

SELECT
    CASE 
        WHEN b.timestamp IS NOT NULL THEN 0
        WHEN c.timestamp IS NOT NULL THEN 0
        WHEN d.timestamp IS NOT NULL THEN 0
        WHEN e.timestamp IS NOT NULL THEN 0
        ELSE 1
     AS `test`
FROM checkpoint_table a
LEFT JOIN main_project_table b 
    ON a.project_id = b.id 
    AND b.timestamp > a.project_finalized_timestamp
LEFT JOIN checkpoint_group_table c 
    ON b.user_id = c.user_id
    AND c.timestamp > a.project_finalized_timestamp
LEFT JOIN parent_table d 
    ON b.id = d.project_id
    AND d.timestamp > a.project_finalized_timestamp
LEFT JOIN child_table e ON d.id = e.parent_id
    ON b.id = d.project_id
    AND e.timestamp > a.project_finalized_timestamp

Now when a request routed to the tables you can run this query and if test == 0, then you return the message.

<?php
      class middleware{
          public function getMessage(){
              // run query

              if($data[0]['test'] == 1){
                   return "project has unfinalized data";
              }else{
                   return null;
              }
          }
      }

Trigger Solution:

CREATE TRIGGER checkpoint_group_table 
AFTER UPDATE on _table_
FOR EACH ROW UPDATE _table_  
SET main_project_table.updated_timestamp = CURTIME() 
WHERE main_project_table.user_id=checkpoint_group_table.id

The advantages to this are that it is perhaps more elegant than the middleware solution. The disadvantages are that triggers are not in plain view, and it is my experience, that when processes are in the background they eventually are forgotten. In the long term, you could be left with this Jenga puzzle, which would make like difficult.

General Log File Solution:

Mysql can log every query on the server. It is possible to access this log file during the time, parse it out, and figure out if any tables were updated. This way you can figure if anything was updated after the project was finalized.

Turn on a general log file.

SET GLOBAL general_log = 'ON';

Set the path of the log file.

SET GLOBAL general_log_file = 'var/log/mysql/mysql_general.log'

Confirm by going to the command terminal.

mysql -se "SHOW VARIABLES" | grep  -e general_log

You might need to reset MySQL.

sudo service MySQL restart

This script can you started...

$v = shell_exec("sudo less /var/log/mysql/mysql_general.log");

$lines = explode("\n",$v);

$new = array();
foreach($lines as $i => $line){
    if(substr($line,0,1) != " "){
        if(isset($l)){
            array_push($new,$l);
        }
        $l = $line;
    }else{
        $l.= preg_replace('/\s+/', ' ', $line);
    }
}

$lines = $new;

$index = array();
foreach($lines as $i => $line){
    $e = explode("\t",$line);
    $new = array();
    foreach($e as $key => $value){
        $new[$key] = trim($value);
    }

    $index[$i] = $new;

}

This will result in this...

array(3) {
  [0]=> string(27) "2017-10-01T08:17:04.659274Z"
  [1]=> string(8) "70 Query"
  [2]=> string(129) "UPDATE checkpoint_group_table SET group_name = 'Dev Group' Where id=6"

}

From here you can use a library called PHP-SQL-Parser to parse out the query.

The advantages to this approach might scale well, being that you will not have to add any columns to your database. The disadvantages are that this will involve more code and that means more complexity. You probably cannot really do this solution without writing unit tests for it.

Answer 2

If I would have been at your situation, I would have made a table with fields project id (FK) and boolean for is_finalized. So every time a project is finalized, I would add an entry in it.

+-----------------+--------------+
| project_id      | is_finalized |
|-----------------|--------------|
| 12              | 1            |
+-----------------+--------------+

before any update/insert, Just check if this key exists for my project. if exists, change it to 0 and while loading the file, Just check if the value is 0. If 0, then show the Message: project has unfinalized data.

It should show the message only if the key exists and the value is 0. If the project is not finalized. The table won't have the value, hence no message.

Quite easy, faster in processing (rather than checking each timestamp) and extensible approach as it would be just dependent on the update or insert queries, which you can use in you upcoming modules in future.