Here is the table I have created by testing the closure table method.
| id | parentId | childId | hops
| | | |
| 270 | 6 | 6 | 0
| 271 | 7 | 7 | 0
| 272 | 8 | 8 | 0
| 273 | 9 | 9 | 0
| 276 | 10 | 10 | 0
| 281 | 9 | 10 | 1
| 282 | 7 | 9 | 1
| 283 | 7 | 10 | 2
| 285 | 7 | 8 | 1
| 286 | 6 | 7 | 1
| 287 | 6 | 9 | 2
| 288 | 6 | 10 | 3
| 289 | 6 | 8 | 2
| 293 | 6 | 9 | 1
| 294 | 6 | 10 | 2
I am trying to create a simple tree of this using PHP. There does not seem to be enough data to create the table. For example, when I look purely at parentId = 6:
-Part 6
-Part 7
- ?
- ?
-Part 9
- ?
- ?
We know that parts 8 and 10 exists below Part 7 or 9, but not which. We know that part 10 exists at both 3 and 4 nodes deep but where?
If I look at other data in the table it is possible to tell it should be:
- Part 6
- Part 7
- Part 9
- Part 10
- Part 9
- Part 10
I thought one of the benefits of closure tables was there was no need for recursive queries? Could you help explain what I am doing wrong?
EDIT: For clarification, this is a mapping table. There is another table called "parts" which has a column called part_id that correlates to both the parentId and childId columns in the "closure" table. The "id" column in the table above (closure) is just for the purposes of maintaining a primary key. It is not really necessary. The methods I have used to create this closure table is described in the following article: http://dirtsimple.org/2010/11/simplest-way-to-do-tree-based-queries.html
EDIT2: It can have two and three hops. I will explain easier by assigning names to the items.
Part 6 = Bicycle
Part 7 = Gears
Part 8 = Chain
Part 9 = Bolt
Part 10 = Nut
Nut is part of Bolt. The Bolt and Nut combo exists directly within Bicycle and within Gears which is part of Bicycle.
In relation to what method to use I have looked at Adjacency, Edges, Enum Paths, Closures, DAGS(networks) and the Nested Set Model. I am still trying to work out what is what, but this is an extremely complex component database where there are multiple parents and any modification to a sub-tree must propogate through the other trees. More importantly there will be insertions, deletions and tree views that I wish to avoid recursion during general use, even at the cost of database space and query time during entry.
