0
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?
Very obvious, but I don’t think I understand. If you can put a more visual example, such as an image of the tables and an example of how they relate, I would appreciate it. do not forget that it is not only my direct family, are my cousins, my great-grandparents etc... Even as if it were a family tree
– Miguel