CODEWITHSUNDEEP
mysqldatabase-designhierarchical-data
koyuki
koyuki

Reputation: 75

problem with hierarchical database model?

I got a problem with my db design, I am developing a forum in which you can add topcis and then subtopics inside that forum and so on but in that subforum It must show the answers of that topic, and the subtopic, so you have a parent topic and children, my problem comes with the id, I mean for example when you create a topic id=1, then you assign parent_id=1, but how do you know the children of that topic, for example topic id=2, parent_id=1, you can assign to the children id=2, but are there a better way? how would be your approach to solve this problem? also mysql I dont k

I got two tables

"Topic"

id name id_creator date message parent_id

"answer"

id name message id_creator date

Upvotes: 0

Views: 534

Answers (1)

MarianP
MarianP

Reputation: 2759

I had to do this in MySQL recently, MySQL unfortunately does not offer anything useful for processing hierarchical data.

I used an extra varchar column called path where I would store the 'path' leading to current item. E.g. a row with an id=127 which is a child of id=120 which is a child of id=15 would have its path='/15/120/127'.

You need to take care of this on your own when inserting data.

Extracting all rows that are direct or non-direct children of a row with id=15 would then look like:

SELECT * FROM table WHERE
  path LIKE '%/15/%'

I can also see that this is a rather much discussed topic here on Stackoverflow. Look on the right in Related column next to this question.

Upvotes: 2

Related Questions