Facebook喜欢通知跟踪（DB Design） [英] Facebook like notifications tracking (DB Design)

问题描述

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

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

通知（id，userid，update，time）; / p>

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

  SELECT`userid `，`update`，`time` 
 FROM`notifications` 
 WHERE`userid` IN 
（...查询获取朋友...）
  

但是，应该使用哪些表格结构来查看哪些通知已被读取，哪些通知没有？

解决方案

我不知道这是否是最好的方法，但是由于我没有任何其他人的想法，所以我会做的。我希望这个答案也可以帮助别人。

我们有2个表

 通知
 ----------------- 
 id（pk）
 userid 
通知类型（用于图像通知的复杂性，视频，应用程序等）
通知
时间
 
 
 notificationRead 
 ---------------- ---- 
 id（pk）（我不认为这个字段是必需的，无论如何）
 lasttime_read 
 userid 
  

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

对于未读通知的查询，我猜想会是这样..

  SELECT`userid`，`notification`，``````````````````````
 WHERE 
`notifications``` userid` IN（...查询以获取一个朋友列表...）
 AND 
（`notifications`.`time`>（
 SELECT`notificationsRead`.`lasttime_read` FROM`notificationsRead` 
 WHERE`notificationsRead`.`userid` = ... $ userid ... 
））
  

上面的查询未被选中。
感谢@espais

的db设计思想

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?

解决方案

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

这篇关于Facebook喜欢通知跟踪（DB Design）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！