How do I create a table structure and query that allows for a flexible hierarchy of records. For example, in a forum type of environment, I have a table that is
Then a relate table that is a parent/child as
parentid, childid which are both t1.id numbers
So if the table were populated with
8 0 (0 being no children)
And I want to display
So is this the correct table structure and if I do it like that, is there a way to do the query to return all the records in this hierarchy order?
Create a belongs_to field that contains the id of the parent.
Makes sense, that saves a join on any query. Is there a way to build the hierarchy with a single query or does that need to be an iterative process from php?