群组通知系统数据库设计
我正在尝试创建一个群组通知系统。如果我在一个群组中，那么任何在群组墙上发表评论的人都会向每个群组成员发送一条通知。这是我的数据库设计：我有两个表：Notification 和 NotificationRead。
通知已读
+用户ID（字符串）
+lastRead (int) - 默认为 0
通知
...
+时间（整数）
...
每个用户在 NotificationRead 中都有一个条目，它会跟踪我最后一次阅读通知的时间。 逻辑是：对于特定用户，如果 Notification.time > > NotificationRead.lastRead，则该通知被视为未读。假设在A组中，有4个通知我没有读过，它们的时间是7,8,9,10，那么当我点击group A时，我设置了NotificationRead.lastRead = 10（最大时间），所以我不会再读它们。新通知的时间将从 11 点开始。现在，这是我的问题。假设我有 3 个组，A、B 和 C
A (4)：最大时间是 10
B(1)：最大时间为14
最大时间是12
C（1）：如果我点击A， ，我的NotificationRead.lastRead = 10，A旁边的4清除, B 旁边的 1 和 C 保持原样。现在，如果我点击 B，我的 lastRead 现在是 14，因此不仅会清除 B 旁边的 1，还会清除旁边的 1从14>开始到C 12..谁能帮我想个办法解决这个问题。我愿意彻底重新设计一切
I am trying to create a group notification system. If I am in a group, then anyone who comment on the group's wall, a notification will send out to every group member. Here is my database design: I have two tables: Notification and NotificationRead.
NotificationRead
+userId (String)
+lastRead (int) - default is 0
Notification
...
+time(int)
...
Every user has one entry in NotificationRead, it keep track of when is the last time I read my notification.
The logic is: for a particular user, if Notification.time > NotificationRead.lastRead, then that notification is considered unread. Let say that in group A, there are 4 notifications I have not read, and their time is 7, 8, 9, 10, then when I click onto group A, I set my NotificationRead.lastRead = 10 (the largest time), so I wont read them again. New notifications will have their time start at 11. Now, here is my problem. Let say I have 3 groups, A, B and C
A (4): largest time is 10
B (1): largest time is 14
C (1): largest time is 12
if I click onto A, my NotificationRead.lastRead = 10, the 4 next to A clear off, 1 next to B and C stay put. Now if I click on B, my lastRead now is 14, so not only it clear off the 1 next to B but also the 1 next to C since 14 > 12. Can anyone help me think of a way to solve this. I am open to completely redesign everything
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您不能只在NotificationRead 表中添加一个groupID 列，以便了解每个User\Group 组合的lastRead 值吗？
Cant you just add a groupID column to your NotificationRead table so you know the lastRead value for each User\Group combination>?
如果您想知道每个组每个用户的最后通知时间，则必须存储该信息。因此，每个用户在NotificationRead中必须有不止一条记录，它必须成为与用户表分开的一张表。该表将包含三列：user_id、group_id 以及该用户/组的最后读取值。
If you wish to know the last notification time per user per group, you must store that information. Therefore, each user must have more than one record in NotificationRead, which must become a separate table from the user table. This table will have three columns, the user_id, the group_id, and the lastread value for that user/group.