php - Hierarchical directory structure in mysql database
I am trying to creating dropbox like web application. I want user to create directory and upload files to server. I have completed most of the task but i am stuck with the question "how to efficiently store the directory structure in the database?".
Project requirements:
1) It should be able to store directories in a way that given the path of particular directory, it should be able to find out all the files and directories inside it.
For eg: given path: root/abc/xyz, I should be able to find all the files and directories under xyz.
2) Two directories inside different parent directories can have same name
My Research on internet:
1)There is a method called path enumeration wherein application would store entire path to directory in the column corresponding to that directory
For eg: if i have directory structure as root/abc/xyz
then row in table corresponding to xyz directory will store path root/abc
but this method doesnt seems efficient
2) I also got information on nested set model. But depth of the tree and total number of hierarchical nodes are unknown so it can't be used
3) There is another method in which i only store root_directory id instead of entire path in each directory entry
for eg: create table directory(directory_id varchar(32) primary key, directory_name varchar(128),created_date varchar(20), root_directory varchar(32),user_id varchar(32))
But how can i query the database using path as root/abc/xyz to to get all the files and directories under xyz directory. I found some multiple join operation to perform so. But join queries will affect the performance badly as the depth of directory structure increases
Please can anyone show me a way to better solution. I would be thankful to you