Create folder / file structure

I'm considering storing a working directory (i.e. recursive/nested folder/files) into a mysql database.

The idea is to have a 'projects' table, and a table that contains all folders / files with their corresponding paths in the tree (varchar).
Querying a project should return folders and files as a list of String paths (+some meta-data) that I will use to build the tree at the client.

I should mention that the 'file' records in my working directory are meta-data that represent json files in a mongodb datastore (they describe webpages as complex nested json). File records will be queried more frequently than folders (they don't get queried at all), and will be linked to other tables. Files have a different meaning for my app than folders, which are merely important for my working directory.

My question is what would be the best option:

• Store files and folders in separate tables
• Store files and folders in the same table (the records will be near identical), and use a FK on a joined table with 2 records "file"/"folder"

Short answer: "classic" relational databases (including MySQL) are not very good at this. Here is a good link: Managing hierarchical data in mySQL

On the other hand, WordPress is an application that does many of the things you're trying to do - and it does it all in MySQL (or equivalent RDBMS).

Look here, look especially at the "taxonomies" section.

Another approach might be to look at a graph database, like neo4J.