user activity database structure

Question

Hello. 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

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.

Answer 2

• I don't see why you need the friends id. For the friends page you would first do a select for all his friends, then select from activity table where user_id in (2,6,89 etc)

• I would make photo and video fields a single field called type, where photo and video would be values, this way you can later on add more activity types

• I would make p_id and v_id a single column called item_id .. no need for 2 columns

• I would add an extra column called info where I would store other information in a json format. This is for the extra data that not all events have. For example, you could have an event for adding a link to the profile ... and you could place the link there, since other events don't have urls and adding a column just for this event type would be not a good solution

Answer 3

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