> -----Original Message----- > From: Michael T. Tangorre [mailto:[EMAIL PROTECTED] > Sent: Wednesday, July 30, 2003 10:20 PM > To: CF-Talk > Subject: RE: Message board / forums > > > What would be the best way to structure a db for the > discussion topics and responses... Assuming I am using sql server?
It kind of depends on what you want to end up with. To me the main question is whether or not you want threaded replies (my preference) or flat topic replies (as it seems most every forum is lately). The latter is by far easier. You would have, perhaps, a table of "forums" (topic groups), a table of topics and then a table of messages. Each message would be linked to a topic and each topic to a forum. Displaying messages would be as easy as selecting all messages keyed to the topic and displaying them in reverse chronological order. You can get more complex than that of course, but that's it in a nutshell. Basically each discussion is just a stack of messages. To do threaded discussion you have several options. I think that the most common way to do this is for each message to remember the ID of it's "parent" (if it's a reply) - this is the "parent/child" model. Then you can recursively (probably using Queries of Queries is easiest/fastest here) display the messages. Another option is "nested set" based design where you defining the groupings (nestings) of your messages. This is confusing to a lot of people but has the advantage of maintaining order better than parent child and can be done with faster, simpler SQL once you get your mind around the model. However it's not as simple to maintain (add messages to the middle, delete messages, etc). Both of these are "tree models" and you'll get a lot of action if you search Google for "SQL Trees". Determining how you'd like to "walk you trees" is probably the more fundamental core of your design. Another thing to keep in mind is how you want to identify your messages. For example using an Identity (autonumber) is probably easiest but could make it hard to copy/syndicate your messages to other installations of the same software. A GUID takes up a LOT more room in the database but can allow you to mix topics from multiple databases much easier. There's still a lot more... it's a REALLY big topic. Jim Davis ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
