Storing logs on mysql database

Question

I was looking for more information on logging user actions on the application (web based project). I did found this question, but it's a more conceptual answer, wich helped me getting started with this.

But I don't know for sure how to build this structure for the log system and also how to log the information. Everywhere people say "log everything", but that's quite embrancing.

Note: I'm already using HeatMap to gather user navigation information and GoogleAnalytics for other type of information.

Currently I have this table on the database e.x.: ap_log:

id | user_id | action | area | description | old_value | new_value | date

And I use it like this:

• action: Number of the action 1: Insert | 2: Update | 3: Delete | 4: other;
• area: Area of the action, ex.: "account" for login, "adress" when adding a new address, etc..;
• description: A short description of the action. "User login", "Add new address";
• old/new_value: Serialized object with the data changed. Ex.: When user update the address, I save the old values on the old_value column and the new address information on new_value;
• date: Date time of the log Y-m-d h:m:s

The columns action, area and description they are strings that is inserted on the database. For example, I call a function like this:

// insertLog(user_id, action, area, description );
insertLog($userId, 1, 'address', 'Add new address');
insertLog($userId, 4, 'account', 'User login');
insertLog($userId, 4, 'account', 'Email sent to recover password');
insertLog($userId, 2, 'account', 'User reset password');

With this, I'm only loging information that is stored in the database, such as user profile, purchases, address, list management, etc.. Is this database table ok and enought for this purpose? Is there a way to improve this? Any advice is very welcome.

Answer 1

That table layout looks very similar to what I've used in the past for a few different projects, and I think it should be sufficient. At this point, the biggest thing you will probably run into is figuring out the right amount of logging (ie. yes you want to log user logins, but you also probably want to log failed login attempts, and depending on the type of information visible to admins/moderators/etc you may want audit trail logs for simply accessing/reading certain types of information as well).