使用 PHP 和 MySQL 的通知/新闻提要
我一直在开发一个复杂的 PHP 系统，该系统融合了社交网络思想中的元素，但针对的是封闭的人群。我有一些模块、照片和视频库、每个模块和子模块的完整评论系统、私人消息、带有 GUI 的个人电子邮件等等。
我的问题是，无论我多么努力，我似乎都无法以高效的方式设计通知和新闻提要部分的后端，就像 facebook 那样。这些模块几乎都是事件驱动的，因此将它们连接到通知系统应该不成问题。希望一些集体头脑风暴能够解决我的问题。
我只会在这篇文章中解决我对通知部分的担忧（如果我也包含新闻提要，它将成为一篇非常长且混乱的文章）。
这是我的第一个 MySQL 表草稿。
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
可能的问题/冲突
- 用户对两个不同用户已经评论过的照片进行评论的事件会触发三个通知吗？一张给照片上传者，一张给每个评论者？ （影响notificationUser）
- notificationTime 是创建时间。出现上述问题后，我们是否应该创建一个新的通知，该字段应替换为 notificationUpdateTime 或者可能存在于其旁边？
我的目标是降低代码复杂性和提高数据库效率。尝试将数据库层与代码层分开让我对网站的这一部分感到困惑:(
我对所有问题和想法持开放态度。
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 pretty 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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
有两种方法可以处理新闻源：
您正在使用写入时扇出方法。在这种情况下，您将为每个相关用户生成一个新的单独活动。
这种方法可能很快就会失败，特别是如果您允许用户拥有无限数量的关注者。每当发生频繁的活动时，您就必须为数据库中的每 1000 多个关注者保存一个新条目。
当您添加群组和不同类型的受众时，事情会变得更加复杂。如果您现在只想将活动广播给 A 组中的人员而不是 B 组中的人员（例如，您创建了一个状态并且想要限制观看者），该怎么办？如果您想要向 A 组和 B 组广播，但 A 组和 B 组中都有用户，您不希望两次接收相同的活动，会发生什么情况？如果您想在创建活动后更改活动的可见性该怎么办？
对于写方法中的扇出来说，这实际上是不可能的，或者至少非常困难。这就是为什么我更喜欢后者——扇出阅读方法。
考虑一下：
用户有一组与其 ID 相关联的新闻源频道。这些通道的格式为
{{ object_name}}:{{ object_id }}。您可以为新闻源和通知设置单独的集合，以便您可以取消某个对象的通知，而无需将其从新闻源中删除。当用户注册以接收来自对象的更新时，他们会将与该对象关联的频道添加到其频道列表中。您可以使用简单的 Redis 集。例如，如果我加入事件 #1，我会将
event:1添加到我的频道列表中。当事件#1 上的属性发生更改时，新活动仅创建一次。此活动有一个名为“观察者”的字段，它是其可见的通道的名称。在本例中，观察者是“event:1”。
当用户拉动他们的活动源时，他们首先获得他们订阅的频道的列表。然后，他们检索观察者在频道列表中的所有活动源项目。
另外
我只是不太确定如何将其变成通知系统。使用读取时扇出方法，我有一组通知通道，如上所述。当对象的属性发生更改时，我会向数据库写入一条新通知，所有订阅的用户都会收到该通知。唯一的问题是：我如何逐个阅读通知？我还没弄清楚那部分。
There are two ways to approach a newsfeed:
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:1to 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.
经过几天的休息，我改变了对新闻源和通知系统的思考方式。我采用的方法是，每个事件（评论、标签等）都会触发有关两个用户的单个通知，即通知所针对的参与者和用户（例如，一个标记和一个被标记的用户）。另一部分的新闻提要将从通知条目中获取合理的最近条目（假设其不是私人的，例如私人消息）并构建提要。这似乎会给数据库带来压力，但我认为对于 100 个（最多）用户的情况来说这不会是问题。感谢您的投入，瑞安:)
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 :)