Notification / News Feed with PHP and MySQL

Question

I am working on a complex PHP system that includes elements from the idea of ​​social networks, but for a closed group of people. In place I have several modules, photo and video galleries, a complete comment system on each module and submodule, private messages, personal letters with a graphical interface and much more.

My problem is that no matter how hard I try, I can not create a backend for some notifications and news, something like what is on facebook, in an effective way. Modules are largely event driven, so connecting them to the notification system should not be a problem. Hope group brainstorming puts an end to my problem.

I will only consider my problems regarding notification in this post (this will become a very long and chaotic mail if I also include it in the news feed).

Here is my first MySQL table project.

  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 that two different users have already commented will trigger three notifications? One for the photo downloader and one for each of the commentators? (affects user notification )
  • notification time - creation time. Following the above problem, should we not create a new notification, should this field be replaced with notificationUpdateTime or perhaps exist away from it?

I try to reduce code complexity and improve database performance. Trying to split the database layer with the code layer puzzled me in this part of the site :(

I am open to all problems and ideas.

Answer 1

There are two ways to get closer to the news feed:

• disable recording
• disable viewing

You are using a write-based fan. In this case, you create a new individual activity for each relevant user.

This approach can explode very quickly, especially if you allow users an unlimited number of subscribers. Whenever there is an action that is frequent, you need to save a new record for every 1000+ followers in the database.

It gets harder when you add groups and different types of audiences. What if now you want to transfer information only to people from group A, but not to group B (for example, you create a status and want to restrict viewing)? What happens when you want to broadcast group A and group B, but are there users in A and B that you do not want to receive the same action twice? What if you want to change the visibility of an activity after creating it?

In fact, this is not possible if the fan is working on a recording basis or at least very difficult. That's why I prefer the latter - a reading-based fan.

Consider this:

The user has a set of news feeds associated with their identifier. These channels are of the form {{ object_name}}:{{ object_id }} . You can have separate sets for news and notifications, so you can cancel notifications on one object without removing it from your news feed.

When a user registers to receive updates from an object, he adds the channel associated with the object to his channel list. You can use a simple set of Redis. For example, if I join event # 1, I add event:1 to the channel list.

When an attribute changes to event # 1, a new action is created only once. This action has a field called an “observer,” which is the name of the channel on which it is seen. In this case, the observer is "event: 1".

When a user retrieves their activity feed, they first get a list of their subscription channels. Then they retrieve all the elements of the activity feed where the observer is in the channel list.

also

I'm just not sure how to turn this into a notification system. Using the “turn off the fan” method, I have a set of channels for notifications, as described above. When the attribute of an object changes, I write a new notification to the database, which all subscribers sign. The only problem is this: how would I read the notifications for each user? I still do not understand this part.

Answer 2

After a few days off, I changed my mindset both in the news feed and in the notification system. I take the approach where each event (comment, tag, etc.) Raises one notification about two users, the actor and the user the notification is aimed at (for example, one tag and those that are marked). In the news feed, on the other hand, reasonable last entries from the notification entries will be taken (provided that this is not a private private message), and create a feed. It might seem database-based, but I don't think it would be a problem for the case of 100 (maximum) users. Thanks for entering Ryan :)