DB dilemma

Hello,

I have USERS table (id, name, email, phone, lastUpdate, etc...)
I want to add permissions to each user
what will be better - to add fields to USERS table
or
to create new table USERS_permissions that will include id, userID, permmission1, permmission2, etc...
?

Roi.

 

I would do something like the second one but slightly different.

I would have USERS_PERMISSION with fields id, userId,permissionId and a table called PERMISSIONS with the fields id,name

then populate something like this

Permissions

id, name
1 , view records
2, edit records
3, delete records

user_permissions
id, userId, permissionId
1, 1, 1
2, 1, 2
3, 1, 3
4, 2, 1
5, 2, 2
6, 3, 1

so above user 1 would be able to view edit and delete user 2 view and edit and user 3 just view records

 

Yes, that is good if your lots of permissions.

but if you have only couple, is it matter which way to use? the second way requeir from me to do 2 queries every time - the first one is for getting the user ID and the second is to located his permission...

 

well the result will be the same. however what if you end up creating more permissions in the future then you will have to change the db structure which is not ideal.

also you can combine everything into one query.

you could do something like this.
(not tested)

$sql = 'SELECT * FROM `users`,`usersPermission` WHERE `users`.`id`='.$_SESSION['userId'].' AND `usersPermission`.`userId`=`users`.`id`';

PHP:

 

The above example is a query that merges two tables together and gets one result. If you took that line and pasted it into phpmyadmin and had true variables filled in you will see the results you get from it. While the above example is a good way to do it, you could however break that into two queries.

First check for the user and make sure the id matches and result out with a mysql_num_rows() to make sure you are at least getting 1 row returned, and then inside the if statement after that (with a positive results $num == 1) then you could simple check for the other portion of that statement - the user permissions.

But the above example is a good way to start using statements for joined commands

 

Thanks
.........