In forum application I have
member table and
post table. What I need is to keep track of what posts have been read by which members. What I thought about is to have a junction table with two columns:
post_id. Each time a post is seen (read) by a member a record is inserted.
I am going to need to fetch/calculate/perform:
- Number of unread posts for each member
- Get all unread posts for each member
- Given list of posts, indicate which ones are unread by particular member
- Mark all posts as read for each member separately
And I am going to need to do this on every request, since 1) and 2) is displayed in sidebar widget present on all pages and 3) is going to be needed when browsing through posts (also very common).
The model works in theory (and in practice under not much load), but sure it does not seem like a scalable solution. I am going to get ~1,000 posts per day viewed by ~500 members which gives 500,000 records a day in junction table...
I am a programmer not DB expert, thus I am asking for some ideas on how to optimize this situation.