MySQL Facebook 点赞通知跟踪（DB 设计）

Facebook like notifications tracking (DB Design)

提问by Atif Mohammed Ameenuddin

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

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

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

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

notifications (id, userid, update, time);

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?

但是，检查哪些通知已被读取，哪些尚未读取的表结构应该是什么？

采纳答案by Atif Mohammed Ameenuddin

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

我们有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

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.

这个想法是从通知表中选择通知并加入通知读取表并检查最后读取的通知和 ID > 通知 ID 的行。每次打开通知页面时，都会更新通知读取表中的行。

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

不检查上面的查询。感谢@espais 的数据库设计理念

回答by the_e

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....

如果您想保留历史记录，您可以保留 X 条最新通知并删除列表中比上次通知旧的其余通知....

回答by Joe Mabel

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.

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

回答by Karl Johan Vallner

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

我看到这里没有人解决这样一个事实，即通知通常会再次发生，也就是。即将到来的交易的通知总是相同的，但其中包含不同的交易 ID 或日期。因此：{您有一笔新的即将付款：@paymentID，到期日为@dueDate }。将文本放在不同的表格中也有助于

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

1. 如果您想稍后更改通知文本
2. 使应用程序多语言更容易，因为我只需将通知表与语言代码分层并检索适当的字符串

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

因此，我还为那些抽象通知制作了一个表格，这些通知只是在用户下方与一个中间表格链接，其中一种通知类型可以多次发送给一个用户。我还没有通过外键 ID 将通知链接到用户，但我为所有通知制作了通知代码，并且 full_text 索引了这些代码的 varchar 字段，以提高读取速度。由于这些通知需要在特定时间发送，因此开发人员编写起来也更容易

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

现在，由于我的消息中将包含自定义数据，即插入字符串中，正如您事先从第二个参数中看到的那样，然后我将它们存储在数据库 blob 中。像这样

$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.

这是因为我想将通知与其他表分离，因此我不想在通知表之间建立不必要的 FK 关系，例如，我可以说通知 234 附加到事务 23，然后加入并获取交易标识。解耦消除了管理这些关系的开销。缺点是，删除通知几乎是不可能的，例如当一个事务被删除时，但在我的用例中我决定，这无论如何都不需要。

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!

我将在 App 端检索并填写文本，如下所示。附言。我正在使用某人的 vksprintf 函数（https://github.com/washingtonpost/datawrapper/blob/master/lib/utils/vksprintf.php），给他道具！

$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)

• 身 (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)

• 身 (pk)
• user_id（fk，索引）
• text_id（fk - NotificationTexts 表）
• values (blob) [包含值的数组，输入到文本字符串中]
• 创建日期时间（日期时间）
• 读（布尔）

[ClusterIndex] => (user_id, createdDateTime)

[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]

• 身 (pk)
• text_id（唯一索引）
• text (varchar) [{ 您有一笔新的即将付款：@paymentID，到期日为 @dueDate }]
• note (varchar, nullable) [开发人员注意事项，信息栏]

回答by Radmation

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.

我也在想办法设计一个通知系统。关于通知状态（已读、未读、删除、存档等），我认为ENUM是一个很好的候选者。我认为除了 READ 和 UNREAD 之外，可能会有两种以上不同类型的状态，例如已删除、已存档、已查看、已关闭等。

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.

此外，我认为（至少在我的情况下）有一个字段来存储操作 url 或链接可能是有意义的。某些通知可能需要或提示用户点击链接。

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)

回答by Yasir Shabbir Choudhary

Table are following

表如下

User

用户

• userId (Integer)
• fullName(VarChar)

• 用户 ID（整数）
• 全名（VarChar）

Notification

通知

• notificationId (Integer)
• creationDate (Date)
• notificationDetailUrl (VarChar)
• isRead (bollean)
• description (VarChar)
• userId (F.K)

• 通知 ID（整数）
• 创建日期（日期）
• notificationDetailUrl (VarChar)
• isRead (bollean)
• 描述 (VarChar)
• 用户 ID (FK)