Table structure for genealogical tree

Question

Table structure for genealogical tree

Asked 8 years, 6 months ago

Viewed 1,031 times

8

How to create a table with genealogical tree structure for an individual using a concise nomenclature?

Example:

Individual
- Father
  - Father (Paternal grandfather)
    - Father (Paternal great-grandfather)
    - Mother (Paternal great-grandmother)
  - Mother (Paternal grandmother)
    - Father (Paternal great-grandfather)
    - Mother (Paternal great-grandmother)
- Mother
  - Father (Maternal grandfather)
    - Father (Maternal great-grandfather)
    - Mother (Maternal great-grandmother)
  - Mother (Maternal grandmother)
    - Father (Maternal great-grandfather)
    - Mother (Maternal great-grandmother)

Name my case goes a little beyond the example, reaching up to 5° of genealogy.

Related: http://answall.com/q/2425/101

– Maniero

2015/12/28 at 17:22
Will this only be for direct relatives (father and mother, grandfather and grandmother)? Or for any relative?

– Guilherme Nascimento

2015/12/28 at 17:22
1

@Guilhermenascimento in my case, only for direct relatives, father and mother of father and mother.

– Lucas Fontes Gaspareto

2015/12/28 at 17:23
1

An idea would be to create a table with only 4 columns id, tipo, nome, idRelacionada. Type would be solved in the application layer and define whether it is grandfather or grandmother (can be tynint of 0 and 1). idRelacionada would be the one who picks up the id of the cascading "child(a)". I will try to formulate an answer, but I want to put an example with mysql to be clearer :)

– Guilherme Nascimento

2015/12/28 at 17:44

1 answer

Browser other questions tagged nomenclature

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

by BrunoRB • **5,526** points · Answer 1 · 2016-01-08T17:59:33+00:00

Tree structures are recursive by nature and a possible model would be a table in which each person has a field id and a field child_id which points to the child’s id, this way you can set the tree in unlimited depth.

Below is an example of the idea in Mysql:

CREATE TABLE person (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    gender VARCHAR(6),
    child_id INTEGER,
    FOREIGN KEY (child_id) REFERENCES person(id)
);

INSERT INTO person (id, name, gender, child_id) VALUES 
    (1, 'Indivíduo', 'male', NULL), 
        (2, 'Pai', 'male', 1),
            (3, 'Pai (Avô paterno)', 'male', 2), 
                (4, 'Pai (Bisavô paterno)', 'male', 3), 
                (5, 'Mãe (Bisavó paterno)', 'female', 3),
            (6, 'Mãe (Avó paterno)', 'female', 2), 
                (7, 'Pai (Bisavô paterno)', 'male', 6), 
                (8, 'Mãe (Bisavó paterno)', 'female', 6), 
        (9, 'Mãe', 'female', 1), 
            (10, 'Pai (Avô materno)', 'male', 9), 
                (11, 'Pai (Bisavô materno)', 'male', 10), 
                (12, 'Mãe (Bisavó materno)', 'female', 10), 
            (13, 'Mãe (Avó materno)', 'female', 9), 
                (14, 'Pai (Bisavô materno)', 'male', 13), 
                (15, 'Mãe (Bisavó materno)', 'female', 13)
    ;

As already stated by Maniero in the question comment his question is closely related to modeling trees on benches and a more comprehensive discussion on this can be found here.