Design workflow table

Navigation
1

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

It would be a "workflow".

I would like it to work as follows:

1- This application will have multiple groups and groups, multiple users.

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

3- The "administrator" can create several workflows, with 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 chooses a group, not a specific user, someone from that group would be drawn.

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

I think that's basically it.

This is really giving me a lot of work. I can not think of anything.

    
asked by anonymous 03.02.2014 / 23:01

2 answers

1

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

  • Person
  • User
  • Group
  • Flow
  • Request
  • Step

    • 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 your description I identified that this is the model specification of this application, from the 4th onwards we have specifications on business rules.

    Below is 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)
        
    04.02.2014 / 12:46
    0

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

    An example that 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 User responsible for mutual exclusion. This rule should be treated in the application code. If there is a need to choose a random leader for a group, also treat the code at the time the step is saved in the database, 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 override. Also, only allow a step to be executed if it is not null the approved field is true in idFlux step -1.

    I hope it helps.

        
    04.02.2014 / 14:22
    Calculate user's age by registered date of birth [duplicate] Printing directly to the printer