SQL for file structure

I am trying to develop a database simulating a file structure on the harddisk. Some files and folders stored in the db other words.
The problem is that i cannot figure out how to build the database and how to write the SQL.
I started to build a table with id´s for every folder and subfolders, giving the subfolder the parentID of the mainfolder. Can i do like that? In that case, how to write the query sorting in in the right way? Some kind of recursive query maybe?
I am grateful for help and DB/SP examples.

Best regards
Patekw

You could do this many different ways. Probably the best way would be to have 2 tables (Folders and Files) and then just relate the together.

Here is an easy 1 table way to do it.
File Table (For SQL Server)

FId (uniqueIdentifier)
FolderName (varchar(50))
FileName (varchar(50))
ParentFolderId (uniqueIdentifier)
IsFile (bit) <---- determines if its a file (1) or a folder (0)

example structure

Thanks! That may work, but how to query the DB for the right output for folders and files?

I think that I am replying to your question pretty late, but I just saw this today. Anayway... here are 2 ways of writing the recursive query that you want.. I am not sure about the syntax of the 2nd answer. You might verify it before using...

1.
select folderName, fileName, isFile
from File_table
startwith ParentFolderID isnull
connectbyprior FId = ParentFolderId;

2.
with search as(
select FId, folderName, FileName, isFile from File_table root, ParentFolderId
unionall
(select child.FId, child.folderName, child.FileName, child.isFile, child.ParentFolderId
from search parent, File_table child
where child.ParentFolderId = parent.FId))
select*from search;

I would think you are a little late answering this question...considering he asked it almost a year ago.

In the future, please don't resurrect dead threads.