mysql database design: thread and reply of a reply?

Question

in my forum i have threads and replies.

one thread has multiple replies. but then, a reply can be a reply of an reply (like google wave). because of that a reply has to have a column "reply_id" so it can point to the parent reply. but then, the "top-level" replies (the replies directly under the thread) will have no parent reply.

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

at the moment it looks like this:

threads: id title body

replies: id thread_id (all replies will belong to a thread) reply_id (here lies the problem. the top-level replies wont have a parent reply) body

what could a smart design look like to enable reply a reply?

Answer 1

You have a heirarchical structure. There are two standard ways of storing this in a database:

• Adjacency lists
• Nested sets

This article by Quassnoi covers the pros and cons of each of these models. Your current design is using adjacency lists. That's OK, but you need to be aware that MySQL doesn't natively support recursive CTEs and that you have to use some tricks to get the data out efficiently, as described in the article.

Answer 2

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