I am working on a community website. I want to show the user's activity in 2 places in the website.[*]The User "A" Profile.[*]The Friends page of the user "A" friends. "What are your friends doing?"The tables for instance are:
members
members_gallery
members_videos
members_friends
my problem is in the Sql structure. I've read this question "User recent activities - PHP MySql"The "union" idea is good but I have an alternative one. I am going to make a new table called
members_activity
The fields:\[code\]id | user_id | photo | video | friend | p_id | v_id | f_id | datetime\[/code\]let's say that the user has just uploaded an image.\[code\]id | user_id | photo | video | friend | p_id | v_id | f_id | datetime1 | 15 | 1 | 0 | 0 | 1203 | 0 | 0 | NOW()\[/code\]advantages:
When i make a SELECT QUERY, i can easily know if it's a photo, video, or a friendship activity.
The user can delete the 'photo activity' but keep the photo.
Can notify friends of the user easily.
disadvantages:
Huge number of table rows?
Any ideas, or suggestions how the big websites deal with it? digg, facebook, etc.
