Table diagram puzzle for user permissions

Databases
1

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?

2

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.

3

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/5707/userpermtablesv4.jpg