User with more than one profile

Asked

Viewed 86 times

1

In the business rule of the system I am working on, a user can have more than one profile. Profiles are: administrator, evaluator and responsible.

For example: So-and-so can be an administrator and evaluator at the same time.

My question is how to map it? I’ve had some ideas but I don’t know if it’s the best way to do it.

One idea: Create a column in the database for each profile, for example: is_admin, is_responsible and is_appraiser and go 0 or 1 according to the user profile.

Here is another idea: Have a column in the database, to store only a number that will be generated according to the profile. 

Exemplo:
Administrador = 0 
Avaliador = 1
Responsável = 2
Administrador + Avaliador = 4

These are ideas I’ve had, but I don’t know the right way to store it.

sql mysqli modeling software-architecture
  • 1

    I believe that the best way is in the user table to have only one column identified which type of profile of the same.

  • What if there is another profile type? Ideally, you should make a second table since the relationship between user and profile 1:N.

  • Is there hierarchy between profiles? Will this control permissions? If so, is each group doing unique to it? Or, for example, administrator to do everything by default?

  • @Andersoncarloswoss there is no hierarchy, they are independent even.

  • Are these profiles static or dynamic? They could be an enumeration or new profiles could appear at any time?

  • @Andersoncarloswoss They are static, will always be these three types of profiles.

  • For a scalable application I would choose the answer of Dherik, and for a more performative and easy to develop application of Anderson.

Show 2 more comments

2 answers

1


If they are static profiles - there is no possibility to create new profiles with the time of application - you can define them through an enumeration with values being powers of 2.

enum Perfis {
    Administrador = 1;
    Avaliador = 2;
    Responsavel = 4; 
}

And, to persist the value in the database, create a column of the whole type. Thus, to define that a user has more than one profile, you can use the binary operator |:

john.perfil = Perfis.Administrador | Perfis.Avaliador;

In this case, the value of john.perfil shall be 3, thus 1 | 2 = 3. And, if necessary, whether john has a specific profile, just use the binary operator &:

if (john.perfil & Perfis.Administrador) {
    // John é um administrador
}

This logic is safe in ensuring that the user has a certain profile because as the values in the enumeration are powers of 2, the only combination that results in 3 is 1 | 2.

Analyzing at a low level, you will basically have a binary value - in this example, size 3: 000 - where each profile is related to a bit. Admin Profile would be bit 0, Evaluator bit 1 and Responsavel bit 3. If the user has a certain profile, just set the respective bit to 1.

I commented on this technique in Python in this question: What does the "|=" Python operator mean?

1

My suggestion is to create a table usuario, a table perfil and a table usuario_perfil.

The user may have more than one profile and a profile may belong to more than one user, characterizing a relationship N-N. Thus, you will need an intermediate table, a usuario_perfil.

Example of a register:

-- tabela usuario
id | nome
1  | Gustavo
2  | Augusto

-- tabela usuario_perfil
usuario_id | perfil_id
1          | 1
1          | 2
2          | 3
2          | 1

-- tabela perfil
id | nome
1  | Administrador
2  | Avaliador
3  | Responsável

It is interesting to create a Constraint single key in table usuario_perfil in the fields usuario_id and perfil_id, to ensure that a user is not associated more than once with the same profile.

Browser other questions tagged sql mysqli modeling software-architecture

You are not signed in. Login or sign up in order to post.