Tender relationship with a foreign key not mandatory

Question

I have a database with three entities that relate: user, process and courses. The goal is to model the bank to meet the following requirements:

• The user can sign up for several selective processes
• Each selection process may or may not have related courses (depending on the type of process).
• When a selection process has courses the user must select the courses he wants to participate and also set a priority for each one, as it will be selected for only one.

Since courses and priority are not mandatory fields - there are processes that do not have courses. Would the best way to do would be using ternary relationship with the three foreign keys?

that way it would be:

table entries

id_usuario (FK)(PK)(NN)
id_processo (FK)(PK)(NN)
id_curso (FK)(quando o processo não tiver curso, esse campo será NULL)
prioridade(quando o processo não tiver curso, esse campo será NULL)

If correct, how would the query select all the processes that a user is enrolled along with the courses and the priority between them.

I am a beginner and this problem seemed to me quite complex. Someone could help?

Answer 1

The structure of the database seems to me perfectly adequate, I would probably do likewise.

The consultation is not so complicated. You will need a JOIN for each of the related tables. As it is guaranteed to have a user and a process for each registration, use INNER JOIN with these tables. As course may be blank, you will need a LEFT JOIN, or the results shall include only registrations.

The query looks like this:

SELECT
     usuario.id AS id_usuario,
     usuario.nome AS nome_usuario,
     processo.id AS id_processo,
     processo.nome AS id_processo,
     curso.id AS id_curso,
     curso.nome AS nome_curso,
     inscricao.prioridade
FROM inscricoes inscricao
    INNER JOIN usuarios usuario
    ON usuario.id = inscricao.id_usuario
    INNER JOIN processos processo
    ON processo.id = inscricao.id_processo
    LEFT OUTER JOIN cursos curso
    ON curso.id = inscricoes.id_curso
WHERE inscricoes.id_usuario = 1 -- parametrize aqui
ORDER BY processo.nome ASC, curso.prioridade DESC, curso.nome ASC