Ternary relationship with a foreign key not required

Question

Ternary relationship with a foreign key not required

Navigation

#1 by (3 votes)

4

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:

table_inscriptions

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?

mysql modelo-relacional

asked by anonymous 09.08.2014 / 02:54

1 answer

Difference between table, entity and behavior Working with C # resources?

score 3 · Accepted Answer

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