Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    8

    Design Challenge: Need help designing tables for multi-level users/security/modules

    (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
    Attached Thumbnails Attached Thumbnails usermodule.jpg  
    Last edited by g00ber; 07-13-04 at 12:28. Reason: A more simple example..

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    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.
    Thanks,

    Matt

  3. #3
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Does the Entity structure have to be recursive at all?

  4. #4
    Join Date
    Jul 2004
    Posts
    8
    Quote Originally Posted by certus
    Does the Module structure have to be recursive at all?
    No.
    Example: There will be only be one instance of MODULE 4 in the entire system.
    Last edited by g00ber; 07-13-04 at 12:24.

  5. #5
    Join Date
    Jul 2004
    Posts
    8
    I modified my original post so it uses a more simple example. Please see the attachments.
    Last edited by g00ber; 07-13-04 at 10:43.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •