Making a message board: Best way indicate a thread has been read?

[Fumigator]

I'm coding a simple message board and can't decide what the best way would be to mark a message as read. Actually, the only method I can come up with at this hour is store person ID and thread ID in a cross reference table. Sure, the row size for this table would be small (3 fields max), but on a popular board that's a lot of rows. Even just 10,000 threads and 500 users would be 5 million possible rows.

Any light bulbs out there?

[Fumigator]

One bump for this, if no one has any ideas that's ok... I'll go with my mediocre idea.

[syosoft]

Simple solution would be to do something like the following db model:

Table: users_viewed_posts
--------------------------------------------------------------
user_id|viewed_string
45 |:4:3:1:5:7:9:0:32:4:

when you log the user in, grab the viewed_string in the database associated w/ that user, and when you post the topics/posts to the page just do a simple:

$viewed = strstr($_SESSION['user']['viewed_string'],':'.$rs['post_id'].':'); 

I'd also suggest a similar method when the user is not logged in. $_SESSION['viewed_posts'] = ':1:3:4:';

So you can keep the viewed post logic the same, would just have to work out the variable names, probably best to be in an isolated method...

$viewed = $forum_obj->hasViewed($rs['post_id']);


That should remedy your problem - assuming strstr is fast enough for ya

[Fumigator]

Thanks for the input. I did consider a similar method of packing all viewed IDs into one string but there are drawbacks. It's not normalized data for one, so I can't take advantage of a RDBMS's strengths. I would save storage space but that would be a tradeoff I'm not sure I want to make. And that string could get pretty long-- I'm sure I've viewed thousands of posts in this forum, for example-- though I have noticed they only keep 1 month's worth of posts active.

Next week I'll make a final decision, thank again

[syosoft]

well if your concern is your database handling the data, then you'll be stuck with the 5 million rows. However, if you want php to be able to handle it....

$_SESSION['user']['viewed'] = exlpode(':',$rs['viewed_string']);
$hasViewed = in_array($_GET['post_id'],$_SESSION['user']['viewed']); 

But i think if you're not going to be using the viewed_string for anything else, it would probably be easiest to just keep it a string and .= $_GET['post_id'].':';

Yeah the strings could get long...but what impacts performance more - large number of records or 1000k data in a column. And you can still search it...

$q = 'SELECT user_id FROM user_views WHERE viewed_posts LIke '%:'.$_GET['post_id'].':%';

Somethign to think about, you'd have to write a script that placed numbers in a similar database (1-10000 (posts) x 25 (users)) and measure the impact when fetching records vs having hte db be able to jump to a single record and either look through a string or just return it.

[marek_mar]

I'd try to only store the date of the last read post of each thread.
If you'd rather store the unread post id's I'd recommmend to store the ids of unread posts.

[syosoft]

Why would you store unread? Seems like that is a little counter-productive. Thoughts? Explination?

[marek_mar]

Usually the number of read posts is greater than the number of unread ones... I don't know why you think it would be counter productive to store the unread posts which is wat the feature needs (preferably stored on demand) and consider it productive to store huge amounts of data of no real value.

[Fumigator]

I've been trying to figure out how vBulletin marks messages read/unread and I'm fully convinced they do not have a massive user-to-thread storage and that it's mostly all date and time manipulation. They store a login timestamp and simply check to see if there are any messages newer than the login timestamp. That's way smarter than storing a billion rows of nothing.

There is a exception to this rule however. If you are in a category with all new threads or new posts in threads they all show "unread", but you can click on random threads and then back out and those individual threads will then be marked "read". So there is some kind of individual user-to-thread storage at least on a temporary basis. Fancy.

[marek_mar]

vBulletin's forum marking is broken... at least on this forum.
Just by the way. There are at least two methods of doing what you want to. You could only show these threads as unread which were posted to since you were last active or track the unread posts so that unread posts will remain unread no matter if your sesson terminates. As far as I know vB doesn't do the second option.