Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011

    Unanswered: need help to design employee hierarchy


    I want to design an Employee table in DB2.I am working with AQT and dont know about my current version of DB2.
    My table has columns Emp_ID and Mgr_ID.
    Emp_ID MGR_ID
    1 2
    2 3
    3 4
    4 null

    If Employee 1 logs in then 2,3,4 should be listed and if 4 logs in then 3,2,1 should be listed.
    As it's an employee hierarchy I want to list all the managers of an employee and all the reportess of a manager.
    Using CTE i was able to do this but I am stuck with one hurdle:
    If any manager/employee leaves the organisation then it's ID will become null i.e. if value of 2 is null from the begining (when data is being populated)?
    In such situation my hierarchy will be broken because I am making Mgr_ID as the self referential key which is referencing to Emp_ID, Emp_ID is the P.K. of the table. If I go for autogenerated ID as P.K. also then to what if intially some employee has Mgr_ID as null then that autogenerated key will also want increase.
    Please suggest what can i do. This is a parent child relation table.

    I know it might be a very silly question for this dbforum but any help will be appriciated:-)

    Thanks in anticipation
    Last edited by nidhitripathi.28@; 07-20-11 at 05:36.

  2. #2
    Join Date
    Aug 2001
    Forget about the data model for a moment.

    Answer the questions(to yourself)

    If 2 leaves the organization,
    -- who is the manager is 1?
    -- on what basis do I associate 3 and 4 with 1.

    Then think about how you design the model.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jul 2011
    I know... this is not the correct way... perhaps thats y I asked a question here...
    I am very much new to db. My requirement was to design an Employee hierarchy where i will be having two columns one is EMp_ID and other Mgr_ID
    If any employee login then his immediate manager and list of all indirect managers should be available and if any manager logs in then list of his direct and indirect reportees should be available....

    Hope i made my question clear

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts