Optimal way to implement post read flags in forums?

Question

I am looking for a optimal way to store info about which users already read what posts on simple forum engine I am building, so I can mark new posts for them. I am looking for both space- and performance- optimized way.

The first that comes to mind is the pure way of creating table with post_id and user_id columns in which each row would represent read post by a user. But this leads to a lot of data inserted and performance stress on the database. And even more of data inserted after clicking on "mark all as read". If all users would have read all posts, this would mean (users count) * (posts count) rows in the database.

This method could be optimized to store timestamp for each thread and each user and update these timestamps each time user reads new posts. But it's still a lot of data.This would mean (users count) * (thread count) rows.

I can simplify this to storing "last visited" timestamp on the forum (i.e. thread category) level. This would mean (users count) * (forum count), which is not that bad, but there is a question when should I update this timestamp, so it's not too much confusing for users.

Thanks for any ideas.

Incubation

i find the optimal way to do things i'm not sure about, is to download an open-source project, and see what they do, then steal the idea\code. – Spalla

Instead of keeping track of which posts/threads the user has seen, keep track of which posts/threads the user has not seen. This way, your worst case scenario is not nearly as bad (depending on the nature of the forum, of course). – Fortyish

erisco: This would cause massive performance stress every time user posts new post. – Building

Dagon: I have very specific needs (I need to have same user credentials for the rest of the site as for the forums, I want to extend the custom functionality, I need compatibility with old login system etc.) It seems much easier to write own simple system that does exactly what I want instead of adapting heavy-weight and universal forum engine. And I know how a lot of PHP open-source projects are written - PHP4-like procedural mess. I just dont to waste time with them ;) – Building

Answer 1

I suppose "each thread has n posts" is how you use those terms.

I think the best would be to store each (user_id, read thread_id, timestamp). I understand you realize it is not (users count) * (thread count) rows, it is (users count) * (read threads count) rows.

If a user marks all as read insert a special value as thread_id like 0.

As Dagon pointed a good open source forum would have already solved all the problems.

Dougdougal

I agree with (user_id, read thread_id, timestamp), which by the way, is the same adopted by PHPBB, which I suggest you download. – Bahner