Link to same field in same table (1 Viewer)

[TB11]

How to link to the same field in same table?

People table
ID, FirstName, LastName
1, Jane, Doe
2, John, Doe
3, Scott, Doe
4, Sue, Doe

Family Tie table
ID, Family Tie
Mother, Father, Wife, Husband, Sister, Brother

Jane = Wife of John, Mother of Scott, Mother of Sue
John = Husband of Jane, Father of Scott, Father of Sue
Scott = Son of Jane, Son of John, Brother of Sue
Sue = Daughter of Jane, Daughter of John, Sister of Scott

I'm stumped.

Any help would be appreciated.

 

[jdraw]

Genealogy databases can get quite complex.
Here're a few video links by Richard Rost to help with concepts
1: https://www.youtube.com/watch?v=ZJV0AmPKgHs
2:

You may also review Gedcom for more info on genealogical structures.

 

[plog]

Since you brought gender into your desired results, and you haven't a gender field in People and because you are discretely defining every relationship (and not just position in nuclear family) your FamilyTie should be structured like so:

FamilyTie
ft_ID, autonumber, primary key of table
ID_peopleMain, number, foreign key to people will fulfill the X portion of 'X Relationship of Y'
ID_peopleRelated, number foreign key to people, will fulfill the Y portion of 'X Relationship of Y'
ft_Relationship, text, will tell what X is to Y (e.g. brother, mother, father, etc.)

Then for every relationship a person (e.g. John) has to another person(e.g. Sally) you will put 2 records into FamilyTie (John father of Sally & Sally daughter of John). Then in a query you link ID_peopleMain to People and ID_peopleRelated to another instance of People.

 

[arnelgp]

it's the same as BOM table.

 

[TB11]

it's the same as BOM table.

@arnelgp what is a BOM table?

 

[TB11]

Thank you. @jdraw Richard Rost is my go to, as well. I already watched one of his Genealogy courses this morning. Learned a few tricks and tips. @plog I will try your suggestions tomorrow.

 

[Pat Hartman]

The relationship is m-m. You need a junction table and in the junction table, you would keep the relationship as ancillary data. The relationship is always how person1 is related to person2 to avoid confusion.

 

[Cotswold]

TB11 you may find it helpful to search the forum for "Family History", "Family Tree" and "Genealogy"

It is invariably the case that whatever you want to do, someone else has already done it or asked it

 

[TB11]

@plog Awesome! I just couldn't think of how to approach the second table for many to many.

 

[arnelgp]

this is a sample of BOM.
Microsoft Word - BOM-Access.doc (csusm.edu)

 

[The_Doc_Man]

Having done an Ancestry.COM project with a few generations in it (like, >10), I have to say that the junction table is the ONLY reasonable way to go to retain any flexibility. It gives you the ability to trace in either direction (parent or child) based on which "side" of the junction table you are examining. If you are trying to describe relationships, having the relationship in the junction table is right. Depending on how you do it, you can say - if going from parent to child, you can apply the label SON or DAUGHTER to the child based on the child's gender. And if you are going the other way, you can apply the label FATHER or MOTHER to the parent depending on the parent's gender.

But here's where the most fun (and most confusion) steps in. As it happens, I needed extended relationships for one of my step-daughters because she has four fathers: her biological father, her father through legally still being married to her biological mother (but he had unofficially split even before that baby was conceived), her adoptive father (when her mother died of an illness and neither of the first two fathers wanted her), and me (who married her adoptive mother - who was also biologically her aunt - when the adoptive father split.) So all I had to do was define codes for "biological" "through marriage" and "adoptive" for parentage. Talk about a fun setup!

Seriously, having a single table of people and a single junction table to relate one person to another gives you the most flexibility and the option to even map out the relationships implied in this video:

 

[gemma-the-husky]

Really the only true link you need in a genealogy table is child-mother and child-father - This way, not the other way.

A child has two parents. A sibling is any other child with the same parents. A person's children are other persons with the subject person identified as parent. A cousin is a person whose parent is a sibling of one of your parents. It's all recursive and queries and quite easy to manage. This tracks the genetic lineage without any issues.

The issues arise when you start including marriages, re-marriages and you then need to track step-relationships, half-siblings, illegitimacy and so on. (as @The_Doc_Man just pointed out) Much easier with horses than people.

So you have to decide exactly what you want to track in your database.

 

[Galaxiom]

The issues arise when you start including marriages, re-marriages and you then need to track step-relationships, half-siblings, illegitimacy and so on.

And not to forget the possibility of mitochondrial donors as a third genetic parent.

 

[Pat Hartman]

Wait until they start insisting on "pronouns" in the family tree

 

[plog]

And to bring back to databases, a tree often isn't the best relationship analogy. Ive know some family lines that are best represented by spider webs.

 

[The_Doc_Man]

Mark Twain was known to mention that HIS family was so disreputable that the family tree had only one branch with a rope and noose dangling from it.