What is the best practice for logging users activity?

Question

I'd like to implement users' activity logging for my web app (php+js+mysql).

Previously, I've used this appoach:

• on users login create a temporary table in the database to store users id
• create triggers for all tables which insert row in activity table using users id from that temporary table

Now I don't really want to put so much logic in the database, so my question is: what is the best practice? Should I stay with the described method, or should I use something else?

Edit 1

I've seen this question, but I'm intrested in comparing the described method with one in the answers.

Edit 2

Why do I need logs: I need to know which user to blame if something goes wrong =)

Logs have to contain changed data and new data to see what has actually changed.

There won't be many users cause it's a corporate app and our company is not so large.

The main question is where should I put logging logics: database or application (php backend) level?

Answer 1

As always, "it depends".

If the evolution over time of your core business concepts is important, it should be a first-class concept in your database design, as well as your PHP logic. Fowler writes about this in "Analysis patterns". This allows you to capture who made which changes to your business objects and answer questions like "who changed the project from type x to y on date z?", "how often did user x change product y?" etc. It does make the domain model more complex.

If you don't need this level of integration, my preference is to put the logging functionality in PHP; triggers have a horrible way of slowing down a database, or leading to unexpected side effects, or being forgotten by developers making changes to the schema. So, in PHP, I'd include explicit "log this" statements, possibly using an aspect-oriented framework (though I've never used that in PHP, only in Java).

Answer 2

For logging, you want to have a table logs, where you log the session id $_SESSION['id'] (presuming you have sessions?) and the user's activity. You then insert it using a delayed MySQL query (because the logs are not high priority):

INSERT DELAYED INTO table (session_id, activity) VALUES ('1234', 'blah');

Refer to this link for more information on DELAYED inserts.

In other words, put all the logic on the PHP side, just have a MySQL table in which you log any activities using delayed. This would be a function log_activity($session_id, $activity) that you can call from anywhere where there is a loggable activity.