SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Design help pls		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ann
Posting Yak Master

215 Posts
Posted - 01/17/2007 :  14:14:27  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts
Posted - 01/17/2007 :  15:50:16  Show Profile  Visit nr's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000