Jump to content


Photo

Permission Table Normalization


  • Please log in to reply
4 replies to this topic

#1 shaggycap

shaggycap
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 24 October 2006 - 08:47 AM

I am working on a basic permissions system. I don't see the need the groups for this application, so I'm using something vaguely resembling the 'CRUD' method.

However I'm struggling to arrive at a decent database structure.

I have a table for the details of the user:

--------------------------------------------
| Username(pk) | Password | Email Address|
--------------------------------------------

and a table for the permissions:

---------------------------------
| Username | Task | CRUD |
---------------------------------
| Billybob | Content| 1011 |
| Sammyjoe | Product| 1100 |


I'm simply using a true/false for ©reate ®ead (u)pdate & (d) to assign permissions.

However, as you can see I havn't really normalised my table here, and I'm at a loss as to the next step.
At present I will end up with a load of duplicate entries for the tasks, ie both Billybob and Sammyjoe will have content permissions, and both have Product permissions etc.

Would anyone be able to advise me how would I improve this further? Or even scratch it if theres a better way.
Note- I don't want it to be too complex

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 24 October 2006 - 03:37 PM

Not sure what makes you think this isn't normalized...
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 shaggycap

shaggycap
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 24 October 2006 - 07:59 PM

Well its almost there, its just that there will be a lot of duplicate entries in the task field, and I thought it would be possible to improve this table. I'm now thinking that it would be less code to just have individual fields for the C,R,U,D part.

???

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 24 October 2006 - 08:53 PM

Duplicates between rows is different than duplicates within rows -- every lookup table has duplicates for all fields in various records, nothing wrong with that.  As for CRUD, personally, I think it's very convenient to use bit flags for this, since it's very easy to query and update.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 shaggycap

shaggycap
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 25 October 2006 - 07:47 AM

Thanks fenway, much appreciated. Looks like I am a little closer to becoming a Jedi than I first anticipated.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users