Facebook喜欢通知跟踪（数据库设计）触发器还是T-SQL？ [英] Facebook like notifications tracking (DB Design) Triggers or T-SQL?
问题描述
我只想弄清楚facebook的数据库是如何构建用于跟踪通知的。
不要像facebook那样复杂化。如果我们想象一个简单的表结构用于通知
通知（id，用户ID，更新，时间）;
我们可以使用
I am just trying to figure out how facebook''s database is structured for tracking notifications.
Wont 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...)
然而应该是什么表格结构来检查哪些通知已被阅读，哪些通知没有？
我不知道这是否是最好的方法，但由于我没有得到任何人的想法，这就是我要做的。
我们有2张桌子
However what should be the table structure to check out which notifications has been read and which hasn''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.
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
想法是选择通知从通知表中加入notificationsRead表并检查上次读取的通知和ID> gt的行。 notificationid。每次打开通知页面时都会更新notificationsRead表中的行。
未读通知的查询我想是这样的..
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...
))
不检查上面的ery。
现在我的理解是，当添加通知时，我们需要找到用户朋友并插入所有这些行（每个朋友的通知） ）在通知表中对吗？如果这是正确的，那么，实现这个目标的最佳方法是什么？
触发器？
编写T-SQL（sql查询）在服务器端）选择所有朋友，然后使用SQL批量复制？
The query above is not checked.
Now my understanding is that when a notification is added, we need to find the users friends and insert all those rows(a notification per friend) in the notification table right ? If this is correct, then, what would be the best way to achieve this ?
Triggers?
Write T-SQL (sql query in server side) to Select all the friends and then use SQL bulk copy?
推荐答案
userid ...
））
userid... ))
未检查上述查询。
现在我的理解是在添加通知时，我们需要找到用户朋友并在通知表中插入所有这些行（每位朋友的通知）吗？如果这是正确的，那么，实现这个目标的最佳方法是什么？
触发器？
编写T-SQL（sql查询）在服务器方面）选择所有朋友然后使用SQL批量复制？
The query above is not checked.
Now my understanding is that when a notification is added, we need to find the users friends and insert all those rows(a notification per friend) in the notification table right ? If this is correct, then, what would be the best way to achieve this ?
Triggers?
Write T-SQL (sql query in server side) to Select all the friends and then use SQL bulk copy?
首先，我认为facebook是基于NoSQL数据库架构......但是......
我要做的是让每个用户的通知序列都有一个顺序的notificationId。然后，如果A订阅或跟随或结束B然后在订阅链接中，记录上次读取的通知是什么，并显示更大的内容。
这样你只是在B'的通知序列中跟踪A到达的位置，而不是每个通知的记录。
如果C也跟随B，那么起点很容易就是设置为当前的notificationId，所以所有的历史记录都没有出现。
这有意义，有帮助吗？
Firstly, I think facebook is based on a NoSQL database architecture... But...
What I would do is have each users notification sequence have a sequential notificationId. Then if A is subscribed to or following or friended B then in the subscription link, record what the last read notificationId is and present everything greater.
This way you are only tracking where A is up to in B''s notification sequence, not a record for every notification.
Should C also follow B, then the start point can easily be set to the current notificationId, so all the history doesn''t appear.
Does this make sense, and help?
这篇关于Facebook喜欢通知跟踪（数据库设计）触发器还是T-SQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
