Creating a Folder/File relationship in SQL Server

I have a task to create a sql server database table that will accommodate up to 30 levels of subfolders under a parent folder for each user in the department.  

As a visual please see attached image.

As a user I can click on the Accounting link which would be the parent folder and I should be able to pull back all the Children Folders  of that parent folder that have my userid linked to it.  In doing some research, I am finding that there is a new datatype called hierarchyid.  I am wondering would that be feasible for me to use as I will have to create a structure in the database for each one of the left side categories.  

As I understand the task, there will be a parent node which will be the department.  The user can create a max of 30 children under the parent.    For example:

Accounting  -  Could this be a hierarchyid
        NewFolder  -  user 132 created this folder
        NewFolder -  user 400 created this folder

CategoryManagement -  Another hierarchyid?
       NewFolder  -  User 100 created this folder
       NewFolder  -  User 200 created this folder


I think my question is if the hierarchyid datatype would work for this type of structure and if so, how would I create a new hierarchyid for each Department.

I know this might be confusing, but any help would be appreciated.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
In my opinion you can do that easily with a database structure like below where to store all this info and best is to put a CONSTRAINT on the folderlist table to limit max 30 DepartmentId,UserId unique combinations AND a UNIQUE CONSTRAINT on UserId,FolderNamePath so they cant have duplicate name folders:


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.