Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Design help pls

Author  Topic 

ann
Posting Yak Master

220 Posts

Posted - 2007-01-17 : 14:14:27
I need to create a table(s) whereby I am storing managers and employees - the issue is that an employee can be both a manager and an employee. In other words, I can have a top level manager that has employees under them and then those employees/managers have employees under them - to the nth level. Example: director has employees under him who are also managers for diff. departments. Those employees\managers have employees under them as well. In the end I will need to be able to query a manager and find all employees associated to that manager - further I will need to be able to also query a manager the employees that fall under him and any employees that fall under them.
Any ideas on what is the best way to acheive this?
Many thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-17 : 15:50:16
Have a person table. This might have attributes like director, manager which would describe what they are allowed to do.
Another table
Person_id, Child_Person_id, relationship (startdate, end_date)
This shows relationships like manages, directs.

v2005 gives you CTE's which enable you to read hierarchies - there areexamples in bol.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -