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!