Table structure for forums.

Hi Folks,

I am developing forums for a site. I just want to know what should be the table structure for storing the messages which are hierarchical i.e reply to a reply, reply to the second reply and so on.

Also how to display each message under the parent message.

The table structure I think of is

Replyid|ForumID|TiTle|Message|MessageIcon|ParentReplyID|ReplyDate|ReplyModificationDate|

ParentReplyID is the Replyid under which a particular message will come.

But the problem is how to display them under the proper parent.

Please enlighten me.

Regards,

Samir

Wrong forum buddy, try the HTML forum, PHP forum, etc...



Phreak

You can create a single messages table and use foreign keys in the table to reference the primary key of the same table. That way you will be able to create recursive one to many relationships and display the whole realtionship tree to as may levels as you like. I created a sample project a while back but it uses VB, so its not your kitty here sadly.



I would have the following

Posts Table

ipkPostID (Primary key - auto increemnt field)
ifkPostID (references the primary key)
vSubject
vMessage
dtPost

Thanx MikkyThomeon

I got a fair idea about the table structure u are talking about. BUt how do I display them in a hierarchical form. Can u give a small example. Even if the code is in VB it will do. I just want the idea.

Thanx in advance.

Regards,

Samir

The way I did it was using classes and collections, but seeing that you are not using VB then you will need to use a recursive function to show the hierarchy

VB Code:

Sub ShowPosts(cn As Connection, PostID As Long, NestLevel As Long)
    Dim rs As Recordset
    Dim NstLevel As Long
    Dim Posts() As Long
    
    Set rs = cn.Execute("SELECT pkid,fkid,subject FROM posts WHERE ikfpostID = " & PostID)
    'write html code here to show the subject, indented by the nest level
    Do Until rs.EOF
        NstLevel = NestLevel + 1
        ShowPost cn, rs("fkid"), NstLevel
        rs.MoveNext
    Loop
End Function

something like this - have not tested it. The only limitation with this is the 32 bit stack.

oops

VB Code:

Set rs = cn.Execute("SELECT pkid,fkid,subject FROM posts WHERE fkid = " & PostID)

HI MikkyThomeon,

Thanx alot. I Think this should help.

BUt one doubt. See the subroutine u are using sets a recordset. SO each time this routine is called, the instance will be created. Is there a way by which can we set the recorset once and get the records using only one query. Do u think we should maintain levels in a separate column.

Please let me know.

Regards,

Samir.

I agree with the single call to the database, so what you can do is pass a recordset as a parameter into the function.

VB Code:

Sub CallShowPosts()
    Dim cn As Connection
    Dim rs As Recordset
    Set cn = New Connection
    cn.CursorLocation = adUseClient
    cn.Open "DSN=MyDSN"
    Set rs = New Recordset
    rs.Open "SELECT TOP 1000 * FROM Posts ORDER BY PostDate DESC", cn, adOpenStatic, adLockBatchOptimistic
    ShowPosts rs, 10, 1
End Sub
 
 
Sub ShowPosts(ByVal rsPosts As Recordset, ByVal PostID As Long, NestLevel As Long)
    Dim rs As Recordset
    Dim NstLevel As Long
    
    rsPosts.Filter = ""
    rsPosts.Filter = "fkid = " & PostID
    'Set rs = cn.Execute("SELECT pkid,fkid,subject FROM posts WHERE ikfpostID = " & PostID)
    'write html code here to show the subject, indented by the nest level
    Do Until rs.EOF
        NstLevel = NestLevel + 1
        ShowPost rsPosts, rs("fkid"), NstLevel
        rsPosts.MoveNext
    Loop
End Sub

Here I have just massed the last 1000 records, so it is probably better that each thread in the folum is related by a single unique ID, so that when you pass the recordset into the Showposts function, you can issue a query like this:


strSQL = "SELECT * FROM Posts WHERE TopicID = 23"

This will then return a recordset that contains all the related posts on a subject, and you can pass this recordset into the Showposts function to sort it into a hierarchy