Unanswered: Hierarchal Design For Nested Folders Using MySQL
I posted a question in the design and concepts folder http://dbforums.com/t588943.html that got me only half way to solving my problem. I need to figure out how to do a Hierarchal Design in MySQL to represent multiple layers of nested folders. I could do the following
Folder ID(pk) Folder Name
Filename Folder ID (fk)
But that would only provide for one layer of nested folders. According to the response I received in that other forum folder this is possible with Oracle... does anyone know how to do this in MySQL?
as for retrieving, well, only oracle does that recursively
in mysql you will have to bring in the entire tree (select all rows of the table) into your scripting language (php) and traverse the tree with code
either that, or traverse the tree by repetitively calling the database one subtree level at a time, but the performance here sucks mightily
finally, you could compromise and use a single query with a number of self-joins, and while this is quite efficient, it can only traverse the tree as many levels deep as the number of self-joins you are willing to code into the query
Re: Hierarchal Design For Nested Folders Using MySQL
Another way to do it:
create table task
(id1 int not null,
id2 int not null auto_increment,
descr varchar(50) not null,
This way is easy to get the "father" instancies by selecting rows with id2=1. "child" instancies will follow the father's id1. Getting/checking a task with no subtask is much easier than in the other implementation.
id1 id2 descr
1 1 'First Task'
1 2 'First Task first subtask'
2 1 'Second Task with no subtask'
3 1 'Third Task'
3 2 'Third Task first subtask'
3 3 'Third Task second subtask'