User Activity Database Structure

Question

I work on a community website. I want to show user activity in 2 places on a website.

• User profile "A".
• Friends page of user "A". "What are your friends doing?"

---

Here are the tables, for example:

• Members
• members_gallery
• members_videos
• members_friends

---

My problem is the Sql structure. I read this question "Recent User Actions - PHP MySql"

The idea of ​​a “union” is good, but I have an alternative. I am going to create a new table called

• members_activity

Fields:

id | user_id | photo | video | friend | p_id | v_id | f_id | datetime

let's say the user just uploaded the image.

id | user_id | photo | video | friend | p_id | v_id | f_id | datetime
1  |   15    |   1   |   0   |    0   | 1203 |   0  |   0  |  NOW()

advantages:

• SELECT QUERY, , , .
• " ", .
• .

:

• ?

, ? digg, facebook ..

Answer 1

, , . , - , ? . , Rails-land, - , :

id | user_id | activity_type_id | p_id | v_id | f_id | datetime

, , .. activity_type_id. activity_types:

 id | name
----+-------
  1 | photo
  2 | video
  3 | ...

, members_activity, activity_type_id, , , SELECT JOIN, :

SELECT * FROM members_activity
  JOIN activity_types ON members_activity.activity_type_id = activity_types.id
 WHERE activity_types.name = 'photo';

Answer 2

, , .

, user_id datetime, , .

MySQL EXPLAIN (<query>), , .

Answer 3

• , . , , user_id (2,6,89 ..)

• type, , ,

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

• I would add an extra column called info, where I would save other information in json format. This applies to additional data that not all events have. For example, you might have an event to add a link to a profile ... and you can put a link there, as other events do not have URLs and adding a column only for this type of event will not be a good solution