Jump to content

Archived

This topic is now archived and is closed to further replies.

shaggycap

Permission Table Normalization

Recommended Posts

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 (c)reate (r)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

Share this post


Link to post
Share on other sites
Not sure what makes you think this isn't normalized...

Share this post


Link to post
Share on other sites
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.

???

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Thanks fenway, much appreciated. Looks like I am a little closer to becoming a Jedi than I first anticipated.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.