How to do in a DB

[Drakkon]

I want to createa a psuedo-structure in a DB making it so items can be categorized in folders like it would on a computer.

Now I'm thinking i could store things in a structure like:

FOLDER
---------
FOLDER_ID
FOLDER_NAME

ITEM
---------
ITEM_ID
FOLDER_ID
ITEM_NAME

and then folder name would be things like: Root/FOLDER1/, Root/FOLDER2, Root/FOLDER1/SOMEOTHERFOLDER
anc could keep create subfolders this way.

So is this the way most do things like this or am i way off? I've never done anything like this - just utilize the file system or have a set number of "sub" cetegories so i could be totally off my rocker.

 

[brandonb]

That would work. However, this is how I'd probably do it (not necessarily the best way to do it either)

FOLDER
---------
FOLDER_ID
FOLDER_NAME
PARENT_FOLDER_ID

That way you can build a tree with FOLDER table...

FOLDER_ID = 1
FOLDER_NAME = "Root"
PARENT_FOLDER_ID = 0

FOLDER_ID = 2
FOLDER_NAME = "Folder1"
PARENT_FOLDER_ID = 1

ITEM_ID = 1
FOLDER_ID = 2
ITEM_NAME = "File"

That way when you check folder id from the item, you get the sub folder. That can link to the parent by climbing the tree. Keep traversing up the tree until you hit PARENT_FOLDER_ID = 0, and build the path as you go...

However, your mileage may very and that might be more complex than you need.

 

[Drakkon]

nope that would be a better solution now that i look at it 😀 thanks!
Anyone else have any input?

 

[mooncancook]

since you can have unlimited level of folders...

ItemID
ItemType -- file or folder
ItemLevel -- Integer, defines the folder/file level
Item_RootID -- the ItemID of its root so all subfolder can relate to its root folder
ItemName -- Folder/File name

that's just my basic idea, need tweaking of course