CODEWITHSUNDEEP
phpmysqlsqlpostgresqlscalability
Devin Dixon
Devin Dixon

Reputation: 12403

User Roles DB Schema Design

I am thinking about a schema design that involves having users and user roles, but I am not sure what would be the better route.

Option 1

Create three tables, one with the user information, one with the role information, and one with th user role relation.

users {
    u_id,
    etc
}

roles {
   r_id,
   r_name,
   etc
}

user_roles {
   u_idm
   r_id
}

Option 2

Create two tables, one with the user information, and the other with role, role info,and relation info.

users {
    u_id,
    etc
}

roles {
   r_id,
   u_id,
   r_name,
   etc
}

Option 1 is more robust but requires an extra join. Option 2 will require an extra primary key but will only be one join. If I ever change the role name, it would take longer to update with option, but I don't forsee updates being frequent.

For a scalable solution, which would be better? What other insights in my missing? This is for a mysql and postgresql solution.

Upvotes: 0

Views: 1045

Answers (2)

Stelian Matei
Stelian Matei

Reputation: 11623

I would go with the first option with some changes:

- each user can belong to ONLY ONE group
- create a table defining privileges
- each group has a list of privileges

The privileges defined could be mapped to various modules of the application or to specific functionality.

The solution is simple, flexible and fast. Both privileges and groups tables should be pretty small, so extra JOINs won't have such a critical impact. Also, the authenticated user privileges could be stored in session and not loaded everytime. There will be more benefits in long term, since the solution is very flexible and easy to extend.

For example, you will create a new module called "Configuration" and you want to create a new user group called "superadmin" to have access everywhere and "configuration". You would simply make changes in the database: create group 'superadmin', add privilege 'configuration', set all privileges and that's it.

Upvotes: 0

Oliver A.
Oliver A.

Reputation: 2900

Option 1. What good is a role if only one user can have each role? If you have 100 registered users there would be 100 duplicate definitions for "registered user".

The more "etc" there is the bigger your db will get.

Having that many duplicates will slow down your database and in the end things will be a lot slower even if you have one join less.

If you run lots of role based querys and relly eel like you need a database like the one from option two you can still create a view and have the database cache it, but I doubt that this will do you any good.

Upvotes: 1

Related Questions