The most effective way to create an activity log

Question

I’m doing the “last steps” tab for profiles on my site, and I will also have a magazine for moderators to see everything that happens on the site. This will require the creation of an activity log.

I just don’t know what will be better. I have 2 options:

• Create a table called "activity", and then every time someone does something, add an entry with the type of action, user ID, timestamp, etc. to it.
  • Problem: the table can be very long.
• Combine all 3 tables (questions, answers, comments_responses), and then somehow show all this on the page in the order in which the action was taken.
  • Problem: it would be very difficult, because I have no idea how I can say that "John commented on the answer to the question" The name of the question is here, "simply by adding 3 tables.

Does anyone know a better way to make an activity log in this situation? I use PHP and MySQL. If this is too inefficient or complicated, I’ll probably just forget the Recent Activity tab for profiles, but I still need an activity log for moderators.

Here is some SQL that I started doing for option 2, but it didn’t work, because there is no way to determine if the action is a comment, question or answer when I repeat the information in a while :

 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 your help!

Answer 1

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

For option 2 (the best IMO option - if you pointed it out correctly), I think UNION more than what you are looking for. Something like that:

 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 

'question' / 'answer' / 'comment' tells what action was taken. Possible problems you may encounter: UNION , each SELECT must have the same number of columns, so if one of them is short, you can simply add NULL for example:

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

Alternatively, if one of the created columns has a different name, you can simply alias

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

Answer 2

I like "option 2", you will basically duplicate your data, and this will slightly slow down the work with additional reads / writes. Perhaps instead of doing

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

You can either simply get the data that you need from each table, or Union three tables can be a slightly cleaner way, after you have limited your request to only those messages of the selected user and order the date of departure.