Determining unread items in a forum

Question

Using PHP and MySQL, I have a forum system I'm trying to build. What I want to know is, how can I set it so that when a user reads a forum entry, it shows as read JUST for that user, no matter what forum they are in, until someone else posts on it.

Currently, for each thread, I have a table with a PostID, and has the UserID that posted it, the ThreadID to link it to, the actual Post (as Text), then the date/time it was posted.

For the thread list in each forum, there is the threadID (Primary Key), the ThreadName, ForumID it belongs to, NumPosts, NumViews, LastPostDateTime, and CreateDateTime. Any help?

Answer 1

The traditional solution is a join table something along the lines of:

CREATE TABLE topicviews (
    userid INTEGER NOT NULL,
    topicid INTEGER NOT NULL,
    lastread TIMESTAMP NOT NULL,
    PRIMARY KEY (userid, topicid),
    FOREIGN KEY (userid) REFERENCES users(id),
    FOREIGN KEY (topicid) REFERENCES topics(id)
);

with lastread updated every time a topic is read. When displaying the list of topics, if the topics.lastupdated is > topicviews.lastread, there are new posts.

The traditional solution is rubbish and will kill your database! Don't do it!

The first problem is that a write on every topic view will soon bring the database server to its knees on a busy forum, especially on MyISAM tables which only have table-level locks. (Don't use MyISAM tables, use InnoDB for everything except fulltext search).

You can improve this situation a bit by only bothering to write through the lastread time when there are actually new messages being read in the topic. If topic.lastupdated < topicviews.lastread you have nothing to gain by updating the value. Even so, on a heavily-used forum this can be a burden.

The second problem is a combinatorial explosion. One row per user per topic soon adds up: just a thousand users and a thousand topics and you have potentially a million topicview rows to store!

You can improve this situation a bit by limiting the number of topics remembered for each user. For example you could remove any topic from the views table when it gets older than a certain age, and just assume all old topics are 'read'. This generally needs a cleanup task to be done in the background.

Other, less intensive approaches include:

• only storing one lastread time per forum
• only storing one lastvisit time per user across the whole site, which would show as 'new' only things updated since the user's previous visit (session)
• not storing any lastread information at all, but including the last-update time in a topic's URL itself. If the user's browser has seen the topic recently, it will remember the URL and mark it as visited. You can then use CSS to style visited links as 'topics containing no new messages'.

Answer 2

May be storing in another table UserID,threadID, LastReadDateTime when the user read that thread.

if (LastPostDateTime > LastReadDateTime) you got an unread post.

Sadly you have a great overhead, on every read you'll have a write.