folder-like structure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

folder-like structure

Author

Topic

Husko
Starting Member

4 Posts

Posted - 2004-06-14 : 12:50:54

Hi. my first post here an man am I glad i found this forum becuase I'm stuck bad lol.

I'm trying to duplicate a file/like structure in a sql database for a vb.net program. I have a fairly good idea of relational databases(I think) but I'm wondering how i can simulate a file/folder like structure for my program.

I have a clients table. and I need to create categories for each client and each category can have a sub category. each category will point to another table where I will store images. storing these images on the hard drive is not an option. I'll also need to allow these categories to be shuffled around.

I'm having a really hard time visualizing how I can do this. will I have to create each category table on the fly and create the releationships? Could I use some sort of xml file to define the relationships to keep the database structure simple? what sort of things would you do for this problem?

Please help...

Thank you!

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-14 : 12:55:28

I would suggest


USE Northwind
GO

CREATE TABLE myClient99 (ClientId int IDENTITY(1,1), Clientname vatchar(255))
GO
CREATE TABLE myCategory99 (CategoryId int IDENTITY(1,1), Categoryname vatchar(255))
GO
CREATE TABLE myCategoryRel99 (ParentCategoryId int, ChildCategoryId int)
GO
CREATE TABLE myClientCategory99(ClientId int, CategoryId int)
GO

Brett

8-)

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-06-15 : 02:14:08

I have used this to do a folder structure before. Worked well http://www.sqlteam.com/item.asp?ItemID=8866

Damian

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-06-15 : 17:21:03

Merkins link will tell you all you need. One single table will be your best bet I belive, but you might face some performanceissues if the tree-structure gets too deep.

Husko
Starting Member

4 Posts

Posted - 2004-06-16 : 07:42:11

Wow, Thanks for the link. This is great. Now I have to figure out how I can populate this to a tree view :)

Husko
Starting Member

4 Posts

Posted - 2004-06-16 : 08:42:23

Actually, the second part is missing?

"In Part 2 of this article I'll cover all the necessary tree operations that are needed (add, delete, move, promote) and various queries (all superiors, all descendants, paths, org levels, etc.) and some other neat tricks you can do (multiple independent trees in the same table, genealogy trees)"

Being I'm new to this sort of SQL programming, I really need this second part...

Any Ideas where else to look?

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-06-16 : 08:52:46

I wouldn't hold your breath for the second part... but pretty much everything you need is in the first part.

Damian

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-16 : 09:08:28

I have an example that I use that supports multiple parents: http://www.seventhnight.com/treestructs.asp

Let me know if you like and would like some additional example queries...

Corey

Husko
Starting Member

4 Posts

Posted - 2004-06-16 : 11:35:49

WOW Cory, I was able to understand the link listed above becuase of the step by step walk through example, but lol, no offense, I'm no way near the level to understand your example. Have time for a walk through? LOL

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-16 : 11:43:12

what you need to know??

I'm only at work

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources