Personally, I have made various trees, and decided to use two tables to effectively make the trees readable at a fast pace.
The stream table contained 3 columns:
stream_identifier, stream_branch_identifier, and stream_sort
The branch table contained 3 or more columns:
branch_identifier, branch_parent
Each stream contained a sorted list of which branches to follow to get to that one little result.
To then pull out a list of branches to follow to get to that one result I wanted, I'd run a fairly simple SELECT statement:
- SELECT *
-
FROM `streams` `stream`
-
LEFT JOIN `branches` `branch`
-
ON `stream`.`stream_branch_identifier` = `branch`.`branch_identifier`
-
ORDER BY
-
`stream`.`stream_identifier` ASC,
-
`stream`.`stream_sort` ASC;
Table branches:
- branch_identifier branch_parent
-
-------------------------------
-
1 0
-
2 1
-
3 2
-
4 0
-
5 4
-
6 5
Table streams:
- stream_identifier stream_branch_identifier stream_sort
-
------------------------------------------------------
-
1 1 2
-
1 2 1
-
1 3 0
-
2 2 1
-
2 3 0
-
3 3 0
-
4 4 2
-
4 5 1
-
4 6 0
-
5 5 1
-
5 6 0
-
6 6 0
If I just wanted to get to the branch with the 1 for its identifier, I could add a WHERE clause, and get a result table as shown below.
- SELECT *
-
FROM `streams` `stream`
-
LEFT JOIN `branches` `branch`
-
ON `stream`.`stream_branch_identifier` = `branch`.`branch_identifier`
-
WHERE `stream`.`stream_identifier` = '1'
-
ORDER BY
-
`stream`.`stream_identifier` ASC,
-
`stream`.`stream_sort` ASC;
Result:
- stream_identifier stream_branch_identifier stream_sort branch_identifier branch_parent
-
--------------------------------------------------------------------------------------
-
1 3 0 3 2
-
1 2 1 2 1
-
1 1 2 1 0
There may be a different way you want to go about this, but the question you're asking is the exact one I asked myself yesterday when I wanted to get a set of modules out of a table, and a way to get to modules within sub directories.
