Facebook 之类的通知跟踪(DB 设计)

发布于 2024-08-14 04:12:48 字数 358 浏览 6 评论 0原文

我只是想弄清楚 Facebook 的数据库是如何构建用于跟踪通知的。

我不会像 Facebook 那样深入讨论复杂性。如果我们想象一个简单的通知表结构:

notifications (id, userid, update, time);

我们可以使用以下方式获取朋友的通知:

SELECT `userid`, `update`, `time`
FROM `notifications`
WHERE `userid` IN 
(... query for getting friends...)

但是,应该使用什么表结构来查看哪些通知哪些已经读过,哪些还没有?

I am just trying to figure out how Facebook's database is structured for tracking notifications.

I won't go much into complexity like Facebook is. If we imagine a simple table structure for notificaitons:

notifications (id, userid, update, time);

We can get the notifications of friends using:

SELECT `userid`, `update`, `time`
FROM `notifications`
WHERE `userid` IN 
(... query for getting friends...)

However, what should be the table structure to check out which notifications have been read and which haven't?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(6

我为君王 2024-08-21 04:12:48

我不知道这是否是最好的方法,但由于我没有从其他人那里得到任何想法,这就是我要做的。我希望这个答案也能对其他人有所帮助。

我们有 2 个表,

notification
-----------------
id (pk)
userid
notification_type (for complexity like notifications for pictures, videos, apps etc.)
notification
time


notificationsRead
--------------------
id (pk) (i dont think this field is required, anyways)
lasttime_read
userid

想法是从通知表中选择通知并加入到 notificationRead 表中,然后检查上次读取的通知和 ID > 的行。通知ID。每次打开通知页面时都会更新 notificationRead 表中的行。

我想对未读通知的查询会是这样的。

SELECT `userid`, `notification`, `time` from `notifications` `notificationsRead`
WHERE 
`notifications`.`userid` IN ( ... query to get a list of friends ...) 
AND 
(`notifications`.`time` > (
    SELECT `notificationsRead`.`lasttime_read` FROM `notificationsRead` 
    WHERE `notificationsRead`.`userid` = ...$userid...
))

上面的查询没有被检查。
感谢@espais的db设计思想

I dont know if this is the best way to do this, but since I got no ideas from anyone else, this is what I would be doing. I hope this answer might help others as well.

We have 2 tables

notification
-----------------
id (pk)
userid
notification_type (for complexity like notifications for pictures, videos, apps etc.)
notification
time


notificationsRead
--------------------
id (pk) (i dont think this field is required, anyways)
lasttime_read
userid

The idea is to select notifications from notifications table and join the notificationsRead table and check the last read notification and rows with ID > notificationid. And each time the notifications page is opened update the row from notificationsRead table.

The query for unread notifications I guess would be like this..

SELECT `userid`, `notification`, `time` from `notifications` `notificationsRead`
WHERE 
`notifications`.`userid` IN ( ... query to get a list of friends ...) 
AND 
(`notifications`.`time` > (
    SELECT `notificationsRead`.`lasttime_read` FROM `notificationsRead` 
    WHERE `notificationsRead`.`userid` = ...$userid...
))

The query above is not checked.
Thanks to the idea of db design from @espais

楠木可依 2024-08-21 04:12:48

您可以添加另一个表...

tblUserNotificationStatus
-------------------------
- id (pk)
- notification_id
- user_id
- read_status (boolean)

如果您想保留历史记录,您可以保留 X 个最新通知并删除列表中早于最后一个通知的其余通知...

You could add another table...

tblUserNotificationStatus
-------------------------
- id (pk)
- notification_id
- user_id
- read_status (boolean)

If you wanted to keep a history, you could keep the X latest notifications and delete the rest that are older than your last notification in the list....

半步萧音过轻尘 2024-08-21 04:12:48

如果您在发出通知时发出了当时可用的所有相关通知,则可以通过将时间戳附加到可通知事件并跟踪每个用户上次收到通知的时间来简化此操作。但是,如果您处于多服务器环境中,则必须小心同步。请注意,这种方法不需要真正的日期时间戳,只需要单调增加的东西。

If, when you give notifications, you give all relevant notifications available at that time, you can make this simpler by attaching timestamps to notifiable events, and keeping track of when each user last received notifications. If you are in a multi-server environment, though, you do have to be careful about synchronization. Note that this approach doesn't require true date-time stamps, just something that increases monotonically.

魂ガ小子 2024-08-21 04:12:48

我发现这里没有人解决这样一个事实:通知通常会重复出现。即将进行的交易的通知始终是相同的,但其中包含不同的交易 ID 或日期。像这样:{您有一笔新的即将付款:@ paymentID,到期日为@ dueDate }。
将文本放在不同的表中也可以帮助

  1. 如果您想稍后更改通知文本
  2. 使应用程序多语言更容易,因为我可以使用语言代码对通知表进行分层并检索适当的字符串

因此我还制作了一个表对于那些抽象通知,它们只是通过中间表链接在用户下,其中一种通知类型可以多次发送给一个用户。我还不是通过外键 ID 将通知链接到用户,但我为所有通知创建了通知代码,并为这些代码的 varchar 字段建立了 full_text 索引,以提高读取速度。由于这些通知需要在特定时间发送,因此开发人员编写

NotificationService::sendNew( Notification::NOTE_NEW_PAYMENT, ['paymentId'] => 123, ['dueDate'] => Carbon::now(), 'userIdToSendTo' );

Now 也更容易,因为我的消息中将包含自定义数据,这些数据将插入到字符串中,正如您可以从提前第二个参数,然后我将它们存储在数据库 blob 中。 这是因为我想将通知与其他表解耦,因此

$values = base64_encode(serialize($valuesInTextArray));

我不想在通知表之间建立不必要的 FK 关系,这样我就可以说通知 234 附加到事务 23,然后加入和获取该交易 ID。将其解耦可以消除管理这些关系的开销。缺点是,几乎不可能删除通知,例如删除交易时,但在我的用例中,我决定,无论如何都不需要这样做。

我将在App端检索并填充文本,如下所示。诗。我正在使用某人的 vksprintf 函数(https://github.com/ WashingtonPost/datawrapper/blob/master/lib/utils/vksprintf.php),支持他!

$valuesToFillInString = unserialize(base64_decode($notification->values));
vksprintf( $notificationText->text, $valuesToFillInString )

另请注意我索引了哪些字段,因为我将按它们查找或排序

我的数据库设计如下

============================= ===

表:用户

  • id (pk)

================================

表:通知

  • id (pk)
  • user_id (fk ,索引)
  • text_id(fk-NotificationTexts表)
  • values(blob)[包含值数组,要输入到文本字符串中]
  • createdDateTime(DateTime)
  • read(boolean)

[ClusterIndex]=>; (user_id,createdDateTime)

================================

表:NotificationTexts

  • id(pk)
  • text_id(唯一索引)
  • text(varchar ) [{ 您有一笔新的即将付款:@ paymentID,到期日为 @dueDate }]
  • note (varchar,可为 null) [开发人员注释,信息列]

I see no-one here addresses the fact, that notifications are usually re-occurring, aka. notification of an upcoming transaction is always going to be the same, but with a different transaction ID or Date in it. as so: { You have a new upcoming payment: @paymentID, with a due date of @dueDate }.
Having texts in a different table can also help with

  1. If you want to change the notification text later on
  2. Making the app multilingual is easier, because I can just layer the notifications table with a language code and retrieve the appropriate string

Thus I also made a table for those abstract notifications, which are just linked under the the user with a middle table, where one notification type can be sent to one user at multiple times. I also linked the notifications to the user not by a foreign key ID, but I made notification codes for all notifications and full_text indexed the varchar field of those codes, for faster read speeds. Due to the fact that these notifications need to be sent at specific times, it is also easier for the developer to write

NotificationService::sendNew( Notification::NOTE_NEW_PAYMENT, ['paymentId'] => 123, ['dueDate'] => Carbon::now(), 'userIdToSendTo' );

Now since my messages are going to have custom data in them, that is inserted into the string, as you can see from the second argument beforehand, then I will store them in a database blob. as such

$values = base64_encode(serialize($valuesInTextArray));

This is because I want to decouple the notifications from other tables and as such I dont want to crete unnessecary FK relations from and to the notifications table, so that I can for example say notification 234 is attached to transaction 23 and then join and get that transaction ID. Decoupling this takes away the overhead of managing these relations. The downside is, it is nigh impossible to delete notifications, when for example a transaction is deleted, but in my use case I decided, this is not needed anyway.

I will retrieve and fill the texts on the App side as follows. Ps. I am using someones vksprintf function (https://github.com/washingtonpost/datawrapper/blob/master/lib/utils/vksprintf.php), props to him!

$valuesToFillInString = unserialize(base64_decode($notification->values));
vksprintf( $notificationText->text, $valuesToFillInString )

Notice also which fields I index, because I am going to find or sort by them

My Database design is as follows

==============================

TABLE: Users

  • id (pk)

==============================

TABLE: Notifications

  • id (pk)
  • user_id (fk, indexed)
  • text_id (fk - NotificationTexts table)
  • values (blob) [containing the array of values, to input into the text string]
  • createdDateTime (DateTime)
  • read (boolean)

[ClusterIndex] => (user_id, createdDateTime)

==============================

TABLE: NotificationTexts

  • id (pk)
  • text_id (uniquem indexed)
  • text (varchar) [{ You have a new upcoming payment: @paymentID, with a due date of @dueDate }]
  • note (varchar, nullable) [notes for developers, informational column]
开始看清了 2024-08-21 04:12:48

我也在试图弄清楚如何设计一个通知系统。关于通知状态(已读、未读、已删除、已存档等),我认为它是 ENUM 的一个很好的候选者。我认为除了 READ 和 UNREAD 之外,可能还会有两种以上不同类型的状态,例如已删除、已存档、已查看、已驳回等。

这将使您能够随着需求的变化而扩展。

另外,我认为有一个字段来存储操作 URL 或链接可能是有意义的(至少在我的情况下)。某些通知可能要求或提示用户点击链接。

如果您想要不同的类型,那么拥有通知类型也可能有意义。我认为可能会有系统通知(例如验证电子邮件通知)和用户提示通知(例如好友请求)。

我认为这是一个像样的通知系统的最低结构。

users
-------------
id
username
password
email

notifications
-------------
id
user_id (fk)
notification_type (enum)
notification_status (enum)
notification_action (link)
notification_text
date_created (timestamp)

I am also trying to figure out how to design a notification system. Regarding notification status (read, unread, deleted, archived, ect) I think that it would be good a good candidate to for ENUM. I think it is possible that there will be more than two different types of status other than READ and UNREAD such as deleted, archived, seen, dismissed, ect.

That will allow you to expand as your needs evolve.

Also I think it may make sense (at least in my case) to have a field to store an action url or a link. Some notifications could require or prompt the user to follow a link.

It also may make sense to have a notification type as well if you want different types. I am thinking there could be system notifications (such as a verify email notification) and user prompted notifications (such as a friend request).

Here is the structure I think would be a minimum to have a decent notification system.

users
-------------
id
username
password
email

notifications
-------------
id
user_id (fk)
notification_type (enum)
notification_status (enum)
notification_action (link)
notification_text
date_created (timestamp)
爱冒险 2024-08-21 04:12:48

表格如下

User

  • userId (Integer)
  • fullName(VarChar)

Notification

  • notificationId (Integer)
  • creationDate (Date)
  • notificationDetailUrl (VarChar)
  • isRead (bollean)
  • description (VarChar)
  • userId (FK )

Table are following

User

  • userId (Integer)
  • fullName(VarChar)

Notification

  • notificationId (Integer)
  • creationDate (Date)
  • notificationDetailUrl (VarChar)
  • isRead (bollean)
  • description (VarChar)
  • userId (F.K)
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文