Many to many ratio between various tables

Question

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

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

And now if we wanted to add a table avos we would have to create another pivot to relate parents' ids to grandparents' ids? And then to add great-grandparents as well? 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 table pivot, simply arrange a separator between ids, for example in the children’s table create a column id_pais and then insert the ids in the format 1|2 and for grandparents would be a column ids_avos with 1|2|3|4, and great-grandparents would do the same? It seems to me almost equally 'laborious' if we had tri, tetra, p... grands.

Is there another way that I didn’t mention here and don’t know how to do it? What’s the best way to do this?

Answer 1

With self-relationship You can come up with a better and cleaner solution, as long as it is by "paternity". If you continue in this model and in this 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 ancestor). Working with specific queries you will achieve good results. I don’t know if you take what you need.

Answer 2

Yes there are several other ways.

One of them: makes a people table with people’s names and data and a table for relationships, in which you have the type of relationship, whether parent or child and who are the people in the relationship. For example:

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

So:

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 guy here as varchar directly, but can/should be placed in a separate table to maintain the normal shape and everything else.