mysql - Database Tables for User management -

i advice best practices in user role management.

i create application user register , have different roles. obvious thing is, create users table , save user data there this:

users

• id
• useruuid
• username
• role

but think separated tables different groups. like:

admins

• id
• useruuid (-> foreign key users table)
• accessright1
• accessright2

subscribers

• id
• useruuid (-> foreign key users table)
• etc

does make sense? should users, regardless of role in 1 database? , specific information different roles?

thanks!

if roles going change work. but, should plan change, , more flexible option allowing add roles without needing change database schema this:

users:

• uid
• user info...

roles:

• rid
• name

userroles:

• uid (fk user table)
• rid (fk roles table)

in code check userroles table determine if user has given role , therefore should allowed whatever.

additionally, if want mix , match permissions actions across multiple roles (i.e. both admin's , moderators have permission delete posts or something). add level permissions table, , join table (rolepermissions) linking roles table. you're access checks become more complicated then, requiring join userroles table , rolepermissions join table determine if user has given permission something.

something this:

select *      userroles u inner join rolepermissions p          on u.rid=p.rid      u.id=<user> , p.pid=<permission>