...

View Full Version : Forum permissions: indiv columns or binary string?



Keleth
08-11-2011, 08:00 PM
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.

Old Pedant
08-11-2011, 08:32 PM
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.

Keleth
08-11-2011, 09:47 PM
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?

Old Pedant
08-11-2011, 10:44 PM
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.

Keleth
08-11-2011, 11:14 PM
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.

Old Pedant
08-11-2011, 11:24 PM
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.

Keleth
08-11-2011, 11:29 PM
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.


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.

Old Pedant
08-12-2011, 04:24 AM
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

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.

Keleth
08-12-2011, 10:11 PM
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'.

Old Pedant
08-13-2011, 02:07 AM
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:


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."

Old Pedant
08-13-2011, 02:17 AM
And a postscript: You don't *HAVE* to change your bit fields to NULL.

You *could* do

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:


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.

Old Pedant
08-13-2011, 03:15 AM
Finally, you could of course use CASE WHEN in place of the nested IF's et al.


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


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.

Keleth
08-13-2011, 08:09 AM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum