Thought I had this worked out, but turns out it doesn’t
I have TWO tables
This table could have 1 or 2 records (Just Mother, Just Father, or Mother and Father)
ID_PERSON is the “child”
ID_PARENT is the Parent (mother or father)
This table indicates a relationship of some type, where each ID relates to one of those people
For a given Focus_PERSON, they could
have one or more partners and have NO CHILDREN with a given partner (ie there would be no entery where FOCUS_PERSON is the ID_PARENT in PARENTS table, but is either ID_PERSON or ID_PARTNER in the PARTNERS table
have one or more partners and have one or more children with a given partner (there would be matching records in both tables)
have a child where the focus is recorded as a parent, but there is no partner recorded
The problem is that a person could have had multiple partners and had one or more children with each one
What I am trying to accomplish is a single SQL Query that will return PARTNER and CHILD given a single FOCUS_ID
It should return records like this
Partner001 : Child001 : FocusID was a partner with Partner001 and had Child001
Partner002 : * : FocusID was a partner with Partner002 and had no children
Any entries that are the result of Partner001 or Partner002 being involved with any other person should be ignored
The problem is PARENTS… since each PARENT is a single record (or and to make it worse, there could be more than one Mother and/or Father [biological, adopted, step, foster etc)
After re-reading everything again, this doesn’t make sense. You state that PARENTS.PERSON_ID is the child, yet your list of possibilities for a FOCUS person is all about that person’s partners and children. It sounds like the FOCUS person can match to either ID in both tables. If this is not the case, then it would help if you broke this down by each table separately first so i (we?) can get a better understanding.
Ok. In this scenario, what if the record in Parents of “ID=SUZY Parent=Dad” was not there? Would your second result line still be included? In others words, do both parents have to be in the Parents table for a child to be listed as “their” child?
Seems to me you need two tables - person & relationship. The person table holds all the details of the person (obviously) while the relationship table links 2 persons together and has a relationship type as a minimum - it should also include a beginning & termination date for partnerships. Your relationship types are bidirectional e.g. Parent/Child, Husband/Wife, Partner/Partner.
With this you can now query the relationship table for the focus person on either person1 or person2 and get all relationships.
No… since it would be looking for the progeny of “DAD”, Suzy would not appear since “DAD” is not one of her parents
Wayne… there is a PERSON table as you describe, but that is not relavant to this query… once I had the associated ID’s getting the demographics is a snap
And the PARTNERS table contains all the “known” relationships
but just because two people are partners doesn’t mean they had children
and it is possible that only one parent is known
and unlike other Genealogy programs… this one supports the concept of PARTNERS… not “Spouse”, that is just a type of partner
Yes it does happen… that is why I am trying to support non-biological (and even same sex marriages)… So far to my knowledge that has not happened on my family tree… and while I am not writing this to be a commerical application, no harm in having a flexible design
Here is a thought. Starting from your ID_PERSON, I would suggest a RELATIONSHIP table. The key to this new table would be ID_PERSON, partner type, sequential number (in case for example there are more than one “spouse” type relationships). ID_PERSON2 would be the characteristic (there may be others). You can handle pretty much any kind of relationship, up or down in the tree. You can search by partner of by partner2.
In this, Say Arthur is Bob’s father. The table would look something like this: Arthur, Son, 0, Bob. Bob is Arthur’s first son. Now, you will need to establish some rules to avoid duplicating information. You would not need another entry with Bob, Father, 0, Arthur. Or perhaps it does not matter. You know your goals best.
Partner types would be something like spouse, kin (brother or sister), parent (or any type of more explicit parent: mother, father, step-whatever). You can be as detailed or as generic as you like.
I agree with most of what you said. Tracking the first born is an “accidental” result. Let’s say it is simply the first of similar relationships that was recorded. This is more accurate.
I wish my father was still alive, he was an internationally known expert on a few families including of course our own. He used to spend hours on the phone with people from all over the world seeking genealogical information that was sometimes hard to find. I have a copy of the wedding certificate of one of my ancestors signed as witness by Jean Talon, then Intendant of Nouvelle France. (actually the colony was small and he attended all weddings…) My father would would have loved to share ideas with you. He was using a system similar to what I described, all on paper because computers did’nt exist yet. His numbering system allowed him to describe within a few minutes the level at which 2 persons were related. His system was patriarcal, with spouse and children recorded under the patriarcal key.
But as I said, you know your goals best. I was just sharing a different approach for your consideration. Cheers.