SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jul 2004
    Location
    Collegeville, PA
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    (Please see attachment)

    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 Images Attached Images

  2. #2
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    Middle Earth
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My suggestions,
    1. A user shouldn't have child users, but you can have several management levels to assign to different users.

    2. Move the field "access" out of the tabel "user" to a seperated table with two fields:
    user_id module_id

    I think these two changes can meet all your requirements and make the concept more clear, for the speed issue, you can load all user/module information into the memory up front.

  3. #3
    SitePoint Member
    Join Date
    Jul 2004
    Location
    Collegeville, PA
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by hurricane_sh
    1. A user shouldn't have child users, but you can have several management levels to assign to different users.
    That removes the feature of admin users having sub-admins that have sub-sub-admins, etc..

    Basically, we want our support team (LEVEL 1) taking care of customer admins (LEVEL 2) who takes of their division managers (LEVEL 3) who takes care of their department managers (LEVEL 4) who take care of their users (LEVEL 5).

    In some cases, there are no divisions, depending on the customer.
    In some cases, there are no sub-admins. Support team will handle all the user for the customer.


Bookmarks

Posting Permissions

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