Re: hypothetical org chart solution

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Hazz (hazz_at_nospameroosonic.net)
Date: 12/02/04 

Date: Wed, 1 Dec 2004 17:23:05 -0800

all good questions Bruce. Are recursive queries the preferred choice? And
the stack building assumes a certain DB design? That is the part I am
focusing on now. Are there db designs that don't require the recursion? Is
it because the table contains a foreign id into the same table that contains
employees whose employeeID's can be both a primary and foreign key? ie.
adjacency model? I am getting dangerous here as I am getting reacquainted
with this "problem space"
Why is tracking sibling order important? What is that for?
Again, thanks for the questions. This was a question I was asked as an
interview question and it has been years since I worked on something like
this. It seems like a worthwhile exercise to anticipate different scenarios
and possible solutions.
I can see that some static, passive org chart could be significantly
different than a more active dynamically drawn decision tree which could
potentially contain clickable nodes revealing more info about that node.
I also understand most org charts might not be more than 10-20 deep but
likely very wide spanning across departments, divisions, subsidiaries, etc.
Thanks for helping me think about this.... -greg

"bruce barker" <nospam_brubar@safeco.com> wrote in message
news:%23ASDFpA2EHA.1452@TK2MSFTNGP11.phx.gbl...
> it depends on the rendering requirements. indented list ok, or do they
> want
> fancy charts? default layout, or can the user modify. do you always start
> at
> top, or have some nav system? no html treeview will support 100,000 nodes,
> unless your users are really patient.
>
> the database designs requires a few questions
>
> 1) can an employee have more than 1 boss (say they work part time for two
> departments)
> 2) do you need to track sibling order?
> 3) does you choice of database servers support recursive queries (say db2
> or
> orcale. sqlserver require an sp build a stack to do these queries)
> 4) if a graphical layout used for rending, where are the coordinates
> stored?
>
> -- bruce (sqlwork.com)
>
>
> "Hazz" <hazz@nospameroosonic.net> wrote in message
> news:O$uQuF$1EHA.2804@TK2MSFTNGP15.phx.gbl...
> | I have been asked what I thought would be a good database design, data
> | delivery mechanism and rendering method for an org chart with as many as
> | 100,000 people. No other design specifications.
> |
> | 1. Database design? Good question.
> | Employee Table
> | EmployeeID
> | EmployeeName
> | ParentNode (BossID ??)
> | Depth(some way to tell what depth the node is at)
> | ???
> | 2. Delivery mechanism? Dataset? anything better?
> | 3 Rendering? Treeview control? GDI+?
> |
> | What stymied me originally with this question is that there are various
> ways
> | to answer this but given the basic ideas above, are there any gems or
> wisdom
> | gained by anyone having done this? I am thinking about elegant and
> simple
> | database designs that just flat out work. Also any caching ideas to
> facility
> | quick navigation through an org chart (or any binary tree)? Does the
> | Treeview work well in asp.net? Any methods need to be overridden to
> enrich
> | that control? Any special sql queries to get back from the database only
> as
> | much data as will be used without getting everything. Does it matter?
> |
> | Thank you, -greg
> |
> |
>
>