Symfony framework; idiomatic way to store a user's role

Question

In my Symfony 2 application I want to use the standard Authorization system of users and roles (http://symfony.com/doc/2.0/book/security.html)

My User is an entity stored in a database with doctrine (implementing User Interface). I will have 5 predefined roles in my system, each user having possibly multiple of these roles.

What would be the most idiomatic way of implementing this? I am thinking of the following three solutions.

1. Make a separate Role entity and create a many-to-many relation with the user entity

   • Plus: Easy to get all users with a specific role
   • Con: Resource intensive? (Always needing a double join to get all roles for a user)
   • Con: Not idomatic? The number of Roles (and their names) never change, so does it make sense to store it in the database as a seperate entity?

2. Have a field within user that is a sorted, comma-seperated list of roles and getRoles() is implemented as explode(',',this.all_roles)

   • Plus: Not computationally expensive
   • Con: Hard to get all users with a specific role
   • Con: Database fields like this make kittens cry (normalizaition and stuff)

3. Have 5 binary fields in the user entity for each role

   • Plus: Not computationally expensive
   • Plus: Easy to get all users with a specific role
   • Con: This still doesn't feel good

What is the most idiomatic way of implementing this system?

Answer 1

Of course an answer depens highly on your requirements, but I'll try to answer it as globally as possible.

Option 1: The relational way

From a pure relational point of view, you want your database normalized which would lead to your first option: A table for the roles with a m:n relation to your user table. This has some advantages:

• Well, it's the way one would expect your database to work, so no functionality which is hidden in your entities.
• No way to screw things up (like when you have a varchar field and expect it to have some kind of format like coma-seperation)
• Only one way to do things

Regarding your concern that roles never change: Storing data relational is not about how often it changes. And one should always remember that requirements change. The more you mess up right now in the name of optimization, the more you struggle later when there is the need for more roles which change more often.

Of course, you may have perfomance issues especially if you run into some kind of eager loading issue or if you don't cache things and have to reload roles on every page load. But than again, relational databases are designed to support such stuff, so here should be ways to optimize the queries.

Option 2: The hack

Your second option, simply storing all roles into a varchar, would be much nicer in terms of performance. Only one text field to load, some PHP processing and you are done. On the other hand, you may run into several issues:

• You have no control over the varchar field so one malfunction script may compromise your whole application (This is really bad, but depending on your project, if you are the only developer and you know what you're doing, it could go well)
• An update to one dataset is much harder, as you have to extract all roles, update the ones in question, and store back
• Querying for all users with a specific role is much harder
• Deleting a role is much harder

Option 3: The pragmatic solution

The third option with 5 booleans for each role is in the middle: There is no way to screw it up and performance should not be a problem. Updating is easy as well as deleting a role or adding a new one. It's pretty clear what the fields do, so no harm on this side either. It makes your entity and database look a bit more ugly and you would have the role names in your user model as well to map the true/false fields to the correct role names, which might be a bit confusing.

Result

This all in mind, I would go with option 1. One may assume that performance is an issue, but unless I have prove of that, I don't think about such stuff. In the end, what do you do when you really have a real performance problem? You may add some additional hardware, optimize your dbms, optimize the queries or maybe use a dbms with more performance build in (Hello Oracle!).

And you can always use option 3 later on if you have prove that your application is slow because of the role table. You would just have to change your user entity and have one query which extracts the roles and sets the correct true/false combinations for every user. If the software is clean, this is an issue of hours, so no need to do it now on the idea that performance might be bad.