vendredi 26 janvier 2018

How to design a user permission handling database?

We have a little problem in one of our projects, where two investors are architects and... as it usually is in life, they don't really get along with some of the ideas. Both have different experiences with previous projects, and it seems they look down upon the ideas of the other one. Yep, I'm one of them.

We have an argument over how to define user permission handling in one our project.

One idea is to have table with permissions, roles which gather sets of permissions and then users who have a role defined.

User
user_id
role_id

Role
role_id
permission_id

Permission
permission_id

The other side would like to propose to do it using a table with columns defining permissions:

User
user_id
role_id

Role
role_id
can_do_something
can_do_something_else
can_do_something_even_different

My take on the first option is that it's far cheaper to maintain: adding a single permission means it's just one insert + handling of the permission in the code.

In case of the other (to me) it means that you have to alter the database, alter the code handling the database and on top of that, add code to handle the permission.

But maybe I'm just wrong, and I don't see some possible benefits of the other solution.

I always thought the former is the standard to handle it, but I'm told that it's subjective and that making a change in the database is a matter of just running a script (where for me it means that the script has to be added to the deployment, has to be run on every database and in case of migration has to be "remembered" etc.)

I know the question could be opinion based, but I'm kind of hoping, this really is a matter of standards and good practice, rather then subjective opinion.

Aucun commentaire:

Enregistrer un commentaire