-1

I have a goal to implement database schema for simple \ typical social network. I have read many threads \ answers but have couple open questions. So we have User table (userId, name and etc). We can make some Actions (reply, like, follow and etc). I want to implement some log for all activities and do it as PULL-MODEL. So we write entry in Activity table for any action. Schema for this table is (id, ownerId, actionType, targetId, time) where ownerId is User's id, who made action. actionType is reply, follow or other action. targetId is id of user or post and depends on actionType. When User get his activities we just do query by friends ids. So it is clear for me. My questions are:

1) In case if I follow User and unfollow him, what I should do? Should I make two entries in Activity table or I should remove the first followAction entry? What is the best practice?

2) It is clear foe me do query by friend ids so I get all activities of my friends. But in case any not my friend liked my photo and I must get event that "Some not my friends liked my photo". So, what are good solutions there for this case. May be I must to change my current schema?

Releated questions :

How to implement the activity stream in a social network

Database Design - "Push" Model, or Fan-out-on-write

What's the best manner of implementing a social activity stream?

Thanks you all for good answers.

Community
  • 1
  • 1
Ilya Demidov
  • 3,275
  • 2
  • 26
  • 38

1 Answers1

4

First, it may be better to split each kind of action into its own table, rather than having all actions in one table, distinguished by types. This makes your metadata about each action more flexible; as you say, the target ID depends on the action; without splitting them out into other tables, it's harder to write constraints on what the data should be.

Second - on your question #1, I think you're confusing a log of user actions with user status. You may need both; you might want two separate data structures. For example, if a user follows and then unfollows, the status is that they aren't following, but the log of actions is that they followed, then unfollowed. So I think you should be careful to have a separate data structure that captures current status of certain relationships, apart from actions. Then the problem becomes simpler, you log all actions as they happen, and update status accordingly.

For question #2, the photo should be its own data object, with "likes" split out into a different table; users like posts. Then of all of the users who like a post, they can easily be grouped into two categories; friends (those who have a friend relationship to the poster) and non-friends.

FrobberOfBits
  • 17,634
  • 4
  • 52
  • 86
  • Thanks for answer. So idea to separate actions looks interesting. What about question 1 - of course I have relations table but now my goal is implemet news stream. So if users A an B friends and B follow C, user A in his stream get news "B started following C", but if B unfollowed C, A should not get this entry. question 2 - if my friend liked post, in me news stream I get entry "User B liked post blahblah". All entries of my friends I can get by friend ids. But if not friend liked my post. How I can get this entry in my news stream? I am interested in sql query level. – Ilya Demidov Mar 01 '15 at 08:19
  • I can't give you SQL because you'd have to publish your whole model to do that. But you want to do a time-boxed query for a news feed. Each user has a "last newsfeed update time". New items on the newsfeed are anything that's happened since that last update time. If B follows C, that's timestamped in your actions table. If A is friends with C, then you update A on any action related to C within the time window. – FrobberOfBits Mar 01 '15 at 14:59