Mysql - A single entity (N:N)

Asked

Viewed 43 times

1

Good morning guys. I’m new to database and I’m trying to understand some concepts:

A single entity (N:N)

A company works with projects in a horizontal way, where the person N in a project can be the person X and in another project the person X can be the person N. This can be for several projects and people.

1) Created a register of people as follows:

CREATE TABLE tb_natural_person(
id_natural_person INT NOT NULL AUTO_INCREMENT,
first_name_natural_person VARCHAR(255) NOT NULL,
last_name_natural_person VARCHAR(255) NOT NULL,
PRIMARY KEY(id_natural_person)
)

2) Para que pudesse uma ter um relacionamento hierárquico de um funcionário para o outro (N:N), criei uma nova tabela onde tanto a coluna “parent_id_natural_person_relation” quanto a coluna “child_id_natural_person_relation” se referenciam (FOREIGN KEY) a coluna “id_natural_person” of the first table.

CREATE TABLE tb_natural_person_relation(
id_natural_person_relation INT NOT NULL AUTO_INCREMENT,
parent_id_natural_person_relation INT NOT NULL,
child_id_natural_person_relation INT NOT NULL,
PRIMARY KEY(id_natural_person_relation),
FOREIGN KEY (parent_id_natural_person_relation)
REFERENCES tb_natural_person(id_natural_person),
FOREIGN KEY (child_id_natural_person_relation)
REFERENCES tb_natural_person(id_natural_person)
)

3) Ao tentar fazer um SELECT via JOIN (INNER JOIN, LEFT JOIN, UNION, etc) percebi que não consigo selecionar os campos “first_name_natural_person” e “last_name_natural_person” relacionados aos campos “parent_id_natural_person_relation” e “child_id_natural_person_relation” (or at least I don’t know how).

Question: I have to make the construction and relationship of these tables, where I can have as many "hierarchical relationships" as necessary. Is it right the way I’m structuring up? Caso sim, como poderia escrever o SELECT de uma forma que apareça em uma linha os campos “first_name_natural_person” e “last_name_natural_person” relacionados aos campos “parent_id_natural_person_relation” e “child_id_natural_person_relation” da outra tabela? In case I’m wrong my way of thinking, how would the right way?

Thank you very much and hugs,

GVM

mysql sql normalization

1 answer

1


This is called self-relationship. Note that the table tb_natural_person must participate in his SELECT two-part: parent and child.

I think what you want is something like:

SELECT * FROM tb_natural_person_relation rel
    INNER JOIN tb_natural_person child ON (rel.parent_id_natural_person_relation = child.id_natural_person)
    INNER JOIN tb_natural_person parent ON (rel.parent_id_natural_person_relation = parent.id_natural_person)

Browser other questions tagged mysql sql normalization

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