Symfony structure idiomatic way of storing user role

Question

In my Symfony 2 application, I want to use the standard user and role authorization system (http://symfony.com/doc/2.0/book/security.html)

My user is an object stored in a database with a doctrine (user interface implementation). I will have 5 predefined roles in my system, each user can have several different roles.

What will be the most idiomatic way to implement this? I think of the following three solutions.

• Create a separate Role object and create a many-to-many relationship with a custom entity

  • Plus: easy to get all users with a specific role
  • Con: resource intensive? (A double connection is always required to get all roles for the user)
  • Con: Not idomatically? The number of roles (and their names) never changes, so it makes sense to store it in the database as a separate object?

• You have a field inside the user that is a sorted, comma-separated list of roles and getRoles () is implemented as explode(',',this.all_roles)

  • Plus: not expensive calculation
  • Con: hard to get all users with a specific role
  • Con: Data fields such as kitty screams do (normalization, etc.)

• Have 5 binary fields in a user entity for each role

  • Plus: not expensive calculation
  • Plus: easy to get all users with a specific role
  • Con: It's still not very good.

What is the most idiomatic way to implement this system?

Answer 1

Of course, the answer very much depends on your requirements, but I will try to answer it as globally as possible.

Option 1: Relational Method

From a purely relational point of view, you want your database to be normalized, which will lead to your first option: a table for roles with the ratio m: n to your user table. This has some advantages:

• Well, this is the way you would expect your database to work, so there are no functions hidden in your entities.
• It is not possible to damage things (for example, when you have a varchar field and expect it to have some format like coma-seperation).
• Only one way to do something.

Regarding your concern, roles never change: storing relational data is not related to how often it changes. And you should always remember that requirements are changing. The more you ruin your name in the name of optimization, the more you will fight later when you need more roles that change more often.

Of course, you may have performance problems, especially if you are faced with some kind of problem with loaded downloads or if you don’t cache things and need to reload roles every time the page loads. But, again, relational databases are designed to support such materials, so there should be ways to optimize queries.

Option 2: hack

The second option, just storing all the roles in varchar, would be much better in terms of performance. Just one text box to load, some PHP processing, and you're done. On the other hand, you may encounter several problems:

• You do not have control over the varchar field, so one malfunctioning script can jeopardize your entire application (this is very bad, but depending on your project, if you are the only developer and know what you are doing, it might go well)
• Upgrading to a single dataset is much more difficult, since you need to extract all the roles, upgrade the ones in question, and save back
• Querying for all users with a specific role is much more complicated.
• Removing a role is much harder

Option 3: a pragmatic solution

The third option with 5 Boolean values ​​for each role in the middle: there is no way to drag it in, and performance should not be a problem. Upgrading is easy, as well as removing a role or adding a new one. It's pretty clear what the fields are doing, so no harm on this side either. This makes your object and database more ugly and you will have role names in your user model, as well as matching true / false fields with the correct role names, which can be a little confusing.

Result

That's all, I would go with option 1. It can be assumed that performance is a problem, but if I do not prove it, I don’t think about such things. After all, what will you do when you really have a real performance problem? You can add additional hardware, optimize your dbms, optimize queries, or perhaps use dbms with better performance (Hello Oracle!).

And you can always use option 3 later if you prove that your application is slow due to the role table. You just need to change your user object and get one request that extracts the roles and sets the correct true / false combinations for each user. If the software is clean, this is a watch problem, so now you don’t need to do this, believing that performance may be poor.