Design Challenge: Tables for multi-level users/entities 1

[g00ber]

               ENTITY
             /               COM1           COM2
        /   \                  CUST1  CUST2         CUST3
     /   \     \          /      DIV1   DIV2  DIV3    DIV4     DIV5
  /  \     \      \      \      /   DPT1  DPT2  DPT3   DPT4  DPT5  DPT6   DPT7


               USERS
             /               MARK           JOHN
        /   \                  DAVE    PHIL         BILL
     /   \     \          /     PETE   JOEL  MARY    JANE    KATE
  /  \     \      \      \     /   KRIS  NOEL  JUDY  GAIL  ADAM  RYAN  BOB

Ok, so heres a typical application..

1) The application has multi-level entities.

2) The application multi-level users

3) A user can have access across different entities.
Ex: MARK has access to CUST1 and DIV4.

4) A user has access to entities below an entity that he has access to.
Ex: MARK has access to CUST1, therefore he has access to DIV1 and DIV2 and its descendants.

5) A parent user controls the child users.
Ex: MARK controls DAVE and PHIL.

6) A parent user can only assign up to his own access to his child users.
Ex: MARK has access to CUST1 and DIV4, therefore he can only give DAVE and PHIL access to CUST1 and DIV4, not CUST2 nor DIV5.

7) OPTIONAL A user can exist under a different branch.
Ex: PETE is under DAVE. PETE is also under BOB (not shown in diagram)

8) OPTIONAL A parent user can control all his descendants skipping his immediate childs.
Ex: MARK can remove DPT1 access away from PETE, without affecting DAVE.

Can someone help me design some tables so I can fit this model?
Please consider the following:

1) Flexible
- Able to add/delete/edit new entities, new users

2) Data Integrity
- Ex: If MARK loses access to CUST1, then all his descendants should not have access to CUST1

3) Relatively Fast
- Checking access will be done quite often throughout the application.

4) Readability
- Simple structure for both the end-user and the programmer to help in reports and user interface.

Here's what I've come up with so far.

TABLE: ENTITY 
Id  Name  Parent
--- ----- ------ 
0   ROOT  (NONE)
1   COM1  ROOT
2   COM2  ROOT
3   CUST1 COM1
4   CUST2 COM1
5   CUST3 COM2
6   DIV1  CUST1
7   DIV2  CUST1
8   DIV3  CUST2
9   DIV4  CUST3
10  DIV5  CUST3
11  DPT1  DIV1
12  DPT2  DIV1
13  DPT3  DIV2
14  DPT4  DIV3
15  DPT5  DIV4
16  DPT6  DIV5
17  DPT7  DIV5


TABLE: USER
Id  Name  Parent Access?
--- ----- ------ ------
1   MARK  (NONE)
2   JOHN  (NONE)
3   DAVE  MARK
4   PHIL  MARK
5   BILL  JOHN
6   PETE  DAVE
7   JOEL  DAVE
8   MARY  PHIL
9   JANE  BILL
10  KATE  BILL
11  KRIS  PETE
12  NOEL  PETE
13  JUDY  JOEL
14  GAIL  MARY
15  ADAM  JANE
16  RYAN  KATE
17  BOB   KATE

 

[ESquared]

On Tek-Tips:

T-SQL to establish hierarchy

On other sites:

Trees in SQL
Versatile High Performance Hierarchies in SQL Server
More Trees & Hierarchies in SQL
Trees in SQL: Nested Sets and Materialized Path (highly technical)

 

[SQLSister]

Esquared, could you throw those links into an FAQ onthe SQL programming forum and maybe this one? IT would be helpful to be able to just refence these articles the next time this issue comes up.

Questions about posting. See faq183-874

 

[ESquared]

Sure! This one does come up a lot, doesn't it? There was one more article I read a week ago that had some great pictures for nested set and I could NOT find it just now, I even searched my browser history.

 

[ESquared]

There is now an FAQ for this subject in both forums.

How do I do ___________ with a Hierarchical structure? faq669-5326

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[ceh4702]

You can do this different ways. One way would be to determine where in the netwok the user was and use that for access, Or to use user groups.

If you do not like my post feel free to point out your opinion or my errors.