Design table of "workflow"

Question

I’m doing an application in Cakephp and would like your help in what would be the best way to create my database and my relationship between models.

It would be a "workflow".

I would like it to work as follows:

1- This application will have several groups and these groups, several users.

2- Each request would have its own flow, and several requests could have the same flow.

3- The "administrator" can create multiple workflows, up to 5 steps, between groups and people. Example:

Primeiro Aprovador | Segundo Aprovador | Terceiro Aprovador | Quarto Aprovador | Quinto
 Grupo Delta           Joao                    Pedro            Grupo Alfa        NULL

4- When the administrator chose a group, and not a specific user, someone from that group would be drawn.

5- Approvals would follow the order, that is, the next can only approve if the previous one has already approved.

I think that’s pretty much it.

This is really giving me a lot of work. I’m not getting anything.

Answer 1

I identified 6 entities (tables) based on their description of the problem, are they:

1. Person
2. User
3. Group
4. Flow
5. Solicitation
6. Footstep

The relationship between them follows the order of the text:

Grupo 1+ <---> 1+ Usuario //dado um Grupo, ele possui 1 ou mais usuários
                          //dado um Usuario, ele está em 1 ou mais grupos

Solicitacao 1+ <---> 1 Fluxo //dada uma Solicitacao, ele possui apenas 1 fluxo
                             //dado um Fluxo, ele possui 1 ou mais solicitações

Passo 1-5 <---> 1 Fluxo //dado um Passo, ele está em apenas um fluxo
                        //dado um Fluxo, ele possui de 1 a 5 passos

Passo 1 <---> 1 Pessoa //dado um Passo, ele pode ser uma Pessoa

Passo 1 <---> 1 Grupo //dado um Passo, ele poder ser um Grupo

Until item 3 of its description identified that it is the specification of the Model of this application, from 4th on we have specifications on business rules.

Below follows the data model. In it I used a generic notation to represent the tables and I believe it is quite simple to translate it to any database.

Pessoa
  - id (pk)
  - nome

Usuario
  - id (pk)
  - login
  - senha

Grupo
  - id (pk)
  - nome

Grupo_Usuarios
  - grupo_id   (fk)
  - usuario_id (fk)
  - primary_key (grupo_id, usuario_id)

Fluxo 
  - id (pk)
  - descricao

Solicitacao
  - id (pk)
  - data
  - fluxo_id (fk)

Passo
  - id (pk)
  - numero
  - fluxo_id (fk)
  - unique (numero, fluxo_id)
  - check (numero > 0 AND numero < 5) //indexado em 0. Valores permitidos de 0 a 4 (cinco valores)

//caso o passo seja para uma Pessoa, inserir registro nessa tabela
Passo_Pessoa
  - passo_id  (fk)
  - pessoa_id (fk)
  - primary_key (passo_id, pessoa_id)

//caso o passo seja para um Grupo, inserir registro nessa tabela
Passo_Grupo
  - passo_id (fk)
  - grupo_id (fk)
  - primary_key (passo_id, grupo_id)

Answer 2

I believe that modeling with information in the tables is ideal, pointing out the order of each step.

An example you can adapt to your need:

Tables

Grupo {id, nome}
Usuario {id, nome}
Membro {idGrupo, idUsuario}
Fluxo {id, titulo, descricao}
Fluxo_Passo {idFluxo, passo, idGrupoResponsavel, idUsuarioResponsavel, idUsuarioAprovou, aprovado}

Use null values in Group and Responsible User for mutual exclusion. This rule must be treated in the application code. If there is a need to choose a random responsible for a group, also deal with the code at the time the step is saved in the bank, but this procedure can also leave only the indicated group and not the user, so any member of the group could assume.

Using an explicit field for the approved user id can allow substitution. Also, only allow a step to be executed if it is not null the approved field is true in idFluxo step -1.

I hope it helps.