How to register each new relationship between two entities

Asked

Viewed 90 times

1

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

Following (theoretically) the tables:

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 alter your status (for the possible status pre-registered) and thus create a new record for academic profile (concerning your status new) and add additional fields in personal profile?

For that, 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 latter 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:

  • Use a third key (such as id) in a relationship table, it is correct?

  • What data normalization says about this?

  • There is another way out of this situation?

mysql sql database modeling relational-model

  • If you are confused, write in a way that is not confusing. Think, detail the problem. Who knows when you are doing this the answer comes naturally. If it doesn’t come up at least we will have less confusing information to help you. The secret to modeling correctly is to have clarity and only those who know the problem can have it. Think of it as an algorithm. Describe the problem step by step, think about everything that is relevant. Have space, do not save. Give a general description of the problem because it may be that everything is wrong.

  • The thing I don’t understand is this id (FK). All idare confused about what is, the relationship between them, because they have a house that doesn’t follow the pattern of others.

  • @bigown added comments explaining the reference of each

1 answer

2


Not to be unanswered.

Using a third key (such as id) in a relationship table, is correct?

I can’t say, I don’t know what it’s for, what it refers to, and even asking I didn’t get an answer. But the name is bad. id should be used only for primary calling.

Actually some of these tables are weird, there’s no information.

What data normalization says about this?

It doesn’t say anything in anything specific. Who does the modeling should analyze and tell how it is. The problem is that it can only tell who knows the problems in detail. The question does not show all the details to make an evaluation.

What you can see is everything normalized. Maybe 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 gets worse if it’s also a foreign key. Why do you need this?

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

There is another way out of this situation?

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

The part of status seems to be on the right track. Of course it needs to implement correctly as well. But if I understand correctly, where it informs what is the status current?

Something tells me this can be done in a completely different way yes, but information is missing to be sure.

This seems to be a case of ill-defined problem and not ill-defined modeling. Obviously the second one will be wrong when the first one is. When you can’t explain the problem to other people, it means you still don’t understand the problem.

Browser other questions tagged mysql sql database modeling relational-model

You are not signed in. Login or sign up in order to post.