4

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: member_id and 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:

  1. Number of unread posts for each member
  2. Get all unread posts for each member
  3. Given list of posts, indicate which ones are unread by particular member
  4. 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.

Improve this question

2 Answers 2

Reset to default
2

It may be better to mark unread entries - read entries will accumulate over time. Unread entries you could kill after let"s say 90 days (show all stuff older than 90 days as read to avoid buildup.

You may also want to just have a top marker per forum - i.e. you can not have a specific post unread. This will seriously cut down on the number of database tnreis.

Improve this answer
2
  • My users insist on a page that displays all unread posts, so unfortunately each post has to be marked. What do you mean by marking unread entries - when new post is created then add ALL members to junction table indicating that all these member have not read the post, and then when member reads the post delete specific record from the table?
    – Eleeist
    Oct 28, 2013 at 19:21
  • 1
    Exactly. The problem is that @all read posts@ will grow over time. All unread posts can be reconfigured as "all unread posts of the last 30 days" or something like that and that can keep the entry count rather constant. I also would discuss this requirement with the users. Wanting one thing is one thing, paying for the hardware antoher one. Real unread lists are normally reserved for high value scenarios, such as email accounts or company internalmessage boards where the ressouces are available. For a public forum - any item design will blow.
    – TomTom
    Oct 28, 2013 at 19:24
1

Your design should work well.

As long as you put logic on the app side to avoid inserting records if the post is already read, which should be straight forward as you will already know this.

Is it important to know WHEN the user read the post? If so, that could change your logic a bit, but if you are only interested in knowing that they read it, your design is fine and should scale.

I am not sure how the postgresql engine works, but you should also set up an index or two as well.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.