Re: Message Board Threading with DBI/MySQL

[Henri Icarus]

The other thing you can do is you can store the thread id as a string of the id's of the message's replied to representing the actual thread hierarchy. This allows you to implement the same kind of thing that you see on this site where it's clear when looking at the threads which level their at.

So, for example, message 1 has two replies (messages 2 & 3), and there are also two repies to message 2 (messages 4 & 5). The thread ids for the messages are:

Message: 1 Thread ID: NULL
Message: 2 Thread ID: "1"
Message: 3 Thread ID: "1"
Message: 4 Thread ID: "1_2"
Message: 5 Thread ID: "1_2"

This lets you use SQL LIKE to pick out the exact level you want, ie:

SELECT ... WHERE thread_id LIKE "1_2%";

to get all the messages that thread with a message at a given reply level, etc..

You can also you regular expression searches for more complex searches. Of course such searches won't be indexable.

Also, using this method, if you sort by the thread id when you get back a list of messages then you can easily examine the thread id string with Perl regular expresions to figure out at which level of indentation to display it!

Good luck!

[Beatnik]

What about inserting, deleting, moving, etc? That would ruin the 'hierarchial' structure if you're not gonna rewrite all child threads :)
I usually stick to less obvious message ID format, like a time & date format (as unique key & incremented if necessary). To solve the pointer to the parent, you can either have a parent 'pointer' in your message ID, or have an actual parent field.

Greetz
Beatnik
... Quidquid perl dictum sit, altum viditur.

[Henri Icarus]

If you have to have that kind of flexibility then of course this system doesn't work. But usually, for a message board app, you don't need that. It's a "write once and leave it" kind of application. And let's say you do delete a message, the fact that it's gone doesn't destroy the relationships of all the other messages...

[Beatnik]

Even if it has 'child' messages, in case you have some kind of reply system? :) When deleting a message, you should also make sure to delete child messages, since orphan messages will get inaccessible if you dont (which ofcourse take up space, slow down queries, etc)

Greetz
Beatnik
... Quidquid perl dictum sit, altum viditur.