问题描述
我刚刚阅读了有关同一主题的以下帖子:
I've just read the following posts on the same topic:
类似于Facebook的通知跟踪(数据库设计)和用于向用户存储通知的数据库设计
提供了一些解决方案，但未完全满足我的需求，以及对于类似Facebook的通知系统的需求.
Some solutions were offered but not exactly what I need and how it should be for a Facebook-like notification system.
在通知中，我们经常有一些链接指向用户采取了某些操作，链接到他发表评论的帖子或视频，链接到任何内容，并且在单个通知中还有多个链接.
In a notification, we often have some links pointing to the user who took some action, link to a post or video he commented on, link to anything, and ofter we have several links in a single notification.
notification
-----------------
id (pk)
userid
notification_type
notification_text
timestamp
last_read
使用此表结构，我们可以显示单个用户的所有通知，这是非常可靠的解决方案.但是在这种情况下，我们只能显示纯文本通知.我们不能简单地链接到用户或墙上的帖子.
With this table structure we can show all notifications for a single user and it is pretty solid solution. But in this case, we can only display a plain text notification. We cannot simply link to a user or a wall post.
我正在尝试解决此问题.一种是将BB代码存储在notification_text属性中，但是随后您需要为Web和移动应用程序编写BB代码解析器.另一个方法是创建另一个表，该表从此通知表派生而来，其中包含我们所需实体的ID.一个例子:
I'm trying to come up with a solution to this problem. One is to store BB Codes in notification_text property, but then you need to write BB code parser both for web and mobile applications. Another would be creating another table which derives from this notification table with ID's for entities we need. An example:
PostCommentNotification : Notification
----------------------------------------
id
userId (user who commented on a wall post)
postId (post where comment was made)
现在，我们可以编写一个用于显示通知的模板(现在不再需要通知表中的text属性)，然后处理显示它.我也不满意此解决方案，因为从通知表派生的表数量可能很大(对于每种通知类型).
Now we can write a template for displaying the notification (we don't need the text property in notifications table anymore now) and then handle displaying it. I'm not satisfied with this solution either since the number of tables deriving from notification table could be big (for each notification type).
我正在寻找想法！:)
推荐答案
不幸的是，这里没有很多答案.我遇到了同样的问题，没有找到任何好的解决方案.数据库中的继承总是棘手的，并且变得太复杂了.因此，我最终得到了一个简单的解决方案:将以JSON包装的资源的键值数组存储在数据"列中.
Unfortunately not a lot of answers here. I had the same problem and didn't find any good solution. Inheritance in databases are always tricky and get complex too quickly.So I ended up with a simple solution: storing a key value array of resources wrapped in JSON in a "data" column.
类似这样的东西:
notification
-----------------
id (pk)
userid
notification_type
notification_data
timestamp
last_read
例如，对于评论通知，您将存储
For example, for a comment notification, you'll store
[{"author_id": "1234", "comment_id":"1234"}]
然后，您可以使用notification_type在客户端正确格式化数据.
Then you use the notification_type to format correctly your data on client-side.
看不到任何弊端，因为我们只需要客户端上的资源id即可创建url或intent，拥有用于索引的原子字段是没有用的.
Can't see any drawbacks as we only need resources id on client to create urls or intents, having atomic fields for indexes is useless.
希望这会有所帮助.
这篇关于跟踪用户通知或活动的数据库(类似于Facebook)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！