How would you make a family tree database?

Question

It's much harder than I thought (I'm pretty much a noob at this)

At first I was thinking of storing the individual's id in one column and how the individual relates to other individuals in the other.

e.g.:

|| Tom || Gary:Brother; Joe: Father; Janet:Mother ||

--------------------------------------...

|| Gary || Tom:Brother; Joe:Father; Jane:Mother||

etc..

But this has so many problems i can't even list them all out.

The obvious problem is that this database would grow exponentially in size, and each time a new person is added, all the previous data would have to be changed (e.g. Tom adds a new boy, then all of Toms siblings would have to have a new nephew added).

So then I was thinking that each person only store the relational information of people directly related. That way Tom's brother would read Tom's data and find Tom's son's data and then in turn read that.

The problem with this is that the database would have to be read each time a person if found (If someone reads Gary's info, the database would have to be accessed 3 times to read Tom Joe and Janet's relational info, and if those turn up new 5 relations then the database would have to be accessed another 5 times) and family trees tend to be huge, so that's very inefficient..

So how are you supposed to make a family tree database..?

No links to Ancestry.com please:)

Answer 1

There are lots of ways to do this. The simplest record structure results from just storing a person's parents, then using queries to find that person's siblings when needed. So:

Person Record:

Person ID

Name

DOB

etc.

Father ID

Mother ID

The problem with this is that people have birth parents, step parents, adoptive parents, etc. They also have multiple spouses. So you might consider tables like:

Person Record:

Person ID

Name

DOB

etc.

Marriage Record:

Person ID 1

Person ID 2

Marriage date

Termination reason (death, divorce, etc.)

Parent Record:

Parent ID

Child ID

Relationship (birth, adoption, legal guardian, etc.)

This creates a more complicated database but it's easier to express complex relationships.

If Bob and Sue have sons Jason and Jared, then Bob dies and Sue marries Bill (who has sons Jeff and Jim by Ann) and has Jenny and Julie you end up with:

Persons

Bob

Sue

Jason

Jared

Bill

Jeff

Jim

Ann

Julie

Jenny

Marriage

Bob and Sue

Bill and Sue

Bill and Ann

Parent

Bob is father of Jared

Bob is father of Jason

Sue is mother of Jared

Sue is mother of Jason

Bill is father of Jeff

Bill is father of Jim

Ann is mother of Jeff

Ann is mother of Jim

Sue is mother of Jenny

Sue is mother of Julie

Bill is father of Jenny

Bill is father of Julie

Sue is mother of Jeff by adoption

Sue is mother of Jim by adoption

You can find the birth parents and direct siblings of any person by examining the parent records.

I'm sure there's more to it than this. I've implemented a very simple system like this for church/club membership management and I just remember it was really complicated to get it all right.

Answer 2

Have you tried geni.com? I have my family tree set up there, and it is quite easy to use.