Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2010
    Posts
    2

    Unanswered: Trouble with Table Design Choices

    I'm having trouble figuring out how to model a few tables. I'm trying to find the tidiest solution possible, so perhaps someone has a definitive, tested-and-true way of achieving this:

    Users, Groups and Permissions

    Users and groups can each have multiple permissions set to them.

    This is a simplified mock-up of what I have so far:
    Code:
    USER
      id        INT, AUTO_INCREMENT, NOT NULL
      username  VARCHAR(32), NOT NULL
      password  CHAR(32), NOT NULL
      level     INT, NOT NULL, DEFAULT 0
    
    GROUP
      id        INT, AUTO_INCREMENT, NOT NULL
      name      VARCHAR(32), NOT NULL
    
    USER_GROUP
      id        INT, AUTO_INCREMENT, NOT NULL
      user_id   INT, NOT NULL
      group_id  INT, NOT NULL
    
    PERMISSION
      id        INT, AUTO_INCREMENT, NOT NULL
      code      VARCHAR(255), NOT NULL
    Now, what would be the best way to associate USERs and GROUPs with PERMISSIONs. I could create USER_PERMISSION and GROUP_PERMISSION tables, but is there a tidy way to merge these to a single table? The tidiness issue I have is with needing either separate user_id and group_id columns, and somehow requiring one to be NOT NULL (but not both) at a given time. The other option is a single usergroup_id column, and another column to designate whether it's a user or group id (ENUM, is_group BOOLEAN, etc.)

    TL;DR: Should I make both USER_PERMISSIONS and GROUP_PERMISSIONS tables, or is there an easier way?
    Last edited by tomcatexodus; 10-11-10 at 14:20.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tomcatexodus View Post
    I'm trying to find the tidiest solution possible
    well, since you asked, here's one way...

    this might not appeal to you, but if you put both groups and users into the same table, then the permissions are easier to handle, yes?

    the only thing to watch for is making sure that no on can sign on with a group id password

    you would still need the user_group table, as one user might belong to more than one group
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2010
    Posts
    2
    Interesting solution, never thought of doing it that way. However, you're right, I don't want to combine the USER and GROUP tables. Either way, thank you

    I'm sort of wondering if there's a general solution for issues like this, the more general question being:
    How does one relate similar fields (IDs for example) from multiple tables in another table?

Tags for this Thread

Posting Permissions

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