CODEWITHSUNDEEP
databasedatabase-designrelational-database
1110
1110

Reputation: 6829

What do you think about this scheme for activity stream

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

Upvotes: 0

Views: 84

Answers (1)

simon at rcl
simon at rcl

Reputation: 7344

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.

Upvotes: 1

Related Questions