How to generate a record for each new relationship between two entities

Question

How to generate a record for each new relationship between two entities

Navigation

#1 by (2 votes)

1

Considering a scenario where a member has a account , personal profile , academic profile status (between pre-defined statuses: teacher, student and collaborator) within the system. For each status , the academic profile is different.

The tables (theoretically) follow:

MEMBRO
id (PK)

MEM_CONTA
membro_id (PK) (FK) // referencia id em MEMBRO
email (string)
username (string)
senha (string)

MEM_PERFIL
membro_id (PK) (PK) // referencia id em MEMBRO

MEM_PER_PESSOAL
perfil_id (PK) (FK) // referencia membro_id em MEM_PERFIL
nome (string)
descricao (string)

Now my problem: how to allow a member to change your status (for pre-registered status ) , create a new record for academic profile data (relative to your new status ) and add additional fields in personal profile ?

For this, I imagined:

STATUS
id (PK) 
titulo (string)
descricao (string)

MEMBRO_STATUS
membro_id (PK) (FK) // referencia id em MEMBRO
status_id (PK) (FK) // referencia id em STATUS
id (FK)

MEMBRO_PERFIL_PROFESSOR
id (PK) (FK) // referencia id em MEMBRO_STATUS

MEMBRO_PERFIL_PROFESSOR_ACADEMICO
perfil_id (PK) (FK) // referencia id em MEMBRO_PERFIL_PROFESSOR
curso (string)
nivel (string)
entrada (timestamp)
saida (timestamp)

MEMBRO_PERFIL_PROFESSOR_PESSOAL
perfil_id (PK) (FK) // referencia id em MEMBRO_PERFIL_PROFESSOR
url_lattes (string)

MEMBRO_PERFIL_ALUNO
id (PK) (FK) // referencia id em MEMBRO_STATUS

MEMBRO_PERFIL_ALUNO_ACADEMICO
perfil_id (PK) (FK) // referencia id em MEMBRO_PERFIL_ALUNO
bolsa (string)

MEMBRO_PERFIL_ALUNO_PESSOAL
perfil_id (PK) (FK) // referencia id em MEMBRO_PERFIL_ALUNO
url_pessoal (string)

To identify the last status (active status) of a member, then:

MEMBRO
id (PK)
status_id (FK) // referencia id em MEMBRO_STATUS

About this practice, I have several questions:

Using a third key (like id ) in a relationship table, is that correct?
What does data normalization say about it?
Is there any way out of this situation?

mysql sql modelagem modelo-relacional

asked by anonymous 17.09.2015 / 13:49

1 answer

Is it possible to change the value of a parameter in a querystring in javascript? No Entity Framework provider found for ADO.NET provider [closed]

score 2 · Accepted Answer

Not to be left unanswered.

Using a third key (like id) in a relationship table, is that correct?

I do not know what to say, I do not know what it's for, what it refers to, and even if I did not get an answer. But the name is bad. id should be used only to call primary.

In fact some of these tables are weird, have no information at all.

What does data normalization say about it?

She says nothing in anything specific. Whoever does the modeling should analyze and tell how it is. The problem is they can only tell anyone who knows the problems in detail. The question does not show all the details to make an evaluation.

What you can see, everything is normalized. Maybe even too much. It may even exist in certain situations, but it is strange to have a table with only primary key and nothing else. It worsens if it is also a foreign key. Why do you need this?

But like I said, it may be right inside the problem.

Is there a way out of this situation?

Certainly. Some better worse ones. But it's hard to say without knowing all the requirements.

The part of status seems to be on the correct path. Of course you need to implement properly as well. But if I understand correctly, where does it tell you what is the status current?

Something tells me that this can be done in a completely different way, but lack information to be sure.

This seems to be a case of ill-defined problem rather than ill-defined modeling. Obviously the second one will be wrong when the first one is. When you can not explain the problem to other people, you still do not understand the problem.