Facebook 点赞通知跟踪(DB 设计)

2021-11-20 00:00:00 mysql database-design data-modeling

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

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:

通知(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?

推荐答案

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

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.

我们有两张桌子

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

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

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

上面的查询没有被检查.感谢@espais 的数据库设计理念

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

相关文章