Have Geek Brain

Mysql database design: response stream and response?

on my forum i have threads and answers.

one thread has multiple responses. but then the answer could be the answer to the answer (like google wave). because of this, the reply must have a "reply_id" column, so it can point to the parent reply. but then the "top level" responses (responses directly below the stream) will not have a parent response.

so how can i fix this? how the columns in the response table (and flow table) should be.

at the moment it looks like this:

Topics: I would title Body

answer: I would have a thread_id (all replies will belong to a thread) reply_id (thats what the problem is: top level replies do not have a parent answer) Body

what might intelligent design look like to provide an answer to an answer?

+2


a source to share


2 answers


You have a hierarchical structure. There are two standard ways to store this in a database:



This article from Quassnoi describes the pros and cons of each of these models. Adjacency lists are used in your current project. That's good, but you need to be aware that MySQL does not support recursive CTEs and that you need to use some tricks to retrieve data efficiently, as described in the article.

+1


a source


See this book by Joe Celko: http://www.amazon.com/Hierarchies-Smarties-Kaufmann-Management-Systems/dp/1558609202/ref=sr_1_3?ie=UTF8&s= books & QID = 1271689275 & cf = 1-3- spell



-1


a source






More articles:


All Articles