Group tree

[verifier]

Hello

Im creating an app that should organize data under different groups. And each can have X sub groups. A group can only have one parent.

GroupTable
=========
groupId
parentId
name

Pretty straightforward, right?

Lets say that I got these groups:

MyCompany::Stockholm::Support::Director
MyCompany::Stockholm::Support::Technician
MyCompany::Stockholm::Sales::Salesman
MyCompany::Stockholm::Sales::Sales support

MyCompanygot id 1, parent 0
Stockholm 2 with parent 1
Support 3 with parent 2
Director 4 with parent 3
Technician 5 with parent 3
Sales 6 with parent 2
Salesmen 7 with parent 6
Sales support 8 with parent 6

How do I list all persons in Stockholm? Or how do I list all in sales department.

Are there a better database design for this task?

[TheCPUWizard]

Is the depth fixed (as shown in your example) or is it variable [for different entries]?

This makes a big impact on implementation [fixed can be done much simpler than variable, but variable can always handle fixed]

[verifier]

It's different for each customer (variable ).

[TheCPUWizard]

OK, then what you need to go is a series of queries agains the database....

First you query by name to get a list of all ID's associated with that name [e.g. Stockholm will return 2]

Then you start a loop where you query for all records with a parent ID of two, you have to keep a list of all of the group ID's and names returned.

If a specific ID returns no rows when it is used as a perent ID, then it is a leaf node, and should be appended to the output.

This approach works well provided you are only listed in leaf nodes [people in your case]. It will NOT work if you need to determine information such as "What are all of the departments in the Stockholm office?" since the nodes in the tree are not associated with a "level".

Hope this helps.

ps: Shameless commercial plug.... In 1986 Dynamic Concepts developed its DynaSearch product line for handling information which does not fit well with fixed relational structures, further information can be obtained by e-mailling: info@dynamicconcepts.us

[verifier]

Ok. thanks.
Just wanted to check if there were a smarter way to do it.

[hspc]

More Trees & Hierarchies in SQL (http://www.sqlteam.com/item.asp?ItemID=8866)