Database design and query

Question

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 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 feel like my homework sensor is going off.

Nathan Feger
– Nathan Feger

2011-02-18 00:21:18 +00:00
Commented Feb 18, 2011 at 0:21 — Nathan Feger
– Nathan Feger, Commented Feb 18, 2011 at 0:21

user479911 · Accepted Answer · 2011-02-18 00:28:59Z

1

I think this database design might work:

TOPIC
id
name

TOPIC_HAS_TOPIC
id
parentId
childId

COMMENTS
id
comment
topicId

answered Feb 18, 2011 at 0:28

user479911

Sign up to request clarification or add additional context in comments.

Comments

Daniel Williams · Accepted Answer · 2011-02-18 00:32:36Z

1

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.

answered Feb 18, 2011 at 0:32

Daniel Williams

9,48016 gold badges79 silver badges119 bronze badges

Comments

PhD · Accepted Answer · 2011-02-18 00:28:20Z

0

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).

answered Feb 18, 2011 at 0:28

PhD

11.4k15 gold badges70 silver badges119 bronze badges

1 Comment

PhD Over a year ago

Assumption: Assuming that a single subtopic belongs to only one topic (else go with arex's schema and you'll just have extra joins in your query)

Collectives™ on Stack Overflow

Database design and query

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related