Selasa, 24 Juni 2014

The most effective way to store user activity

Today, I made a company website. The website owner asked me to store all of user activities. 
This challenge is quite interesting. The normal way is storing the data right in the table in question. But, I think the best way is storing the data into is just using one table.

All we have to do is create a table to store it with json format.

You can use array and then encode it into json format.
I use a PHP code to explain;
//activity data
$user_id = $_SESSION['user_id'];
$activity = "INSERT";
$table = "posts";
$data = array(
    'id' => $id,
    'post_date' => $post_date,
    'post_content' => $post_content
$the_data = json_encode($data);
mysql_query("insert into user_activity_table (user_id, activity, table, data) values ('$user_id', '$activity','$table','$the_data')");

This is an example of user_activity_table
| ID | user_id | activity | table  | data                 |
| 1  | 1       | INSERT   | posts  |{ "id": "2", [...] }  |
| 2  | 3       | UPDATE   | photos |{ "id": "5", [...] }  |

We can take a lot of advantage of using json format. Among the advantages is we can recover deleted data.

In the end I say, you can elaborate the method according to your needs. :)