Storaging a tree structure in a table/database
Posted by: Chris Bassett
Date: February 16, 2018 11:07PM

What is the recommended method of storing a tree structure in a database table?

For example, I have a tree structure like this:

(root level)
+-- Photos
| |
| +- 2016
| | |
| | +- Vacations
| |
| +- 2017
| | |
| | +- Vacations

(and so on)

(this laytout mimics what could be seen in with Windows Explorer in Windows
OS environments with the Folders pane.)

Basically, I want to be able to create sub-tree elements that have the same name (such as Vacations above) but them exist within different folders.

I have thought about a few ways to do this... one is using a table like this:

Table: FolderStructure
item_id bigint (PK)
parent_id bigint
item_name varchar(32)

... and so on...

So basically, the parent_id would be used to reference the parent object (parent_id is just a reference to another item_id in the same table). If an item doesn't have a parent_id specified then that item is assumed
to be a parent/root level element.

The problem with this design I have found is that it doesn't allow a multi-level structures to be created, like in my example above.

I will be storing references to physical files on a machine, in a folder structure. Each file (in the database) will need to be put into a folder of some sort (as files will not be allowed to be stored at the root level--as in, not in a folder--in the design I have come up with), but it could be just one folder deep, or a few folders deep.

This is basically going to be a system for cataloging image files and sorting them into folders.

How can model something that would let me store this structure above with the Photos example where I can have a elements a few levels deep, yet
store it in a database table? Do I need to maybe start looking at XML databases rather than your typical relational database? I am familiar with general database design, but storing data like into a structure in a table is not something I've really dealt with (at least, not with more than one level possible).

Options: ReplyQuote

Written By
Storaging a tree structure in a table/database
February 16, 2018 11:07PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.