Many-to-many relationship between multiple tables

Question

Many-to-many relationship between multiple tables

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I know that between two tables in a many-to-many relationship 'we need' (not mandatory but facilitates) a third table pivot which is what conventionally relates the ids of one to the ids of another. So far so good. So for example to make a gene tree? Would we have to create a pivot table for every two tables? Below is an image to illustrate a parent / child relationship where there is a pivot table in the 'support':

Tables: pais , filhos , pais_filhos respectively. And finally the INNER JOIN between them

And now if we wanted to add a avos table we would have to create another pivot to relate the ids of the parents to the ids of the grandparents? And then to add great-grandparents too? Or is there an 'easier' way to achieve this? That is, without 'needing' to create a pivot table for every two tables? I know I could not even use a pivot table , just get a separator between ids, for example in the children's table create a column id_pais and insert the ids in the format 1|2 and grandparents would it be a ids_avos column with 1|2|3|4 , and would it also do the same? It seems almost equally 'laborious' if we had tri, tetra, penta ... avos.

Is there another way that I did not mention here and I do not know to do this? What is the best way to do this?

sql modelo-relacional

asked by anonymous 18.06.2016 / 12:56

2 answers

Read data json inserted in mysql AngularJS value $ scope out of view

score 1 · Answer 1

With self-relationship you can come up with a better and leaner solution, provided it is by "parenting." If you continue in this model and in that solution, at each level, a new table will appear.

DROP TABLE IF EXISTS PESSOA;

CREATE TABLE PESSOA (
    CODIGO BIGINT NOT NULL,
    NOME VARCHAR(200) NOT NULL,
    ANCESTRAL BIGINT DEFAULT NULL,
    PRIMARY KEY (CODIGO),
    KEY ANCESTRAL (ANCESTRAL),
    CONSTRAINT FK_ANCESTRAL FOREIGN KEY (ANCESTRAL) REFERENCES PESSOA(CODIGO)
);

INSERT INTO PESSOA VALUES (1, 'SEU AVO', NULL);
INSERT INTO PESSOA VALUES (2, 'SEU PAI', 1);
INSERT INTO PESSOA VALUES (3, 'VOCE', 2);

You could have one more attribute that would be grade (relative to the ancestor). Working with specific queries will bring you good results. I do not know if you get what you need.

score 0 · Answer 2

Yes there are several other ways.

One of them: it makes a table people with the names and data of the people and a table for the relationships, in which you have the type of the relationship, if father or son and who are the people in the relationship. For example:

André (filho de) Francisco (pai de) Luiz
Francisco (avo de) Luiz

Then:

pessoas            relacionamento
-------            --------------
oid nome           oid id1 id2 tipo
1   André          1   1   2   filho
2   Francisco      2   1   3   pai
3   Luiz           3   2   3   avo
etc.               4   3   2   neto
                   etc.

Obviously I put the type here as varchar directly, but it can / should be placed in a separate table to maintain normal shape and everything else.