Discussion Forum - table design suggestions

  • Hi,

    I am working on this discussion forum, same like this one. I have created two tables - one to store the parent messages, ie., the first message in a thread, and second table to store all the child messages or the replies. This is the table design.

    ForumParents - threadid, userid, title, message

    ForumChildren - messageid, threadid, userid, title, message (messageid is the primary key identity column, threadid is the parent-message threadid and foreign key)

    The forum main page lists the threads (top x) in a table format - Title, StartedBy, StartedDate, LastPostBy, LastPostDate, #ofReplies. It looks like the select query would use two temp tables and three joins to get the above columns.

    Is this a good design to have two seperate tables? There are more columns in the forumparents table that I have left out (stickie, blockreplies, notification...), and those columns does not apply to child messages. Or go with one table?

    Thanks

  • Not saying your design is flawed, but I would model it based on the type of objects found in a forum.

    Table for Forum.

    Table for Threads.

    Table for Messages.

    The only distinction between a parent and a child message would be a property of the Message entity. No need for a different entity (parent & child) to distinguish different types of essentially the same thing (messages).

  • Can you please tell what typical fields you would have in each table. There is going to be just one forum. I think there will be no need for Forum table, if that's what is it for. And you said there is no need for a different entity (parent & child), but you have listed two seperate tables for threads and messages. (I am assuming threads will have the parent messages and messages will have the replies?)

    Thanks.

  • I'd see a Thread as being a container for 1 or more Message records. The Thread entity would not contain a message. Some properties might be:

    ThreadID (PKey)

    Subject

    IsSticky

    IsLocked

    IconID

    CreatedBy

    LastReplyDate

    NumberOfReplies

    Every Message would have a foreign key to the ThreadID containing it. Every Message may have a self-referencing foreign key pointing to the Message its replying to (Null in the case of the 1st Message in each thread). Or perhaps an ascending sequence number, with Msg #1 in any thread indicating parent message, but sequences can get ugly in forums where moderators can remove messages.

  • Thank you.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply