MySQL forum read/unread - effective database design?

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

MySQL forum read/unread - effective database design?

Post by JAB Creations »

I'm trying to figure out how phpBB has implemented their read/unread option for the forums. I understand the basic concept for threads at least... Here is a basic MySQL table export I'm experimenting with...

Code: Select all

CREATE TABLE IF NOT EXISTS forum_threads_read (  id INT(7) NOT NULL AUTO_INCREMENT,  id_thread INT(7) NOT NULL,  id_user INT(7) NOT NULL,  count_read INT(7) NOT NULL,  PRIMARY KEY (id)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
I've got MySQL to the point where my blog index, forum index, thread index, and thread page are all pulling the main content with a single query (I'm also using INNER JOIN a lot more now) though this is one example where I'd at least initially approach joining this read/unread table with a LEFT JOIN. So any way I'd compare the forum_thread_count_post against (if (isset(count_read))) this table's count_read value to determine the CSS class that PHP would echo for the table row. That's easy enough! :) What has me stumped at the moment is...how would I setup this up for the main forum index?

I'm thinking I could compare the count() via MySQL with an added id_forum_id column...or perhaps a last date...though I don't see any way that I could implement this without creating a gigantic mess (conceptually speaking). I've been poking around phpBB's code though I don't see any table's that suggest a correlation to the feature. Any thoughts on how I could implement this gracefully please?
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: MySQL forum read/unread - effective database design?

Post by JAB Creations »

Actually...I think I have it figured out. The basic idea is that you counting before and after posts, it doesn't matter if it's post count for a single thread or a single forum! So all I have to do is compare id_forum_count in the relational table and then in the forum_forums table compare it to forum_forums_count_posts column! (trying to use column names that signify what they are used for).
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: MySQL forum read/unread - effective database design?

Post by josh »

I think common convention for foreign keys is

table_id
not
id_table

But as per your original topic I'm pretty sure phpBB at some point discards that data and uses the last login datetime of the user, and compares that to the date times of the posts, otherwise the table wouldn't scale over time
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: MySQL forum read/unread - effective database design?

Post by kaisellgren »

The most common approach is to store last-read timestamp for each topic per user. That works well, but you will have one write per topic read which is really CPU intensive and also your number of rows will increase a lot. One of the nastiest approaches to achieve topic read states is to place last-read value in the topic URI so that the web browser will remember if it has visited that URI before and then you can use CSS to disguise between visited and non-visited (:visited). Another approach is to store the detail in cookies.

And of course, store a value of last-updated for each topic, and don't query for the timestamp of the latest post in topics.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: MySQL forum read/unread - effective database design?

Post by josh »

I thought most forums count all threads as read after the user logged out, so in fact you would store it at the forum level instead of the thread level after a certain amount of time
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: MySQL forum read/unread - effective database design?

Post by kaisellgren »

josh wrote:I thought most forums count all threads as read after the user logged out, so in fact you would store it at the forum level instead of the thread level after a certain amount of time
I think it's best to have last-updated for each forum and for each topic. vBulletin (at least) remembers my unread topics when I log out and log in. It actually allows you to change the unread behavior with different approaches in the ACP.
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: MySQL forum read/unread - effective database design?

Post by JAB Creations »

I figured this feature would be a bit more system intensive then anything I've done so far. I already do an UPDATE for thread count just for viewing the thread (though all forums do that I can only presume) though you guys have already discussed as I'm writing this there are ways to get around writing this all at once however for now I think the massive reduction in regex in Version 2.8 will compensate for the increase in write based MySQL queries in Version 2.9 of my site with the forums; they're not going to be all that active initially plus I can always add this on to my to-do list...I just gotta get this 16 months in the works version live. Adding a SELECT and an INSERT or UPDATE for everything thread/user makes me question a lot of things in general but I'm making progress and I know I have a bit of breathing room right now. I think though that by the time the size of the numbers being stored starts being comparative to date/time stamps that 1.) I will be able to hire a super genius to implement something better if I really wanted (or may even become one or enough of one to do it myself haha) and 2.) it's not as if web servers are never upgraded.

Josh...I always put any sort of id's like that. I can write stuff how a company prefers if desired though I find my own way of doing things to make more sense...at least for me. Yeah yeah...I admit it I'm an oddball with a lot of stuff. :roll: :mrgreen:

I've already implemented the read aspects (and for my own user account related forum_id_count's) and it works nicely. I know it's a bit of a stretch to say that when I implement my own site-direct way off managing forums that it will require an ALTER command for the forum_forums_read table to add a column per forum though since it's so rare I figured until I find a better way...my forums won't be all that active any way and at worst it'll just show all threads in a new forum (which would of course be 0) as already read. I think it'd probably be best to disable the forum while it executes such a feature...that would be a while from now though...plus I will implement a "Mark forum as read" feature any way. I'll figure it out as I go along.

I think I've noticed something with phpBB where it will update the counts if you manually refresh a the thread index though it won't if you click to go to the thread index page (of a forum). Like I said...I'm just trying to get the basic feature working (doing a decent job at it so far) for now. I wrote the blog and forum software last November and December respectively and ended up revising the MySQL queries (the blog originally spawned dozens of queries and now only does one for /blog/ page). I'll go with my current approach for now, because after all I'll need something to do with the future versions of my site right? :mrgreen: I think the with date/time stamp feature I might miss posts where as with this feature I definitely won't (provided it's not bugged though I test the heck out of my stuff). Once my filters become much stronger and I get some mods it'll be less important to cover every post though I'm still interested in things conceptually speaking. Thanks for all the input! :)
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: MySQL forum read/unread - effective database design?

Post by josh »

Regarding changing schemas after they're live, I recommend reading the book "database refactoring", I checked a simple implementation into the data shuffler SVN if anyone is interested.
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: MySQL forum read/unread - effective database design?

Post by JAB Creations »

I kept database refactoring in mind. Here are some of the scenarios I've considered for the forum_forums_read table...

1.) Moving a forum won't change it's ID. If I change it's ID I simply change the ID of the column name however don't see why I would do this as (first it's kind of dumb to being with) plus the vertical order of forums is actually set in the forum_forums by a column that sets the display order to begin with.

2.) Adding a new column for a new forum would mean that all the values would be set the 0...but there would be no new threads in the forum to begin with unless I manually moved threads.

3.) Unlike the forum_threads_read table each row is unique to the user because forums and their ID's will change rarely if ever and this would reduce this table's row count to no more rows then the user's account table.

4.) It's my personal site and the main point is for me to learn, if people lose their read/unread history while it would not be good in a business environment I'd rather have optimized other parts of the site on mission-critical things that should absolutely not break or be interrupted. In a sense if I end up refactoring and failing some way it's not as if anything fundamentally important would be lost.

That being said I think I've noticed an issue more so now then before with this. If most sites do this by date then I've noticed I don't sign out of my own work...no real need to. In Opera my session expires but in Firefox it does not.

From what I gather as far as the threads are concerned though is that there are two dates to juggle. First is the last session date which let's say...is saved to the cookie every time during it's initial setting. Be it a guest or a registered user the next time they return and a new session is started the previous session's date would be pulled in to a session variable to remembered for session b so you could compare the date/time stamps of posts against session a (in the session variable) while they visiting as session b. They leave, come back, and the new session would be c, if that helps make sense of it?

I'm going to mess around with my site's sessions and dates a bit more. I may consider keeping both for my current setup, the more database though accurate version for mods and admins to make sure all posts are viewed by moderators and the lighter version for users. I think that may be the way to go though I'm not entirely sure just yet.
Cirdan
Forum Contributor
Posts: 144
Joined: Sat Nov 01, 2008 3:20 pm

Re: MySQL forum read/unread - effective database design?

Post by Cirdan »

What I've done is create a cookie array of the unread posts' id since the user last logged in up to a certain point. Then when they view that thread, it deletes that part of the array, and is no longer marked as unread.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: MySQL forum read/unread - effective database design?

Post by John Cartwright »

Cirdan wrote:What I've done is create a cookie array of the unread posts' id since the user last logged in up to a certain point. Then when they view that thread, it deletes that part of the array, and is no longer marked as unread.
I believe that's how phpBB does it as well. It certainly alleviates the load on the server. I don't see any benefit to handling this on the server, as a properly normalized structure for this scenario is not practical.
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: MySQL forum read/unread - effective database design?

Post by JAB Creations »

I got it to work beautifully! Thanks to everyone's input and a bit of conceptualization the read/unread icons now work great on my forums. I'm comparing the date the user last signed on with against the threads last post date. However that does not cover posts since the user signed in so I took Cirdan's idea and reversed it; instead of setting an array of threads not viewed my array is a list of threads viewed (keys) and how many posts there were when the user viewed the thread last (values).

The problem I'm facing now conceptually speaking is...how am I going to accurately determine if there are new posts in each individual forum? I went back to Cirdan's idea which I initially implemented as counting new threads total if the unread array wasn't set. The question is...unlike the thread index when new posts are made after the user is signed in the forum index will not update (naturally) the icon back to unread posts. I'm not sure how to work around this?
Post Reply