Saving a folder hierarchy in a relational database

Question

I have objects representing folders, and I wonder if they should be represented in the database.

On the one hand, it seems that the easiest way is not to represent the objects in the folder and just store the path value for the objects contained in the folder. The problems that I see in this is that you cannot save a folder whose descendants do not contain any items, which is not too big for a deal. Also, I don't have a clear idea of ​​how to load the folder hierarchy for display (e.g. in TreeView) without loading everything into memory in advance, which is likely to be a performance issue.

An alternative is the presence of the "Folder" table with links to its parent folder. This seems to work, but I'm not sure how to allow folders with the same name until they share the parent. Should it be something that the database should relate to itself or is it something that I should just apply in business logic?

Answer 1

The idea is something like this (self-regulation):

CREATE TABLE FileSystemObject ( ID int not null primary key identity, Name varchar(100) not null, ParentID int null references FileSystemObject(ID), constraint uk_Path UNIQUE (Name, ParentID), IsFolder bit not null ) 

Answer 2

Take a look at the ERD in the middle of this page . Factorization of the hierarchy in a separate table allows you to maintain several taxonomies.

Answer 3

First ask yourself what the goal is to keep the hierarchy in the database and what functionality you get from it. Then ask to consider the work and maintenance that go for it.

If you just use it to populate a tree control, there are built-in controls that go right against the folder system. Will this work better for you? Do you get something other than this by storing it in a database? How do you plan to synchronize the database with the actual folder system, which can be changed outside the database? If you do not provide a virtual file system, it might be better to just go straight to the real thing with the appropriate paths stored in the database.

Answer 4

SQL Server has a hierarchyid data type that supports hierarchical structures. Please note that you only work in the full version.