How to create a family table

You can create a table with below columns

id, name, parentId1, parentId2

You can assume parentId1 as male/father id and parentId2 as mother/female

For the top level of root the parentId1 and parentId2 will be zero. So this will make a tree structure and you can also add other details of any family members like age or any otherthing.

Evening @cantz

I think firstly you will need to create a family table and then define the relationship between them e.g

Family.php model:

public function members()
{
       return $this->hasMany(Member::class);
}

Member.php model:

public function family()
{
       return $this->belongsTo(Family::class);
}

Once you've done that you can then call all family members from a given family like

 $members = Member::where('family_id', $some_family_id)->get();

 // or specific family member
$member = Member::find(1);

// then in view
echo $member->family->family_name; // or some other field in the family table

As for father being head of the family (tad old fashioned) this could just be a boolean field in the member table 'is_head' maybe?

And finally the auto increment: I'm not sure about this as by default the id will increment when you add a new family.

Not long ago a very deep discussion was brought up on this, and nested sets came up.

If you don't want nested sets, go with a single table called self join.

See http://allenbrowne.com/ser-06.html

you will need to create a family table and then define the relationship

But a son has a son has a son has a son.

So son has dad, has dad, has dad, etc.

I have done a dog pedigree and I stuck with a single table. To me just easier.

Otherwise Billy Bob is in father table and in sons table, becomes a mess.

@jlrdw @cantz I've not thought this through entirely (just off to bed) but could you potentially have a parent_id within the member table to denote ancestry?

EDIT: following on from what @somnathsah said earlier - something like this in the Member.php model:

// A member has one or more children (in the same table)
public function children()
{
     return $this->hasMany(Member::class, 'parent_id', 'id');
}

public function parent()
{
     return $this->belongsTo(Member::class, 'parent_id');
}

Just a thought

or maybe that should be father_id .. then another for mother_id :-)

.. could get complicated .. I should go to bed.

I also built a dog genealogy site and store all 80,000 dogs in one table. Each dog has sire (father) and dam (mother). the id of each references back to the same table through a paternal field(could be father_id) and maternal field (could be mother_id)

There are no other relationships on the dogs apart from breeder and owner so all dogs under the same owner could be listed. But I have not found any need to hold downward looking relationships - just parents.

members of the same family could be considered as children with identical parents (full siblings)

here's most of the relationships that I have found I need, you might be able to take something from this;

    public function mother()
    {
        return $this->belongsTo(Dog::class,'Maternal');
    }

    public function father()
    {
        return $this->belongsTo(Dog::class,'Paternal');
    }

    public function offspringOfMother()
    {
        return $this->hasMany(Dog::class,'Maternal','id');
    }

    public function offspringOfFather()
    {
        return $this->hasMany(Dog::class,'Paternal','id');
    }

    public function progeny()   // this means children
    {   
        if($this->Gender=='B'){
            $query = $this->offspringOfMother();
            return $query;
        }
        $query = $this->offspringOfFather();
        return $query;

    }

    public function fullSiblings()
    {
        return Dog::where('Maternal', $this->Maternal)
            ->where('Paternal', $this->Paternal)
            ->where('id','!=',$this->id)
            ->orderBy('DOB', 'asc')
            ->get();
    }

For instance;

http://borderterrier.org.uk/dogs/show/~m3lFYWpz?depth=4

This is the site in its pre-laravel form. I'm currently in the process of totally re-writing it using Laravel.