如何为每个用户的未读项目警报构建数据库 [英] How to structure database for unread item alerts per user

问题描述

我只是有一个一般性的数据库理论问题.我需要做一些类似于显示用户查看过或未查看过的帖子/项目(例如在论坛中)或未读电子邮件的操作.我所拥有的是多个用户可以查看的帖子，但是需要按实际查看过的用户分开.因此，如果用户A查看了帖子1，它将不再显示帖子1是要查看的新项目，而是向用户B仍然显示帖子1是要查看的新项目.

I just have a general database theory question. I have a need to make something similar to showing what posts/items a user has viewed or not (such as in a forum) or an unread email message. What I have is there are posts that multiple users can view, but it needs to separate by user who has actually viewed it. So if User A viewed Post 1, it would no longer show that Post 1 is a new item to view, but to User B, it would still show that Post 1 is a new item to view.

我正在搜索其他想法，其中之一是获取用户上次登录时间的时间戳，但实际上我需要跟踪他们所看到的帖子，而不是自他们以来发生过的帖子上次登录.

I've search for other ideas and one of them is to get a timestamp of when the user last logged in, but I actually need to keep track of the posts they've seen as opposed to posts that have happened since they last logged in.

如果可能的话，我想要一个MySQL数据库解决方案，但是如果必须的话，我会接受cookie.我可以自己做，只想弄清楚，但是对于任何有关如何正确构造表以使其最有效的建议，我将不胜感激.另外，带宽和存储也不成问题.

I would like a MySQL database solution if possible, but I'm open to cookies if that is a must. I could do this on my own and just figure it out, but I'd appreciate any advice on how to properly structure a table(s) to make this the most efficient. Also, bandwidth and storage is not issue.

推荐答案

同时回顾

While reviewing the relevant schema for phpBB, I found the following:

# Table: 'phpbb_topics_track'
CREATE TABLE phpbb_topics_track (
    user_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
    topic_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
    forum_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
    mark_time int(11) UNSIGNED DEFAULT '0' NOT NULL,
    PRIMARY KEY (user_id, topic_id),
    KEY topic_id (topic_id),
    KEY forum_id (forum_id)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

并且:

# Table: 'phpbb_forums_track'
CREATE TABLE phpbb_forums_track (
    user_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
    forum_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
    mark_time int(11) UNSIGNED DEFAULT '0' NOT NULL,
    PRIMARY KEY (user_id, forum_id)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

然后我在其Wiki中查看此处:

此表保留访问主题的记录，以将其标记为已读或未读.我们将mark_time时间戳与主题x时间戳的最后一帖，以了解主题x是否被读取.

This table keeps record for visited topics in order to mark them as read or unread. We use the mark_time timestamp in conjunction with last post of topic x's timestamp to know if topic x is read or not.

为了准确判断主题是否被阅读，还必须检查phpbb_forums_track.

In order to accurately tell whether a topic is read, one has to also check phpbb_forums_track.

因此，从本质上讲，他们有一个查找表来存储与用户查看主题(线程)相关的数据，然后对照论坛视图表中的时间戳对其进行检查，以确定该主题是否已被用户查看.

So essentially they have a lookup table to store the data associated with a user's viewing of a topic (thread), and then check it against the timestamp in the forum view table, to determine whether the topic has been viewed by the user.

这篇关于如何为每个用户的未读项目警报构建数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！