Show records for certain users within a company hierarchy 1

[FesterSXS]

Hi,

I am trying to get my head around this problem. I am designing an application that allows our sales force to login and do a multiple choice test to assess their knowledge of our company's products.

The problem I have is that I need to return test results for all people that sit beneath a particular director/manager within the company.

For example:

            o Director 1              o Director 2
           / \                       / \
          /   \                     /   \
   Mgr 1 o     o Mgr 2       Mgr 3 o     o Mgr 4
        / \   / \                 / \   / \
       o   o o   o               o   o o   o
Users  1   2 3   4               5   6 7   8

When a director or manager logs in, they will want to be able to see records for all the users that are beneath them. So Director 1 should only see records for Managers 1 & 2 and Users 1-4. Director 2 should only see records for Manager 3 & 4 and Users 5-8. Manager 2 should only see records for Users 3 & 4.

I am unsure of the best way to structure my users table in the database.

Should all users, managers, directors be in a single table? Managers need to take this test as well so they could also be considered as users.

How should I structure the SQL query to return all the relevant records based on the ID of the logged in user?

I'm not after a complete solution, just need some guidance really on where to start.

I asked this in the ASP.NET forum and I've had a suggestion that all the users/managers/directors should be in a single table:

ID     Name         ManagerID     UserLevelID
1      Director 1   NULL          3
2      Mgr 1        1             2
3      User 1       2             1
4      Mgr 2        1             2

Is this an efficient solution - and if so, how would the SQL look to retrieve all the records beneath Director 1 for example?

Thanks guys and girls.

Tony

Tony
---------------------------------------

http://www.SpiraxSarco.com/

 

[ca8msm]

What version of SQL Server are you using?


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[FesterSXS]

Hello again

I'm using SQL Server 2000

Tony
---------------------------------------

http://www.SpiraxSarco.com/

 

[ca8msm]

OK, if you are using SQL Server 2000, and you are using the structure you mentioned in thread855-1450709, then here are some methods of retrieving the data:

http://www.mssqltips.com/tip.asp?tip=938

There's easier methods of implementing this in SQL Server 2005 as well, however, I'd wait to see what other people suggest before going down this route as they may have other methods that are better than this.




-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[FesterSXS]

Thats great thanks - I'll take a look at that link and let you know how I get on.

Tony
---------------------------------------

http://www.SpiraxSarco.com/

 

[SQLSister]

We also have an FAQ on Hierarchial data

http://www.tek-tips.com/faqs.cfm?fid=5322

"NOTHING is more important in a database than integrity." ESquared

 

[FesterSXS]

ca8msm, that link you provided was exactly what I needed - many thanks

Tony
---------------------------------------

http://www.SpiraxSarco.com/