Show records for certain users within a company hierarchy

[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.

Thanks guys and girls.

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

http://www.SpiraxSarco.com/

 

[Smeat]

I haven't given this a lot of thought so please don't assume this is a good solution, but I would probably explore using a single table for all users and have a recursive link, i.e. each user has a manager id:

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

This structure would allow you to have an infinite number of levels in the heirarchy although a user can only ever belong to 1 heirarchy.

HTH

Smeat

 

[ca8msm]

I'd ask in forum183 (or the relevant forum for whatever database you are using). We can help later if you need assistance displaying the actual data.


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

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]

Thanks both

I already have my table structured exactly like that Smeat but I was unsure whether it was the best way or not. I also dont know how to do the recursive link so that was stopping me from getting it developed any further.

I have asked the question again in the SQL Server Programming forum.


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

http://www.SpiraxSarco.com/

 

[jmeckley]

you have a couple options to build the hierachy.
1. load all rows into memory, construct the heirachy, and dicard the remaining rows.
2. start with a single users id and execute multiple sql queries against the db to build the heirachy.

each method has it's own pros/cons. I don't think you can do this using the MS drag/drop methodology, since custom business logic is required.

some 3rd party DAL tools have a lazy load feature which solve n+1 sql statements scenarios (option 2).

is the depth of your heirachy known? example: there are only 3 levels, director, manager, user? if so this can simplify the solution because it could never be more than X levels deep.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[Smeat]

The following might help get you started on the recursive link:

SELECT 
	U.[ID] AS UserID, U.[Name] AS UserName, M.[ID] AS ManagerID
FROM
	User U
		LEFT OUTER JOIN
	User M
		ON
	U.ManagerID = M.[ID]
ORDER BY
	M.[ID]

HTH

Smeat

 

[ca8msm]

The following might help get you started on the [!]recursive link[/!]

The example you've given is known as a self-join, not a recursive link as it has a finite amount of levels which it can return data from. Obviously it may be a viable solution if the amount of levels are known, it was just the terminology which was slightly incorrect.


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

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]

The amount of levels can vary unfortunately - anything from 2 to about 8.

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

http://www.SpiraxSarco.com/

 

[ca8msm]

Looks like you will be stuck using the recursive function then, and as you are on SQL Server 2000 it does make it a bit harder than with later versions of the product.

Judging from the other responses you got on your SQL Server thread, it looks like the only way you can do this is by using the methods that were suggested in the links I gave you (the FAQ link basically covers the same methods).


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

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]

Yes I'm just looking at those now - they look very promising. I hope to get this sorted this morning. Thanks for all your help - like I said, I'll let you know how I get on.

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

http://www.SpiraxSarco.com/

 

[jmeckley]

SELECT U.[ID] AS UserID, U.[Name] AS UserName, M.[ID] AS ManagerID
FROM User U LEFT OUTER JOIN User M ON U.ManagerID = M.[ID]
ORDER BY M.[ID]

is the same as

SELECT U.[ID] AS UserID, U.[Name] AS UserName, U.[ManagerID] AS ManagerID
FROM User U
ORDER BY U.[ManagerID]

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[Smeat]

Well spotted. I didn't spend anytime checking my answer so i'm not suprised there's a mistake.

Smeat