Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,536
    Thanks
    45
    Thanked 259 Times in 256 Posts

    Forum permissions: indiv columns or binary string?

    I'm looking for some advice on how I should handle this. I've been developing a forum for quite a while now, and for permissions, currently I have 3 tables (one for general, one for user, and one for group) for permissions, each with a number of matching columns. A 1 indicates access, 0 means don't care, and -1 means deny.

    I've been looking at it, and I realize that I need to add another permission to the system. That means adding a column to each table, as well as a view that merges the 3 tables. Really, the number of permissions won't change that much (this is the first one I've added in months, and I have a few coming soon as well, as a group), so its rather static.

    The issue is, is this the best way to do it? I've been considering if I should store a decimal (either a binary to dec conversion, or storing 0-3) or hex string, then when processing, get the string, convert to binary, and figure out what those permissions mean.

    One of the reasons I'm hesitant to go this way is currently, as there are 3 permission types (general, group, user, in ascending order of priority), I first have to get all the users possible permissions for the forum in question and any parent forums, figure out what the actual permissions level for each forum is, then check if any parent permissions supersede the current permissions. If I were to store the data as a string, I'd also have to convert the data to binary/a useable format, also involving translating what each character position represented in terms of what permission its granting.

    I've looked at permissions in stuff like phpBB, and they store as a string best as I know, so there is obviously something to this method, but I'm not quite getting it.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,595
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    On top of the objections to the string/et al. scheme that you raised, how about this one: Would make it harder to write the *query* to do all that. And it should clearly be done in the query, not in PHP/ASP/JSP code.

    Now, you *can* do "bit twiddling" in MySQL. But in my opinion it's usually best to use MySQL's ENUM data type, especially if you want your tables and queries to be more human-readable.

    But there's not one size fits all answer to this one.

  • #3
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,536
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Quote Originally Posted by Old Pedant View Post
    Would make it harder to write the *query* to do all that. And it should clearly be done in the query, not in PHP/ASP/JSP code.
    I'm not sure I follow. I'm not even sure how you could write a query to do what I described?

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,595
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    I'd have to see the list of tables and fields to write the query.

    I'm not even sure why you have 3 tables (general, user, group) instead of one with a type indicator field.

  • #5
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,536
    Thanks
    45
    Thanked 259 Times in 256 Posts
    I can give my table creates and some test data if you'd like, but I guess I'm more curious about the methodology. Then again, maybe a mock query would explain the methodology.

    I have 3 tables, as I was advised that foreign keys should not point to a foreign situationally (I was told that was bad design). So given the user table has a reference to userID, it should be separate from the group table which references groupID. Also, at one point I was building the tables as InnoDB, and was using the InnoDB foreign key constants (which means I had to use 3 tables). I am using MyISAM now, and doing the constraints checks though PHP.
    Last edited by Keleth; 08-11-2011 at 10:18 PM.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,595
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    Oh, I see why the 3 tables, then. Other ways around that, but your answer is the traditionally correct one. Sorry.

    I don't have time to tackle this today. Maybe in a couple of days.

  • #7
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,536
    Thanks
    45
    Thanked 259 Times in 256 Posts
    No worries. If you don't mind at some point, expanding on what you mean by "other ways around that", I'd appreciate it. I've gotten a lotta advice on DB design from here and other sources, and more then a few times, it conflicts, so your thoughts to it would be appreciated. In lieu of further advice, I've included the create statements for your consideration.

    Code:
    CREATE TABLE IF NOT EXISTS `forums_permissions_general` (
      `forumID` int(11) NOT NULL,
      `read` tinyint(1) NOT NULL,
      `write` tinyint(1) NOT NULL,
      `editPost` tinyint(1) NOT NULL,
      `deletePost` tinyint(1) NOT NULL,
      `createThread` tinyint(1) NOT NULL,
      `deleteThread` tinyint(1) NOT NULL,
      `addRolls` tinyint(1) NOT NULL DEFAULT '-1',
      `addDraws` tinyint(1) NOT NULL DEFAULT '-1',
      `moderate` tinyint(1) NOT NULL DEFAULT '-1',
      PRIMARY KEY (`forumID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    
    CREATE TABLE IF NOT EXISTS `forums_permissions_groups` (
      `groupID` int(11) NOT NULL,
      `forumID` int(11) NOT NULL,
      `read` tinyint(1) NOT NULL,
      `write` tinyint(1) NOT NULL,
      `editPost` tinyint(1) NOT NULL,
      `deletePost` tinyint(1) NOT NULL,
      `createThread` tinyint(1) NOT NULL,
      `deleteThread` tinyint(1) NOT NULL,
      `addRolls` tinyint(1) NOT NULL DEFAULT '-1',
      `addDraws` tinyint(1) NOT NULL DEFAULT '-1',
      `moderate` tinyint(1) NOT NULL DEFAULT '-1',
      PRIMARY KEY (`groupID`,`forumID`),
      KEY `forumID` (`forumID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    
    CREATE TABLE IF NOT EXISTS `forums_permissions_users` (
      `userID` int(11) NOT NULL,
      `forumID` int(11) NOT NULL,
      `read` tinyint(1) NOT NULL,
      `write` tinyint(1) NOT NULL,
      `editPost` tinyint(1) NOT NULL,
      `deletePost` tinyint(1) NOT NULL,
      `createThread` tinyint(1) NOT NULL,
      `deleteThread` tinyint(1) NOT NULL,
      `addRolls` tinyint(1) NOT NULL DEFAULT '-1',
      `addDraws` tinyint(1) NOT NULL DEFAULT '-1',
      `moderate` tinyint(1) NOT NULL DEFAULT '-1',
      PRIMARY KEY (`userID`,`forumID`),
      KEY `forumID` (`forumID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    So the if someone had a general permission to 'addRoll' of -1, group of 0, and user of 1, the end permission is 1. If it were general 0, group -1, user 0, it'd be -1, etc.
    Last edited by Keleth; 08-11-2011 at 10:33 PM.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,595
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    Okay, and do you look at more than one of those permissions at a time?

    That is, if a user is trying to edit a post do you also look at the "deletePost" flag for some reason?

    And by the by, even when I use MYISAM tables, I still put in the foreign key REFERENCES as much for self-documentation as anything. That is, in your forums_permissions_users table, I would have used
    Code:
        forumID INT REFERENCES forums(forumID),
    or whatever is appropriate. We had massive MyISAM tables last place I worked and *always* did that, so that others who came along later would know what the relationships were without having to dig. Obviously for a one person project, it's not so important, but I think it's not a bad habit to get into.

  • #9
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,536
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Fair point. I never bothered as, as far as I know, MyISAM does nothing with that info, but even just as referential info rather then a comment, that makes sense. I'll get into the habit.

    And often, yes, I do look at multiple permissions. For example, when viewing a thread, its necessary to check 'read', 'write', and 'moderate'.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,595
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    Okay, step 1 is to change all of those TINYINT fields from NOT NULL to NULL.

    And then do an update in all tables setting the values to NULL if they are currently zero.

    And then:
    Code:
    SELECT 
        IFNULL( U.`read`, IFNULL( G.`read`, IFNULL( A.`read`, 0 ) ) ) AS readPermissions,
        IFNULL( U.`write`, IFNULL( G.`write`, A.`write`, IFNULL( A.`write`, 0 ) ) ) AS writePermissions,
        IFNULL( U.`moderate`, IFNULL( G.moderate, A.moderate, IFNULL( A.moderate, 0 ) ) ) AS moderatedPermissions
    FROM forums_permissions_general AS A
    LEFT JOIN forums_permissions_groups AS G
         ON A.forumID = G.forumID AND G.groupID = $gid
    LEFT JOIN forums_permissions_users AS U
         ON A.forumID = U.forumID AND U.userid = $uid
    WHERE A.forumID = $fid
    See why? Now any NULL values in USER or GROUP level are ignored in favor of the value in the ALL (General) table. [I guess you could leave the zeroes in the General table and eliminate the last IFNULL, but I like consistency.]

    Also, if there simply isn't any matching record at the USER or GROUP level, you also get a NULL, so no record means the same thing as no special considerations for the given level.

    Naturally, you can join other table(s) to the General table (presumably using INNER JOIN) if you wish. I show that you are passing in the $gid, $uid, and $fid values, but they could easily come from your Forums and Users tables (whatever they are named) instead.

    So...

    Looks like I'm voting that you leave things as they are!

    Using strings would be much more complex. As would using bit patterns. You don't get to take advantage of NULL meaning "doesn't matter."

  • Users who have thanked Old Pedant for this post:

    Keleth (08-13-2011)

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,595
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    And a postscript: You don't *HAVE* to change your bit fields to NULL.

    You *could* do
    Code:
    SELECT 
        IFNULL( IF(U.read=0,NULL,U.read), IFNULL( IF(G.read=0,NULL,G.read), A.read 0 ) ) AS readPermissions,
    Not much uglier. Should work fine.

    Could also write it thus:
    Code:
    SELECT 
        IF( IFNULL(U.read,0)<>0, U.read, IF( IFNULL(G.read,0)<>0, G.read, A.read ) ) AS readPermissions,
    Huh...now that I write that, maybe I like it best. And you can leave your BIT fields as NOT NULL.

    Fun stuff.

    In that last one, the subexpression IF ( IFNULL(U.read,0)<>0, x, y ) is just a shorter
    way of writing IF(U.read IS NOT NULL AND U.read <> 0, x, y ) which would work as well, of course.

    Anyway, several choices.

  • Users who have thanked Old Pedant for this post:

    Keleth (08-13-2011)

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,595
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    Finally, you could of course use CASE WHEN in place of the nested IF's et al.
    Code:
    SELECT 
        ( CASE WHEN U.read IS NOT NULL AND U.read <> 0 THEN U.read
               WHEN G.read IS NOT NULL AND G.read <> 0 THEN G.read
               ELSE A.read END ) AS readPermissions,
    etc.
    Have no idea which would perform best. Now that I read the CASE WHEN version, I think it's clearer.

    And of course it could also be written as
    Code:
    SELECT 
        ( CASE WHEN IFNULL(U.read,0) <> 0 THEN U.read
               WHEN IFNULL(G.read,0) <> 0 THEN G.read
               ELSE A.read ) AS readPermissions,
    Whatever floats your catamaran.

  • Users who have thanked Old Pedant for this post:

    Keleth (08-13-2011)

  • #13
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,536
    Thanks
    45
    Thanked 259 Times in 256 Posts
    That is... ridiculously brilliant. I know when I first built the tables, I tried to figure out some way to get it to analyze that way, but for whatever reason, I couldn't figure out a query to do it. And all the techniques you provided are great... I can try them out to see which works best for me, and they've all given me some insight into development in general, and techniques I can use in other situations.

    As with all the advice you give, thank you very much. Always detailed, well written explanations that are a boon.


  •  

    Posting Permissions

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