March 9, 2007 at 10:39 am
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
March 9, 2007 at 11:06 am
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).
March 9, 2007 at 11:12 am
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.
March 9, 2007 at 12:11 pm
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.
March 9, 2007 at 1:21 pm
Thank you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply