CODEWITHSUNDEEP
database-design
Chaitanya
Chaitanya

Reputation: 2444

Database design to track updates since last visit

I am designing database for a Forum kind of application. The use cases involves showing unread topics for an user. So, I require to keep track of read/unread status for each post for each user id.

I have users and posts table. I am thinking I should created 'Read_Posts' table with userid and postid, so I can add each post user has seen to this table. But, over time this table will become very huge.

Any other alternate approaches to this?

Thanks!

Upvotes: 3

Views: 409

Answers (3)

Kris C
Kris C

Reputation: 2848

For each user you could store the greatest post_id when they logoff (or at each action that they take). Then when they return, any posts with an id greater than that are flagged as being new.

It doesn't account for posts that they didn't read on their previous visit, but would give a simple indicator of activity that is new since their last visit. It would only need an extra column on the users table, so should be relatively low impact.

This assumes that posts have incrementing integers as their ID of course

Upvotes: 0

Daniel
Daniel

Reputation: 28104

You can somehow try to compress the information somehow. You need to show the user if he has read a post, only if the user logs on, so you should store the information somewhere near the user. The user might look throught your posts in a date sorted way, so read-post-information of nearly dates should ly nearby, for efficient caching and reading of many values.

Try a table with this stucture:

  • user_id
  • postmonth
  • readposts_array

Depending on how many posts you expect, you might use week or day instead of month. If you cache the value in your app, you might see a performance increase when displaying many posts.

Upvotes: 1

Oded
Oded

Reputation: 499392

Your design seems like the right one.

You can perhaps consider an expiration for this functionality - that is, any post that is over a week old (or whatever) can be expunged from this table (either archive it or not).

Or a maximum of 10 unread posts per user...

Upvotes: 2

Related Questions