Database design and query

I have a requirement to have a number of sub-topics per main Topic. And every sub-topic needs to be able to have n number 开发者_StackOverflow社区of comments.

I need to have database tables to represent this structure and then query for it to get the list of sub-topics, and for every subtopic get all the comments that belong to it. I also have to have a way to know when I reached the end of the comments for every subitopic.

Could someone please advise on best design/query for this?

I use MySQL.

Thank you very much! - Alex

I think this database design might work:

TOPIC
id
name

TOPIC_HAS_TOPIC
id
parentId
childId

COMMENTS
id
comment
topicId

Tables needed: Topic SubTopic (has a parent key pointing back to parent topic) Comment (has a parent key pointing to the parent sub topic)

Then for a given subtopic, just select all comments with that sub topic key.

You could design it like this:

|Topic| -1----*- |SubTopic| -1----*- |Comments|

(EDIT:The asterisk seems to mark for italics. So please ignore the color difference)

i.e., a 1-to-many relation from topic to subtopic and another 1-many from subtopic to comments

You could have surrogate primary keys per table:

Create Table Topic (topic_id int auto_increment, primary key(topic_id),...)

similarly subtopic_id for Subtopic and a foreign key to topic and so on for Comments.

Getting all the comments for a particular topic could be like: (assuming a text attribute for the Comments table (probably a TEXT data type?))

select comment.text from comments
join suptopic using(subtopic_id)
join topic using (topic_id)
where topic_id = 1;

(join is the same as inner join and 'using' is applicable ONLY if the 'join-column' shares the same name, else the syntax differs).