Re: Help with designing threaded discussion group

From: Jakob Outzen <>
Date: Fri, 23 Dec 2005 06:20:53 GMT
Message-ID: <>

Thks for your recommendations - looked around but could not find anything. I agree that i shouldnt try to "invent the wheel" - have looked around to find excisting solutions, however only width limited success.
Have been inspired though to following solution; Creating 2 new properties called children, subthread. Children will have a value of 0 (for no children) and 1 (for children). Subthread will have a subthread number which should be unique in conjunction with the "root thread number" (post id of root). Whenever a new post is added it will get the same subthread as the parent if the parents "children" value is 0. If not, it will make an update query adding +1 to the subthread numbers of all excisting posts within the "root thread number" with a substhread number greater than the subthread number of the parent. After that I can make a normal "insert" of the new post with a unique subthread number.

When retrieving a recordset I only have to make 1 sql query and can write directly to html without having to store temporary data in an array! Can anybody comment on this approach - performance wise? regards
Jakob Outzen

PS: Realize this may not be the correct forum to discuss this topic so will continue this thread in comp.databases.mysql

On 22 Dec 2005 16:41:12 -0800, "JOG" <> wrote:

>Jakobo wrote:
>> Im trying to design a threaded discussion group in an intranet
>> application. Basicly i want to design the database and the subsequent
>> sql statements so that i put a minimum of workload on the server/client
>> handling the application, avoiding looping through large arrays. I want
>> to be able to get all the root posts of the group, and unfold one of
>> these root posts showing all the underlaying posts in a hierarchical
>> tree structure. Have so far planned for following properties of a table
>> design (holding a single discussion group); field0 - post id (auto
>> number), thread - (post id of root), answer (post id of parent)
>> together with other fields reg. the post itself (author, date, subject,
>> mess. text).
>Having performed this task from scratch several times myself, I have
>one recommendation to you above all else: look at someone else's code.
>I once was naive enough to think i could reinvent the wheel (but better
>of course, superior road traction maybe), and it was only when I
>downloaded PHPBB and had a look at their source code that I realised
>just how much I had to learn about engineering even something as
>relatively simple as a bulletin board.
>While I don't use the software any long, head to
> and take a look at their source. Its free, and
>it's a good clear structure.
>all best, Jim.
>> Can anybody please help with idears about how I should design the table
>> and/or construct sql statements so that i can retrieve data from a
>> record set and put them in a hierarchical structure without having to
>> loop through large arrays. Im working in a windows invironment, using
>> IIS, mysql, vbscript serverside, jscript clientside.
>> regards
>> Jakob Outzen
Cut out the laughing if replying to email address Received on Fri Dec 23 2005 - 07:20:53 CET

Original text of this message