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.

 

Nah, I would have just two coloumns UserID and Permission. Each user would have multiple rows. Makes it easier to change.

 

Can a user have multiple permissions? If not, I would just add a "user_permissions" field to the USERS table, as you suggest.

If a user can have multiple permissions, you have a "one-to-many" relationship, in which case I'd use two tables:

create table user_names ( user_id varchar(10) not null, name varchar(40) not null, email varchar(40) null, etc etc. ) ; 
create table user_permissions ( user_id varchar(10) not null, permission varchar not null ) ;

Code (markup):

User_id then becomes the primary key on table user_names, and a foreign key on table user_permissions.
You can join the two tables as required:

select n.user_id, n.user_name, p.permission
from user_names n, user_permissions p
where n.user_id = n.user_permissions

Code (markup):

This may seem a long way around, but will save you time & trouble going forwards.

 

Yes a user ID and permission would do real good in this case.

 

I would separate the tables and use the Ceemage's join example with the query...I always separate user login database from their details and any other tables with additional data, then use session variables to pick stuff as and when needed.
That way you are somewhat indexing your database and querying only what you need