permission DB design

[DHDesign]

can i get feedback on the following please...

Goal: User Permission System without Groups (individual assignment)
Details:

- 7 locations (LocA, LocB, LocC, LocD, LocE, LocF, LocG)
- 5 Permissions (View, Add, Edit, Delete, Close)
- when setting up a User, you get a checkbox matrix and assign Permissions to one or multiple Locations for the User

Example:
User1 = LocA -> View, Add
User2 = LocA -> View / LocB ->View, Add / LocC -> View, Add, Edit
User3 = LocE -> View, Add, Edit / LocF -> View
AND SO ON...

When setting up the user, a table with Locations as Column Headers and Permissions as Row Headers will be given with a series of checkboxes in the middle...any combination for multiple Locations/Permissions can be given to a User.

What is the best way to design this? I will post my idea in a sec.
Thank you!

[bwh2]

make it relational...

tbl_users
---------------------
user_id
user_name



tbl_locations
---------------------
location_id
location_name



tbl_users_locations
---------------------
user_id
location_id



tbl_permissions
---------------------
permission_id
permission_name


tbl_users_permissions
---------------------
user_id
permission_id

[DHDesign]

@bwh2...thanks for the feedback. i thought about that, but the problem is that one user can have different permissions levels for each location.

User1: LocA -> View / LocB -> View, Edit / LocF -> View, Add, Edit, Delete

How would this work? In your suggestion, u have in tbl_users_permission two fields, user_id & permission_id...but how do you associate the one permission of View for LocA and two permissions of View, Edit for LocB, etc.?

make any sense?

[bwh2]

tbl_users_permissions
-------------------------
user_id
permission_id
location_id
that make sense?

[DHDesign]

yes...so then i wouldnt need the TBL_USER_LOCATIONS because it would be redundant with the TBL_USERS_PERMISSIONS, no?

[bwh2]

yes, that's correct.

[DHDesign]

recently while talking to a db developer, he mentioned the following and i want to get your thought on this:

since there will only be 7 locations (never more or less), he said that a single table with multiple columns that have 0's and 1's would be more efficient in terms of speed of the query...such as:



TBL_USER_PERMISSIONS
------------------------
User_ID
LocA_View
LocA_Add
LocA_Delete
LocA_Edit
LocB_View
LocB_Add
LocB_Delete
LocB_Edit
LocC_View
and so on...


When the user logs in, then you are just running one query to one table to find that one User_ID and find out which columns have 0's (user doesn't have permission) and which columns have 1's (grant user the permission).

In terms of normalization and theory, I realize this is not right, but in terms of this project where the 7 Locations will always remain at 7 (hence no table changes required down the road for flexibility), would this be a better way?

[bwh2]

what i don't like about that design is that you're relying on the coding (php, asp, whatever) to relate the tables. so while queries may run faster with that method, the time spent processing the conditions and arrays probably makes it slower overall. and it's not relational.

i'm not saying it's necessarily a bad idea. i use bit fields quite often actually. but you should test both methods out.

[DHDesign]

i definitely agree....you would have to rely on coding rather than tables and yes, its not relational. ill test it out and post the results...but the test wont be an effective test until i populate the tables with over at least 1000 records to see the difference in performance.

thanks for constant feedback bwh2...much appreciated...hopefully i can return the assistance some day. :)

[bwh2]

no problem.