Database design and SQL query for activity feed?

I want to create an activity feed similar to facebook. Before asking my question, I want to explain my database design mySQL. I have a user table, i.e.

user_id     name     other columns
userA       -           -
userB       -           -
userC       -           -
userD       -           -

Users can follow each other, and here is a table called "followers"

id      user_id         follower_id
1        userA            userB
2        userD            userB
3        userB            userA
-
-
-

As we can see, in the above table, user_B follows userA and userD.

Now my question is that when ANY user in user_id, which userB obeys, performs an action, should be notified. Should I make another table, say, “activity”, something like:

user_id     activity_type   activity_link  activity_date  
userA           note
userB           photo
userC           note
userD           photo

Question 1: Do I need to make an additional table (shown above) to store actions, or can this be done without it?

2: userB userA userD, userB , , SQL- ? sth like:

 Select * from activity where user_id exists in user_id in followers table where follower_id = userB 

( , SQL-)

, .

+3
2

, . .

- . , , .

select * from activity a
    join followers f on a.user_id = f.user_id
where follower_id = 'userB' 
+3

, . , , .

, , new_activity. :

follower_id int
activity_id int

, PK.

activity. "Triggered... triggered... aha, , , activity !". . , , follower/activity, new_activity.

, , followers . user/follower, activity , , , , , ( -) .

, , .. , . , . , GetNewActivity (@follower_id int), new_activity , , new_activity, . , . , GetNewActivity, , .

, . , - , , , , . , .:)

№2, ... , : , (). , , , , , .

, , , , , ar , , . , , , : , .

+1

Source: https://habr.com/ru/post/1790151/


All Articles