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?

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
)