Hi,

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

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.

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