How to structure a database for unread event messages for each user

Question

I have only a general question about database theory. I need to do something similar to show which messages / items the user has viewed or not (like in a forum) or an unread email message. I have messages that can be viewed by several users, but it should be separated by the user who was actually viewing it. Therefore, if user A views message 1, he will no longer show that message 1 is a new item to view, but for user B he will still show that message 1 is a new item to view.

I have a search for other ideas, and one of them is to get the timestamp when the user last logged in, but I really need to keep track of the messages they saw, as opposed to the messages that have occurred since they last logged in .

I would like, if possible, a solution for the MySQL database, but I am open to cookies if necessary. I could do it myself and just understand, but I would appreciate any advice on how to properly structure the table (s) to make it most efficient. In addition, bandwidth and memory are not a problem.

Answer 1

When considering the appropriate 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`; 

and

 # 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`; 

Then I look here on my wikipedia :

This table stores entries for topics visited to mark them as read or read. We use the mark_time time stamp in conjunction with the last post of topic x timestamp to find out if topic x is read or not.

To determine if a topic has been read, check phpbb_forums_track as well.

Thus, they have a lookup table for storing data related to the user viewing the topic (stream), and then check it for a time stamp in the forum viewing table to determine if the user has viewed the topic.

Answer 2

Just create a simple cross-reference table ( read_posts or something else):

 user_id|post_id ---------------- 2 | 132 53 | 43 .... 

Make sure that both of these columns are indexed (it is especially important that the user_id index is indexed), and then use the join (or auxiliary query) to select unread messages for the registered user. If you are just trying to show a list of unread messages, for example, you simply run:

 SELECT * FROM `posts` WHERE `post_id` NOT IN ( SELECT `post_id` FROM `read_posts` WHERE `user_id`='[$USER ID]') ORDER BY [your ordering clause] 

Answer 3

Based on this description, I would use a simple table with possibly three columns.

• User ID
• Message id
• Initial time stamp

When the user views the message, add a row to the table. If the row does not exist in the table for the given user / post id command, they did not view the message.