Store as much data as I want in a tebela column in oracle Apex

Question

I’m creating a page on Apex And I need to create a graph of the reasons that cause the most fines, but a fine can have several reasons. I’m not able to store these reasons so I can generate the graph

CREATE TABLE "MULTA"(
    "COD_MULTA" NUMBER(*,0), 
    "DATA_APLICACAO_MULTA" DATE, 
    "QNT_PARCELAS" NUMBER(*,0), 
    "VALOR" FLOAT(2), 
    "CPF" VARCHAR2(11), 
    "NOME_MOTIVO" VARCHAR2(1000), 
    PRIMARY KEY ("COD_MULTA")
    USING INDEX ENABLE
)

CREATE TABLE "MOTIVO"(  
    "COD_MOTIVO" NUMBER(*,0), 
    "NOME_MOTIVO" VARCHAR2(1000), 
    PRIMARY KEY ("COD_MOTIVO")
    USING INDEX ENABLE
)

Can someone help me with that?

Answer 1

If a fine can have more than one motive then the relationship between the fines and the motives is 1-N. In this way a third table is needed that relates each fine to its various reasons.

Something like this:

CREATE TABLE motivos_multa (
  COD_MULTA NUMBER,
  COD_MOTIVO NUMBER
);

It does not intentionally include references to foreign keys. This table must be populated whenever a fine is entered, associating the fine with its motives (one line for each associated reason).

To get the reason for frequent fines the query below is sufficient:

SELECT motivo.NOME_MOTIVO, count(1)
FROM motivos_multa 
  INNER JOIN motivo ON motivos_multa.cod_motivo = motivo.cod_motivo
GROUP BY motivo.NOME_MOTIVO;