Private message unread markers

PHP
1

Hi there,

Right now I have a private message system. Private messages are THREADED (as in, there’s not just one database table). There’s a “pm_threads” table (containing the subject and the starter’s user ID, timestamp, etc) and each pm_threads has many pm_posts…

What would be the best way to manage “private message markers?” In other words, what would be the most efficient way to track who the thread is “unread” for (since there’s only one thread)? In the pm_threads table, would it be easiest just to have two columns, unread_for_starter and unread_for_recipient?

Never done unread markers before… I am also developing a custom made forum system too. So I’d port over this concept there as well…

Thanks for the ideas in advanced.

2

Funnily I always do the opposite . I place the linkage into it’s own table. Traversing trees inevitably involves self joins, which will be more expensive if you mix the links with the data, as they will be spread over more disk pages.

I don’t build the tree in SQL, but rather PHP, so keeping the parent id in the same table, the code simply uses recursion to build the tree.

Cheers,
Alex

3

I would not suggest doing this as you will have NULLs all over your database and it’s not helping in anyway to increase performance or save HD space.

If you keep your tables in 4th normal form you can add an additional column to the table where all the replies are kept. A column named “unread” as an ENUM(“N” for not read and “Y” for read) or TINYINT (0 for unread and 1 for read) would suffice your requirements.

PS. If you are on MS-SQL you can use CHECK instead of ENUM or TINYINT

4

Hi…

Funnily I always do the opposite :). I place the linkage into it’s own table. Traversing trees inevitably involves self joins, which will be more expensive if you mix the links with the data, as they will be spread over more disk pages.

You can index both the id and the reply_to together to mitigate this, but I’ve just found a separate table easier to work with. You can create views just on the linkage information. Also the content only gets joined once in an outer query. Just seems to be cleaner for me. YMMV.

If you don’t want to do this (date searches are a complication) then at least move the content out to a separate table. That will help a lot.

Another thing I’ve found useful is also having a link to the root of the thread. As all posts within a thread will have this link, it makes it easy for the DB to retrieve just the thread and work on that.


create table threading (
    post integer not null references posts(post),
    root_post integer not null references posts(post),
    reply_to integer references posts(post));

As for having read the thread, I can’t see anything simpler than a table joining people to threads holding the date they last viewed the thread. The date is most flexible, you can drop them at the first unread post in the thread for example.

yours, Marcus

5

at first I thought you’d need a junction table for this, particularly if it gets more complex for a forum etc. But then I’ve been thinking about this column there, is_read, wouldn’t it be possible to put a serialised array in there with the IDs of users who’ve read it, and then check with if in_array.

6

Until the other participant replies to the thread, then it should be set to ‘unread’ again (exactly like forums do).

I think the original poster’s idea of having a topic_read field for the starter and one for the other participant is not such a bad idea.

If there are ever more than just two participants then it won’t work though, and a junction table should be used.

7

I have tried a couple of methods of doing message systems…

The simple variety is one table, message_to, message_from, is_read.

That works well if you don’t need a subject.

For a thread driven system, I setup a thread table with id, date_added, last_update_user_id, last_update, title, and then a messages table with thread_id, user_id, message.

That way I can query the threads and users table for an inbox type page easily, and then join messages and threads for the message thread page.

There is a third table, thread_participants, which has user_id, thread_id, so I can have one thread to many participants.

I haven’t considered having read/unread in the second method, as an email notification is sent upon thread create and message reply, and for my needs that is enough. If I did though, I could add is_read to thread_participants, and then update that to false when replies are added.

By the way, there is no point having unread_for_starter, as surely if they typed the message, they have already “read” it…

8

(as in, there’s not just one database table

You don’t need multiple tables to implement threaded discussion boards, in fact I’d suggest against.

Use a single table where each thread has a parent ID, something like:

id, parent_id, subject, content

EDIT | I didn’t even read your entire thread I just answered what I thought I should.

Anyways, I think using a simple junction table would be sufficient, if your going to persist many un-read markers. Otherwise, I’d personally just keep the read thread ID’s in a cookie.

Cheers,
Alex