Here's a Design Challenge: Need help designing tables for multi-level users/entities

g00ber · #1 (**permalink**) 07-12-04, 16:31

(Please see attachments)

Ok, so heres a typical application..

1) The application has multi-level modules and multi-level users.

2) A user can have access across different modules.
Ex: JOE ORANGE has access to 3 and 7.

3) A user has access to all modules (descendants) below an modules that he has access to.
Ex: ANN BLUE has access to 6, therefore he has access to 9 and 10.

4) A parent user controls the child users.
Ex: BOB GREEN contols JOE ORANGE and KEN PURPLE.

5) A parent user can only assign up to his own access to his child users.
Ex: JOE ORANGE only has access access to 3 and 7, so he cannot give access 1, 2 nor 4 to his descendants.

6) OPTIONAL A parent user can control all his descendants skipping his immediate childs.
Ex: BOB GREEN can remove access 10 to ANN BLUE without affecting JOE ORANGE.

7) OPTIONAL A user can exist under a different branch, but not within his own branch
Ex: KEN PURPLE is under BOB GREEN. KEN PURPLE can also be under ANN BLUE, but not under SUE RED. (Not shown in diagram)

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

1) Flexible
- Able to add/delete/edit modules and users

2) Data Integrity
- Ex: If JOE ORANGE loses access to 10, then all his descendants should not have access to 10.

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 for quick reports and simple user interface.

5) High-Volume Data
- My example uses 10 modules (3 levels) and 6 users (4 levels). Our real system could be using 10,000 modules (10 levels) and 2,000 users (10 levels).

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

Code:

TABLE: MODULE  
Id  Parent
--- ------
1   0
2   1
3   1
4   2
5   3
6   3
7   4
8   5
9   5
10  6

TABLE: USER
Name        Parent      Access?
----------- ----------- ------
BOB GREEN   (NONE)
JOE ORANGE  BOB GREEN
KEN PURPLE  BOB GREEN
ANN BLUE    JOE ORANGE
KIM YELLOW  JOE ORANGE
SUE RED     KEN PURPLE

MattR · #2 (**permalink**) 07-12-04, 17:31

Quick reply while still mulling over the problem. You don't really need the ID columns; the combination of Name/Parent etc. is a perfectly good unique identifier.