Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Storing a directory structure in database

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?

like image 823
Jatin Ganhotra Avatar asked Jul 27 '10 11:07

Jatin Ganhotra


People also ask

How do you find the structure of a directory?

You need to use command called tree. It will list contents of directories in a tree-like format. It is a recursive directory listing program that produces a depth indented listing of files. When directory arguments are given, tree lists all the files and/or directories found in the given directories each in turn.

Is a directory a tree structure?

Structure. The directory is structured in the form of a tree. It also has a root directory, and every file in the system has a unique path. A directory within a tree-structured directory may contain files or subdirectories.

What is a folder with a directory structure?

What is directory structure? The directory structure is the organization of files into a hierarchy of folders. It should be stable and scalable; it should not fundamentally change, only be added to. Computers have used the folder metaphor for decades as a way to help users keep track of where something can be found.


3 Answers

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.

like image 155
ewall Avatar answered Sep 21 '22 09:09

ewall


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.

like image 32
a_horse_with_no_name Avatar answered Sep 23 '22 09:09

a_horse_with_no_name


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

like image 20
Eimantas Avatar answered Sep 23 '22 09:09

Eimantas