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.
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?"