Indexing af discussion forum

From: <>
Date: Sat, 1 Sep 2001 12:25:45 +0200
Message-ID: <9mqdl2$13rh$>

Can anyone suggest a good way of indexing the the messages in a discussion forum. I collect all postings and replies in a single table. My most successfull attempt goes like this: I have a MsgId field which is simply an AutoInc type. For the index I use a ThreadId and a PrevId field: ThreadId: A new posting at the base level is given a unique ThreadId (Say its MsgId). All replies and successive replies to this posting is given the same ThreadId
PrevId: When a new message is going to be inserted between say MsgId "n" and MsgId "m" it is first assigned a new MsgId say "k". Next I update as follows:


Try do the following:

Insert Msg1: MsgId=1, ThreadId=1, PrevId=0
Insert Msg2: MsgId=2, ThreadId=2, PrevId=1
Insert Msg3: MsgId=3, ThreadId=3, PrevId=2

This is straight forward with no updates required. Now insert a reply to Msg2:
Insert Msg2: MsgId=4, ThreadId=2,PrevId=2

     Update Msg3: PrevId=4

and another reply to Msg2: (Re: Msg2)
Insert Msg5: MsgId=5, ThreadId=2,PrevId=4

     Update Msg3: PrevId=5

and next a reply to Msg4: (Re: Re: Msg2) Insert Msg6: MsgId=6, ThreadId=2,PrevId=4

     Update Msg5: PrevId=6

Uptil now everythhing works fine, but now try insert a reply to Msg1: Insert Msg7: MsgId=7, ThreadId=1,PrevId=1

     Update Msg2: PrevId=7

Here my index fails to sort the messages properly. I may have missed the point, but there must be a (simple) solution to this problem. At least my news-reader can mannage to sort the messages the way I want to.

Thanks in advance.


Rene Received on Sat Sep 01 2001 - 12:25:45 CEST

Original text of this message