Indexing af discussion forum

Hi,
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:

    Msg(MsgId="k").PrevId<-"n"
    Msg(MsgId="m".PrevId<-"k"

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.

Cheers,

Rene Received on Sat Sep 01 2001 - 05:25:45 CDT