SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Barefoot on the Moon! silver trophy
    Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,516
    Mentioned
    51 Post(s)
    Tagged
    1 Thread(s)

    table diagram puzzle for user permissions

    I'm putting together a few tables here to keep track of user permissions, with the intention of letting users have granular control over them, and have the ability to define "sets" of permissions (such as "administrator", "reviewer", "editor", "super administrator, or whatever)

    So, the idea is to be able to define the permission within the database, and have a plain english description to go along with it which would display in the GUI.

    At the next level, taking a collection/set of different permissions, assigning a numerical value for the permission, using a status field to allow/deny a particular set be selectable as a pre-defined permission set in the GUI, and assigning an editable display name to that set of permissions.

    Then, assign that defined set of permissions to a user.


    Here's a table diagram:




    The problem with this table diagram is the permission_sets table. The combination of the set_id and perm fields should be unique, in order to only allow one perm value per set, but it's redundantly redundant (heh) to include the perm field as the foreign key in the permission_set_details table.

    Therein rests the quandary.

    Thoughts?
    Visit The Blog | Follow On Twitter
    301tool 1.1.5 - URL redirector & shortener (PHP/MySQL)
    Can be hosted on and utilize your own domain

  2. #2
    Barefoot on the Moon! silver trophy
    Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,516
    Mentioned
    51 Post(s)
    Tagged
    1 Thread(s)
    Some additional thoughts:

    It's redundant to include the perm field as the foreign key in the permission_set_details table.

    The other problem is that in the permission_set_details table, I only need *one* copy of the status and set_name fields per set. If I were to use both the set_id and perm fields as foreign keys in permission_set_details table, I would get multiple copies of those fields per set, which makes things more difficult to edit.
    Visit The Blog | Follow On Twitter
    301tool 1.1.5 - URL redirector & shortener (PHP/MySQL)
    Can be hosted on and utilize your own domain

  3. #3
    Barefoot on the Moon! silver trophy
    Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,516
    Mentioned
    51 Post(s)
    Tagged
    1 Thread(s)
    Thanks if anyone took a look at this...I managed to find a solution thanks to a friend of mine.

    Basically, I was taking an approach that built up, while this approach goes from the top down:

    http://img192.imageshack.us/img192/5...rmtablesv4.jpg
    Visit The Blog | Follow On Twitter
    301tool 1.1.5 - URL redirector & shortener (PHP/MySQL)
    Can be hosted on and utilize your own domain


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
  •