I have a database with three entities that relate: user, process_selective and courses. The goal is to model the bank to meet the following requirements:
- User can subscribe to multiple selective processes
- Each selection process may or may not have related courses (depending on the type of process).
- When a selective process has courses, the user must select the courses that he wants to participate and also set a priority for each one, as it will be selected for only one.
Given that courses and priority are not mandatory fields – there are processes that do not have courses. Is the best way to do this would be by using ternary relations with the three foreign keys?
this way it would look like:
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 it is correct, what would the query look like to select all the processes a user is enrolled in along with the courses and the priority between them.
I’m a beginner and this problem seemed quite complex. Could someone help?
The structure of the database seems perfectly adequate, I would probably do it the same way.
The query is not so complicated. You will need a
JOIN for each of the related tables. Because it is guaranteed to have a user and process for each subscription, use
INNER JOIN with these tables. Since course can be blank, you will need a
LEFT JOIN , or the results will only include registrations that have taken course.
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