0

I am building a small social network where user can connect with other users and like in all other social networks I need activities stream.
I saw some similar questions but I am not sure about others solutions.
So I came up with something like this:

Activity { UserId, ToUserId, ActivityTypeId, ActivityDate, IsRead, ItemId }

UserId - user that generated activity,
ToUserId - user that should see this activity,
ActivityTypeId - new connection, photo, comment etc.,
ActivityDate,
IsRead
ItemId - based on type I know from which table I need to get data (users, comment, photo etc.)

ActivityType { ActivityTypeId, Text}

Text - e.g. Has new connection, Added new comment to, Edited his profile data etc.

When my connection for example get a new connection I should get:

Your connection [fetch name based on UserId] Has a new connection [fetch data (by ItemId) from table user]

or

Your connection [fetch name based on UserId] Added new comment to [fetch data (by ItemId) from table (for example) Photo]

My questions:
Is this bad design and why?
Is there better way?

UPDATE:

When user made a new activity I fetch all his connections ID's and for each of them I add that activity in Activity table.



Or this answer is the better option?
How to implement the activity stream in a social network

Community
  • 1
  • 1
1110
  • 7,829
  • 55
  • 176
  • 334

1 Answers1

1

As your database and users grow, adding a connection will take longer and longer as there are more activities to generate for the user with the new connection. Also, what happens if they break the connection (un-friend)? They should all be deteted, which could be many many rows of data.

Maybe it would be better would be to have a connection type and use that instead of ToUserId in the Activity table. Then when someone makes a connection of a certain type there is no new data to generate (or delete when they unfriend each other) except one row in a Connection table (FromUserId, ToUserId, ConnectionTypeId).

I'm not saying that this is better, just something to think about.

simon at rcl
  • 7,326
  • 1
  • 17
  • 24
  • I didn't think about that. I saw this question and what is your opinion on accepted answer there: http://stackoverflow.com/questions/1443960/how-to-implement-the-activity-stream-in-a-social-network?lq=1 – 1110 Jan 03 '14 at 15:38
  • If that's the answer at the top (with about 79 up-votes) then it looks like he's doing something similar to what I said - he doesn't have a ToUserId, and it looks like there will only ever be one row representing that Activity rather than one per person it should be shown to. How he's controlling who sees it is via what looks like a Friends table, similar to what I suggested, although I went a bit further and suggested a Friend-Type which may be overkill. – simon at rcl Jan 03 '14 at 15:44