(Please see attachments before reading)
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 a module 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.
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 and 6 users. Our real system could be using 10,000 modules and 2,000 users.
Here's what I've come up with so far.
Ugh!! this CODE tag doesnt work!! its not aligning my words
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 a module 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.
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 and 6 users. Our real system could be using 10,000 modules and 2,000 users.
Here's what I've come up with so far.
Code:
[font=Courier New]TABLE: MODULE [/font]
[font=Courier New]Id Parent[/font]
[font=Courier New]--- ------[/font]
[font=Courier New]1 0[/font]
[font=Courier New]2 1 [/font]
[font=Courier New]3 1[/font]
[font=Courier New]4 2[/font]
[font=Courier New]5 3[/font]
[font=Courier New]6 3[/font]
[font=Courier New]7 4 [/font]
[font=Courier New]8 5[/font]
[font=Courier New]9 5[/font]
[font=Courier New]10 6[/font]
[font=Courier New][/font]
[font=Courier New]TABLE: USER [/font]
[font=Courier New]Name Parent Access?[/font]
[font=Courier New]----------- ----------- -------[/font]
[font=Courier New]BOB GREEN (NONE)[/font]
[font=Courier New]JOE ORANGE BOB GREEN[/font]
[font=Courier New]KEN PURPLE BOB GREEN[/font]
[font=Courier New]ANN BLUE JOE ORANGE[/font]
[font=Courier New]KIM YELLOW JOE ORANGE[/font]
[font=Courier New]SUE RED KEN PURPLE[/font]