I'm trying to design an activity feed, but I have a small problem.
Here's my current table.
id user_id status activitytype_id (status, video, photo, etc) created_at location video
I didn't add some unrelated columns to make it short.
This works when I want to create a feed for the friends' posts of a user. I basically create an array with PHP and check for user_id's in the activity table and sort them by date.
But a user also follows groups and events, this is the tricky part. Do I need to create a second query for groups and events and merge with the user posts together?
What's the best way to include them (groups and events) in the feed? I can add a column to the activity called event_post and group_post and add posts id's as foreign keys to the activity table (null by default), but I'd like to hear your opinions.
id communitytype_id (group or event) name created_at user_id (FK from users table, owner of the group or event)