Storing folder hierarchy in relational database

Question

I have objects representing folders and I'm wondering if they should be represented in the database.

On the one hand it seems like the easiest way would be to not represent folder objects and just store a path value for objects contained in a folder. Problems I see with this is that you can't persist an folder whose descendants do not contain any items, which isn't too big of a deal. Also I don't have a clear idea of how to load the folder hierarchy to display (such as in a TreeView) without loading everything into memory upfront, which would probably be a performance issue.

The alternative is to have a "Folder" table with references to its parent folder. This seems like it should work, but I'm unsure how to allow folders with the same name as long as they do not share a parent. Should that even be something the DB should be concerning itself with or is that something that I should just enforce in the the business logic?

Radiotelegram

It's easy to implement with new HIERARCHYID filed in SQL Server 2008 (on DB side) – Moulding

Hmm, is that available in SQLCE? – Radiotelegram

@Davy8, in all versions (inlcuding express) except SQL CE. – Moulding

+1 Good question. Benefits of using a SQL database - performance-wise to store/process a hierachy. – Blocky

Answer 1

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

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
)

Milka

unique key :), not necessary, it's just part of the constraint name. – Milka

cool thanks :) I know it's just part of the name, but most common sql naming convention prefixes are unknown to me as most of the OO world tends to shy away from hungarian notation which seems fairly popular in the relational world – Radiotelegram

Answer 2

Take a look at the ERD in the middle of this page. Factoring out the hierarchy into a separate table permits you to support multiple taxonomies.

Courageous

I seems the link is broken. – Glioma

Answer 3

SQL Server has a hierarchyid data type that has support for hierarchical structures. Works only in the full version, mind you.

Bounty

Answer 4

First ask yourself what the purpose is of keeping the hierarchy in the database and what functionality you gain by that. Then ask consider the work and maintenance that goes into doing it.

If you're simply using it to fill a tree control, there are built-in controls that go directly against the folder system. Would that work better for you? Do you gain something beyond that by storing it in the database? How do you plan to keep the database in sync with the actual folder system, which can be changed outside of the DB? Unless your providing a virtual file system it may be better to just go directly against the real thing with relevant paths stored in the database.

Harlandharle