user activity database structure
data-structures, database, mysql, php, sql
Solution
I think you're correct that a single-table approach is best here. One disadvantage, however, is that it doesn't scale well--what if you want to add link or comment activity types? With this model you'd have to add another column for each of those. An approach that I've seen in Rails-land is to use a polymorphic model, which would look like this:
id | user_id | activity_type_id | p_id | v_id | f_id | datetime
You can see that I've replaced video, photo, etc. with `activity_type_id`. Then there would be a second table called `activity_types`:
id | name
----+-------
1 | photo
2 | video
3 | ...
Then when you create a `members_activity` record you can assign the appropriate `activity_type_id`, and if you want to create new activity types later on it's relatively painless, and you could `SELECT` a particular kind of activity with a simple `JOIN`, e.g.:
SELECT * FROM members_activity
JOIN activity_types ON members_activity.activity_type_id = activity_types.id
WHERE activity_types.name = 'photo';
Problem
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: ``` id | user_id | photo | video | friend | p_id | v_id | f_id | datetime ``` let's say that the user has just uploaded an image. ``` id | user_id | photo | video | friend | p_id | v_id | f_id | datetime 1 | 15 | 1 | 0 | 0 | 1203 | 0 | 0 | NOW() ``` 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.