hypothetical org chart solution

Discussion in 'ASP .Net' started by Hazz, Dec 1, 2004.

  1. Hazz

    Hazz Guest

    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
     
    Hazz, Dec 1, 2004
    #1
    1. Advertisements

  2. Hazz

    Hazz Guest

    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
     
    Hazz, Dec 1, 2004
    #2
    1. Advertisements

  3. Hazz

    bruce barker Guest

    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
    |
    |
     
    bruce barker, Dec 2, 2004
    #3
  4. Hazz

    Hazz Guest

    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
     
    Hazz, Dec 2, 2004
    #4
  5. Hazz

    Guest Guest

    As Bruce wrote..
    Its depends what they want..
    Be more specific Hazz!!
     
    Guest, Dec 2, 2004
    #5
  6. Hazz

    Hazz Guest

    you both are right. I went to the SQL Server programming newsgroup to start
    from the ground up. -greg
     
    Hazz, Dec 2, 2004
    #6
  7. Hazz

    Hazz Guest

    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.