Table structure for genealogical tree

6

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

Example:

  • Individual
    • Father
      • Father (paternal grandfather)
        • Father (Father's great grandfather)
        • Mother (paternal great-grandmother)
      • Mother (Paternal Grandmother)
        • Father (Father's great grandfather)
        • Mother (paternal great-grandmother)
    • Mother
      • Father (maternal grandfather)
        • Father (maternal great-grandfather)
        • Mother (maternal great-grandmother)
      • Mother (Mother's grandmother)
        • Father (maternal great-grandfather)
        • Mother (maternal great-grandmother)
  • My case name goes a little further than the example, up to the 5th genealogy.

        
    asked by anonymous 28.12.2015 / 18:21

    1 answer

    2

    Tree structures are recursive in nature and a possible template would be a table where each person has an "id" field and a "child_id" field which points to the child id, so you can set the tree to unlimited depth.

    See below 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 said by the bigown in the question comment your question is closely related to tree modeling in banks and a more comprehensive discussion on such can be found here .

        
    08.01.2016 / 18:59