[disco_stu]

Hi, I have a basic grasp on SQL and database design, but I would like some help brainstorming...

I'd like to set up a database backend for an online forum, I guess somewhat similar to Slashdot's design. I'd like to have the following type of comment design:

• Comment #1
• Comment #2
  • Comment #2.1
  • Comment #2.2
    • Comment #2.2.1
    • Comment #2.2.2
    • Comment #2.2.3
• Comment #3
  • Comment #3.1
• Comment #4
• Comment #5

Basically I'd like to design a database that allows not only comments on the original post, but comments also on other comments.

I'd greatly appreciate any ideas on how to design this (I'll be using MySQL for my db), or if you could point me in a general direction.

Thanks a lot,
disco_stu

[Chuckit]

It looks like a pretty normal one-to-many relationship here, the standard solution for which is to put some field like parent_id that refers to the comment being commented on.

[Zim]

To expand on what Chuck said.. as a programmer I kept thinking how to make linked-lists in DB... but just store all comments as equal items, with their fields being

- parent_id
- text
- who
- when
- ip
- timestamp

and then for each comment you print, search for all comments that have it as their parent_id, and print those comments.. again searching for each of those for any comments that have this "sub" comment as their parent... etc.

Still drives me batty sometimes

Mike

[disco_stu]

Originally Posted by Zim

*SNIP*...then for each comment you print, search for all comments that have it as their parent_id, and print those comments.. again searching for each of those for any comments that have this "sub" comment as their parent... etc.

Thanks for the replies, guys. This was along the lines of what I was thinking, but I wasn't sure how efficient it was to recursively query the database. Worst case, there could be a TON of calls to the database ( O( n log n )? Arg, I can't believe I'm actually using something from my Algorithms classes! ), so I was just curious if there was another way to design the db so that I wouldn't have to do so many potential queries.

Again, thanks for the input.
disco_stu

[Chuckit]

If you want to load them all at once, you could also include a thread attribute and then sort them into parent-child relationships based on their parent_id yourself.