Most efficient way to track unread messages per user

Question

I am working on a jQuery Mobile Web App where a company will have the ability to message certain groups of users (based on their profile preferences).

I am debating on what is the most efficient way to mark when each user has read the latest messages. I have considered using a session to try and keep track of the last time they opened the messages page, and comparing that to the post times of the messages. I have also considered a table with the message_id and the user_id, marking each one as read when they open the page.

I think both would work, but I am trying to balance the pros and cons. Keeping in a database would allow me to keep a history (especially if i added a timestamp column to know when they read the message), but if it is going to hurt the app performance due to the table size, then it may not be worth it. The app will potentially have 10's of thousands of users.

One thing I should probably mention is that the users may use the app on multiple devices and the app will have very long session times, potentially allowing the user to stay logged in for months. I like the idea that if they read it on one device then it would mark it read on all devices, which may make sessions difficult to work with, right?

Answer 1

Ok, I'm gonna put everything I said in the comments into one solid answer.

Short Answer: You should be using the database to store 'read' notifications

Logic behind it:

1. It should be a negligable performance hit with decent servers and optimized code (couple of ms max) even with hundreds of thousands of users
2. It is highly maintainable
3. You can track it and sync it across devices

Specifically why you shouldn't use sessions

1. Sessions were designed to store temporary user data (think ram), they're not supposed to log stuff.

2. You should not be keeping sessions for months. It is highly insecure as it opens up a much larger window for session hijacking. Rather you should be generating a new session each time the app is accessed, and using a different "remember me" cookie or something each time to authenticate them.

3. Even if you do make your session persist for months, after those months won't the user all of a sudden get a bajillion "unread" notifications?

How to store it in the database
This is called a many-to-many relationship (from the message perspective) OR a one-to-many relationship (from the user perspective)

Table 1: messages

ID, message, timestamp

Table 2: messages_users

ID, user_id, message_id, read

Table 3: users

(Do user business as usual)