Most efficient way to make an activity log

Question

I am making a "recent activity" tab to profiles on my site and I also am going to have a log for moderators to see everything that happens on the site. This would require making an activity log of some sort.

I just don't know what would be better. I have 2 options:

1. Make a table called "activity" and then every time someone does something, add a record to it with the type of action, user id, timestamp, etc.
   • Problem: table could get very long.
2. Join all 3 tables (questions, answers, answer_comments) and then somehow show all these on the page in the order in which the action was taken.
   • Problem: this would be extremely hard because I have no clue how I could make it say "John commented on an answer on Question Title Here" by just joining 3 tables.

Does anyone know of a better way of making an activity log in this situation? I am using PHP and MySQL. If this is either too inefficient or hard I will probably just forget the Recent Activity tab on profiles but I still need an activity log for moderators.

Here's some SQL that I started making for option 2, but this would not work because there is no way of detecting whether the action is a comment, question, or answer when I echo the info in a while loop:

SELECT q.*, a.*, ac.* 
    FROM questions q JOIN answers a ON a.questionid = q.qid
        JOIN answer_comments ac ON c.answerid = a.ans_id
WHERE q.user = $userid 
AND a.userid = $userid 
AND ac.userid = $userid
    ORDER BY q.created DESC, a.created DESC, ac.created DESC

Thanks in advance for any help!

Answer 1

Why do you have q.user and q.userid?

For option 2 (the better option IMO - as long as you've indexed properly), I think a UNION is more what you're looking for. Something like this:

SELECT 'question' AS action, id, created 
    FROM questions WHERE userid = {$userid}
    UNION
SELECT 'answer' AS action, id, created
    FROM answers WHERE userid = {$userid}
    UNION
SELECT 'comment' AS action, id, created
    FROM answer_comments WHERE userid = {$userid}
ORDER BY created DESC LIMIT 20

The 'question' / 'answer' / 'comment' tells you which action was taken. Possible issues you may run into: being a UNION, each SELECT statement must have the same number of columns, so if one is short, you can just add a NULL e.g.:

SELECT 'comment', id, created, NULL FROM ac

Also, if one of the created columns has a different name you can just alias

SELECT 'comment', id, comment_date AS created FROM ac

Answer 2

I like "option 2", you would basically be duplicating your data and it would slow things down a bit with the extra reads/writes. Maybe instead of doing a

SELECT q.*, a.*, ac.* 

You could either just get the data that you need from each table, or a slightly cleaner way may be to do a Union of the three tables, after limiting your query to only those posts by selected user, and order by the date posted.