Storing directory structure in a database

Question

In my rails application, a user can have a directory structure that has folders and files in subfolders. What is the best way to store such data?
Also, which database offers the best way to do this?

Answer 1

You can save the directory tree in one table using any SQL database, making the table self-referencing. A good example is the Windows Installer Directory table , where you will see this structure:

• Directory = primary key field, usually an integer
• Directory_Parent = field "foreign key", which indicates the identifier of another directory in the same table
• Value = string containing directory / folder name

A foreign key will be added to your file table that refers to the directory identifier. To find the full path, you must follow the chain and build a path from the end (right), linking each parent directory to the front (left). For example, the file will point to the directory identifier "4" with a subfolder of "Value", then you will get the parent value "folder", and then the parent value again until you reach the root, creating a path, for example /root/folder/subfolder/filename .

Answer 2

If your database supports recursive queries (either Oracle connects or standard recursive common table expressions), then the self-reflection table is excellent (it’s easy to update and query).

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

Answer 3

This is a simple tree stored in sql. Either check the standard parent-child scheme, or implement a pre-ordered tree traversal scheme (from left to right).