File folder sql programming

Question

I'm trying to figure out the best way to make a file folder system in sql. I'm making a website that will be using similar system as explorer in windows.

You open your c: drive and you see there some folders and files, and you open one folder and you see more files and folders there.

So what i'm asking, would i use one table for this and just point to the parent id number or what ?

I have this in my head.. You make a Primary folder, it gets the u_id=1. Then i make a file in that folder, it gets u_id=2 and p_id=1 so i know its there right? same with the folders.

This would all be in one table but i cant help thinking there is some major flaw in this..

Answer 1

What you are describing is a tree hierarchy. You are correct in using a recursive key to indicate parent-child relationships.

A good design example:

ID int (PK)

Parent int nullable (FK)

Foldername varchar(100)

Use files as per hunter's post

Answer 2

I would tackle this in a few different ways:

Add a Folders table with these columns and whatever else you need:

FolderId [INT]
ParentFolderId [NULL INT]
Key [VARCHAR(MAX)]

What the Key holds is every ParentId of the current child combined like so:

19-144-444-

What this lets you do is easily find child folders without recursion.

Add a Files table with these columns:

FileId [INT]
FolderId [INT]

For the most part, files will have much more meta data associated with them so there's no reason to overuse one table