What's the optimal DB structure for a threaded forum?

Question

I want to build a threaded forum for an elearning site (opensource asp.net mvc ofcourse, though this doesn't matter for this question).

What should be the DB structure which will help retrieve the forum postings with optimum performance? I am not putting a no. to it as it may vary with the amount of rows being retrieved.

Besides I should be able to link a particular thread with another threads. For eg. show "Related Forum Links".

I am using SQL Server 2005.

The following is the structure that I have in mind (shamelessly took it from ) Stephen Walther Excellent blog post

Table : Forum

· Id
· ParentId  (null if this is the first message)
· ParentThreadId  (Identify message in the same thread)
· Author
· Subject
· Body
· PostedDate

Table: RelatedForum

· ForumId
· RelatedForumId

Ideas/suggestions welcome.

Thanks in advance.

Answer 1

When you have non-recursive top-down (Forum -> Thread -> Postings) retrieval of your data in mind for the most common use case, than this table structure is a good start, because this would result mainly in WHERE ParentId = @SomeId queries.

When you want to be able to calculate things like "How many postings exist in this Forum/Thread?", you will easily get into the situation that you are unable to tell which Ids are nested into which other Ids (i.e. the children relationship is missing).

You could account for that by redundantly saving ThreadId and ForumId into each posting. Then you would be able to ask SELECT COUNT(*) FROM Postings WHERE ThreadId = @SomeId.

These IDs are unlikely to ever change for a given posting, so the redundancy will not right away create insert/update anomalies, but you should have a procedure in place to update all related postings with the correct IDs in case you decide to move things around.

For more advanced ways of storing hierarcical data into a RDBMS, you can look into the answers to this question (it's my own, "no fishing up-votes" intended): "What is the most efficient/elegant way to parse a flat table into a tree?"

Answer 2

Table: Post

· ThreadId
· UUID
· Author
· Subject
· Body
· PostedDate

Table: Thread

·ThreadID
·Forum
·UUID
·Author
·Subject
·Body
·PostedDate

Only on a MySQL server with Caching and Indexing. Otherwise this Structure is not the best but with the said server, this makes for easy counting and full-text-searching

Answer 3

Looks good. I would call ParentThreadID simply ThreadID. Adding ForumID can't hurt, especially for counting purposes.

You should add AuthorName. Presumably Author is an ID to your User table. Pull that user's name and attach it now. This saves you the trouble of looking of 50 names from the user table when displaying a list of threads or responses. Similarly, if a user gets deleted from the system, you can't look up the name any more. And certainly don't want to delete those nodes from the tree.