Storing a directory structure in database

Question

In my rails app a user can have a directory structure which has folders and files in sub-folders. Which is the best way to store such data ??
Also, which database offers best way to do so?

Answer 1

It's simple tree stored in sql. Either check the standard parent-child scheme or implement preordered tree traversal scheme (left-right).

Answer 2

You can store a directory tree in a single table using any SQL database, by making the table self-referential. A good example is the Windows Installer's Directory table, where you will see a structure like this:

• Directory = primary key id field, typically an integer
• Directory_Parent = "foreign key" id field, which points to the id of another Directory in the same table
• Value = string containing the directory/folder name

Your file table would then have a foreign key referencing the Directory id. To find the full path, you must follow it up the chain and build up the path from the end (right), tacking each parent directory onto the front (left). For example, the file would point to Directory id '4' with the Value 'subfolder', then you fetch the parent's value 'folder', then the parents value again until you get to the root, creating a path like /root/folder/subfolder/filename.

Answer 3

If your database supports recursive queries (either Oracle's connect by or the standard recursive common table expressions) then a self referencing table is fine (it's easy to update and query).

If your DBMS does not support hierarchical queries, then Eimantas suggestion to use a preordered tree traversal scheme is probably the best way.