What is the algorithm to implement "Follow - User" feature with PHP - MySQL?

A very simple table structure would be two tables one called user and another called follows. The user table would store your normal user data with say a PK of user_id The follows table would have two columns, user_id and follow_id, both should be FKs to user.user_id Then you'd have relationships between a user and who they follow. There is no algorithm for this it's just related tables. If you through in concepts such as categories, groups, permissions then you complicate it but it'd still pretty simple and follows the normal user -> group -> permissions model. In terms of updates, you just look for the posts of everyone that person has in the follows table then look for the posts. Depending on scale and architecture depends on how you would then query out the data, but in a small scale system where you could query it with joins in one statement you could then use something like ... SELECT p.* FROM user AS u INNER JOIN follow AS f ON f.user_id = u.user_id INNER JOIN user as u2 ON u2.user_id = f.follow_id INNER JOIN post AS p ON p.user_id = u2.user_id WHERE u.user_id = ?? ORDER BY p.created_on DESC LIMIT 10 to return the latest 10 posts by everyone who the user identified by the user_id ?? follows

Without going into too much detail, this can be easily achieved by following these steps: a) Define entities (brands, users, whatever) b) Define update types they can do (new product, status update, blog post, image, whatever) c) Fetch all updates for an entity and cache the result d) Save a flag "new_stuff" somewhere as FALSE e) When entity makes an update, set new_stuff to TRUE f) When a user looks at his stream or notifications screen or what have you, initiate a request to all the entities he follows. Pull and pool all cached content, serve to user. If an entity has new stuff on TRUE, pull content, cache it, combine with new_stuff=false content and serve everything to user. Obviously, this is quite dumbed down and there's loads more that could be done, but generally this is how many sites do it.

This type of question will require you to plan for performance when a given brand or user followers grows to say over 100,000. You might want to build a system that not only allow people to follow brands or users, but also you have to take care of filtering. Yes not all type of notification will people want, you have to have a way to filter out those that had actually subscribed to be notified if certain event happens and those that though they are following the brand or user, but had decided not to receive certain type of notification. Facebook uses some sort of association system to handle this. Implementing such system i probably will : Create a storage for all the users of the system Create a storage for the followers, this might either be a complete table which builds a relation between the followers and the followed Create a preference table for each of the users and store information about their notification preferences When an event happens, load up all the followers as well as their preferences and filter out only those that want to receive notification for the event With the list narrowed down, for me i would further filter this lists by checking for users from the list who had been active on the site perhaps for the last 30 days, these will be my first target set with the notification.

Is it necessary to use mysql as the backend? I would suggest using redis as the backend. Redis comes with a built in publisher subscriber model which can be easily used to create a 'follow user' feature similar to twitter. The redis website gives an.example as well. http://redis.io/topics/twitter-clone This example uses some of the inbuilt data structures in redis like sets and lists to build the 'follow user' example in php. The example also explains how this can be scaled horizontally to multiple servers. The key over here is performance. With mysql as a data store the task is achievable but the performance required to deal with huge list of followers may not be achieved. Redis, on the other hand provides inbuilt data structures with atomic operations on them which can be used to deal with such issues and provide a better solution to the problem. my $0.02

Suppose you have a user table with user_id as the primary key , then you can define a table in the following manner :- id    | user_id            | follower --------------------------------------------   1  |    someid1        |  someid2 While declaring this table a foreign key constraint should be placed on user_id , follower , where both these fields will be foreign key's to user_id field in the user table .