I have a user and he has a role. The number of roles is limited and very small (15-20). It is better for each user to store his role or create an additional table of roles and store the roles in them, and for users to save only the IDs of these roles. I found a lot of different literature, but I would like to ask this question here too. If it is not difficult to answer please.

  • It is not clear that in this case there is a "role". Because in terms of design that ID, that the name - one hell. And it is also not clear whether there is a possibility of the emergence in the future of new, not existing now roles ... - Akina
  • @Akina, such an opportunity for sure exists, what is a role? This field is in a single DB table, a table with a size of 100,000 records, and only 15-20 roles — Ivan Gladush
  • 1) If the list can change, you have to drop the ENUM / SET type. However, nobody really counted on him. 2) Ie is it just some attribute with a fixed set of values? In this case, the table of values ​​and FK from the main table is the only reasonable solution. Unless, of course, there are any exotic operations on this field — for example, the need for a composite index involving the value of this field. - Akina

1 answer 1

The smaller the number of queries to the database to achieve the same result, the more efficient the operation of the application as a whole (in a vacuum). If it is possible to collect data about the user and his role in one request, then it is possible to store them in one table. Even better.

For a more accurate answer, you need to see and understand the interaction of data within the application.