hypothetical org chart solution

H

Hazz

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
 
H

Hazz

That's embarassing. There is no treeview control in ASP.NET. That is why I
tried the IE Treeview control...which I wasn't thrilled with.
-greg
 
B

bruce barker

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)


| 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
|
|
 
H

Hazz

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
 
H

Hazz

you both are right. I went to the SQL Server programming newsgroup to start
from the ground up. -greg
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top