Notification/News feed with PHP and MySQL

I have been working on a complex PHP system that incorporates elements from the idea of Social networking but for a closed group of people. In place I have a few modules, photo and video galleries, complete commenting system on every module and submodule, private messaging, personal emails with a GUI and many more.

My problem is that no matter how hard I try I cant seem to design the backend for the notification and news feed part, something like what facebook has, in an efficient way. The modules are prett开发者_StackOverflowy much event driven so connecting them to the notification system shouldn't be a problem. Hopefully some group brainstorming will give an end to my problem.

I will only be addressing my concerns on the notification part in this post (it'll become a very long and chaotic post should I include the news feed as well).

Here is my first MySQL table draft.

notificationID
Primary key

notificationModule
Module type foreign key - photo, video, comment, message

notificationConstructor
Foreign key of the element (which is of type "notificationModule") that triggered the creation of this notification

notificationUser
The user that this notification is aimed towards

notificationTime
Time at which the notification was created

notificationFlag
Notification has been read flag

Possible Problems / Conflicts

• The event of a user commenting on a photo two different users have already commented on will trigger three notifications? One for the photo uploader and one for each of the commenters? (affecting notificationUser)
  • notificationTime is the creation time. Following the above issue, should we not create a new notification this field should be replaced by notificationUpdateTime or maybe exist aside it?

I'm aiming towards less code complexity and more database efficiency. Trying to separate the database layer from the code layer puzzled me on this part of the site :(

I'm open to all concerns and ideas.

There are two ways to approach a newsfeed:

• fan out on write
• fan out on read

You are using the fan out on write approach. In this case, you generate a new individual activity for each relevant user.

This approach can blow up very quickly, especially if you allow users to have unlimited numbers of followers. Whenever an activity occurs, which is frequent, you will have to save a new entry for every 1000+ followers in the database.

Things get more complicated when you add groups and different types of audiences. What if you now want to only broadcast the activity to people in group A but not group B (e.g. you create a status and you want to restrict the viewers)? What happens when you want to broadcast to group A and group B, but there are users in both A and B that you don't want receiving the same activity twice? What if you want to change the visibility of the activity after it is created?

This isn't really possible with a fan out on write approach, or at least very difficult. This is why I prefer the latter - a fan out on read approach.

Consider this:

A user has a set of newsfeed channels associated with their id. These channels are in the form {{ object_name}}:{{ object_id }}. You can have separate sets for newsfeed and notifications, so that you can cancel notifications on one object without removing it from your newsfeed.

When a user registers to receive updates from an object, they are adding the channel associated with the object to their list of channels. You can use a simple Redis set. For example, if I join event #1, I add event:1 to my list of channels.

When an attribute changes on event #1, a new activity is created only once. This activity has a field called 'observer' that is the name of the channel that it is visible to. In this case, the observer is 'event:1`.

When a user pulls their activity feed, they first get the list of their subscribed channels. Then, they retrieve all activity feed items where the observer is in their list of channels.

ALSO

I am just not really sure how to turn this into a notifications system. Using the fan out on read approach, I have a set of channels for notifications, as described above. When an attribute on an object changes, I write a new notification to the database that all subscribed users pick up. The only problem is: how would I read the notifications on a user-by-user basis? I still haven't figured that part out.

After a few days break I have changed my way on thinking on both the news feed and the notification system. I'm taking the approach where every event (comment, tag etc.) trigers a single notification concerning two users, the actor and the user the notification is aimed at (e.g the one tagging and the one being tagged). The news feed on the other part will take a reasonable recent entries from the notification entries (assuming its not private e.g private message) and build a feed. This might seem stressing on the database but I don't think it will be a problem on a case with 100 (max) users. Thanks for the input Ryan :)