user activity database structure

Question

I am working on a community website. I want to show the user's activity in 2 places in the website.

1. The User "A" Profile.
2. 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.

Answer 1

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';

Answer 2

If you have a huge number of rows, it is really not going to be a practical disadvantage as long as you index the table properly.

At the very least I would index user_id and datetime, assuming you will be selecting activity for a particular user and ordering by date.

Use MySQL's EXPLAIN (<query>) to ensure your indexes are optimised for the queries you are running often.