What are my options for table/db designs to model an organization chart.
Parent/child/siblings
root/leaves ideas?
How can depth or "layers deep" be handled?
I haven't had to think about this for awhile and all of the sudden I have
people asking me questions about using queues to simplify hiearchical or
binary tree designs to avoid recursive descent parsers. A performance thing
I hear.
Also I am becoming aware of my own intuitive "adjacency modeling" that looks
back into itself. My ID calls your ID in the same table. I think this might
be part of the recursive problem I think I want to avoid. I have worked with
some pretty nasty database designs in the past where performance got
severely compromised.
thank you for any ideas or questions to help me thing about this.
-greg

I've had good performance with the adjacency model by using a user-defined
function to navigate the hiearchy and return the list in table form allowing
the hierarchy to be joined within a query.

--
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
Enterprise Data Architect, www.Compassion.com


CREATE

-- alter

FUNCTION dbo.SubClasses

(@ClassName CHAR(25))

RETURNS @Classes TABLE (ClassID INT, ClassName VARCHAR(25), Lv INT)

AS

BEGIN

DECLARE @LC INT

SET @LC = 1

-- insert the top level

INSERT @Classes

SELECT ClassID, ClassName, @LC

FROM dbo.Class with (NoLock)

WHERE ClassName = @ClassName

-- Loop through sub-levels

WHILE @@RowCount > 0

BEGIN

SET @LC = @LC + 1

-- insert the Class level

INSERT @Classes

SELECT ClassTree.ClassID, ClassTree.ClassName, @LC

FROM dbo.Class ClassNode with (NoLock)

JOIN dbo.Class ClassTree with (NoLock)

ON ClassNode.ClassID = ClassTree.SuperClassID

JOIN @Classes CC

ON CC.ClassID = ClassNode.ClassID

WHERE CC.Lv = @LC - 1

END

RETURN

END




"Hazz" <hazz (AT) nospameroosonic (DOT) net> wrote

Adjacency modeling would suite your requirement. And it would not compromise
on performance if you have proper indexes defined on the table.
defining hierarchy would be simple, but you have to some extra work on
resolving circular references.

if you think the order of the siblings is not important, you can go ahead
with this approach. to maintain order, there is some extra cost involved.

Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet

"Hazz" <hazz (AT) nospameroosonic (DOT) net> wrote

On Wed, 1 Dec 2004 17:37:44 -0800, Hazz wrote:

Hi Greg,

If you want to explore some alternatives, google this groups for posts by
Joe Celko containg the words "nested sets model".

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Thank you so much Paul!
I am currently looking at
http://www.intelligententerprise.com...questid=527773
to follow up on a suggestion by Hugo below to google "nested sets model" .

I have gone through your code a couple of times and I realized that I need
to digest a bit at a time.
I am using SQL Server books online to help me slowly put together your
algorithm so I can understand how it works.
Would you mind adding a few sentences to help me out in case I am going down
the wrong "tree" conceptually.
You are using classes rather than employees as the compoments in the system?
You have superclass, class and a function subclass which utilizes the value
LV which is a integer value for the leaf?
What does "insert the class level" do in words? Is this all in your book so
I can leave this as an exercise for myself?
I have just been humbled again to realize that any of my SQL success stories
in the past have just been greatly minimized. ;-)
Thanks again Paul. -greg

"Paul Nielsen" <pnielsen (AT) us (DOT) ci.org> wrote

Thank you Hugo.
I am looking at
http://www.intelligententerprise.com...questid=527773
right now.
-greg

"Hugo Kornelis" <hugo (AT) pe_NO_rFact (DOT) in_SPAM_fo> wrote

thank you Av. this is all a little beyond me at this point but hopefully
these points you brought up will mean something to me in the near
uture. -greg

"avnrao" <avn (AT) newsgroups (DOT) com> wrote

Go to amazon.com and get a copy of my book, TREES & HIERARCHIES IN SQL.
It covers many different models.

I fyou use the adjacency list model, remember to add all the constraints
needed to preserve the tree structure; they are very tricky.

--CELKO--
Please post DDL in a human-readable format and not a machne-generated
one. This way people do not have to guess what the keys, constraints,
Declarative Referential Integrity, datatypes, etc. in your schema are.
Sample data is also a good idea, along with clear specifications.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!