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..

callisto · Accepted Answer · 2010-05-05 16:37:46Z

3

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

edited May 5, 2010 at 16:37

answered May 5, 2010 at 16:31

callisto

5,11311 gold badges55 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hunter · Accepted Answer · 2010-05-05 16:34:09Z

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

answered May 5, 2010 at 16:34

hunter

63.7k19 gold badges115 silver badges115 bronze badges

4 Comments

J. Polfer Over a year ago

I'd especially follow this method with Key if your RDBMS doesn't have a good way to facilitate recursive queries (many don't).

eski Over a year ago

so if i understand this right.. the key holds the id of everything that is under that current folder.. ? Little lost :o

hunter Over a year ago

No, the Key holds all of its ancestors

hunter Over a year ago

so if this was T-SQL you could write SELECT * FROM Folders WHERE Key LIKE '22-332-%' which will get you all children of this folder all the way down the chain without recursion

Collectives™ on Stack Overflow

File folder sql programming

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related