Import directory structure into a data structure to allow additional fields

Question

In the application we are building, a folder has a piece of data that needs to be associated with it called 'folder role'. Based on the folder role, I will be displaying all files associated with a particular role in a web page. I strongly suspect that further development will lead to additional requirements for what I'm calling metadata associated with an entire file system.

This question isn't so much about how to walk the directory and grab information, so much as it is about what data structure would be a good choice. Most folder structures will be of the following (template) form, but it could really end up being anything and any number of subfolders.

work
---projects
---assets
------clips
------music
------3d
deliver
---review
---approve
---master
finalize
---VFX
---GFX

I originally split up work, deliver and finalize into 3 database tables, but then the client informed these master folders also need to be dynamic. So now I'm really thinking that I will just store every folder and file in the directory, with path as the primary key, in a database table. I'm not sure how to keep my metadata in sync as files move around in the file system. I suspect with UPDATES, instead of just DELETE on the entire table and then INSERTs again which would lose the metadata, but suspect it's achievable.

So what table structure would be good? Thanks, any help is appreciated.

Answer 1

So, what I understand from reading the question and comments is that you need to keep in your database a list of folders and sub folders that is similar to the OS folders, except you need to add to each folder a property for folder role.

Unless I'm missing something, a simple self referencing table should be enough:

CREATE TABLE tblFolders
(
    FolderId int identity(1, 1),
    FolderName varchar(100), -- or whatever length that suits your needs
    FolderRole int, -- a foreign key to folder roles table
    FolderParentId int NULL-- a foreign key to reference the current table
    --- what ever else data you might need
)