Database Ups and Downs--parent, partner & offspring events

[Uncle Buddy]

Here's an example of a database feature that has provided no end of entertainment for me, the creator of Treebard, since I started out with no experience and have had to do a lot of things over because of it.

In the base table `person` there's a one-to-one relationship between each person, his person_id number, and his/her gender. I couldn't find a place for anything else in this table. The person_id is referenced in other tables, particularly in `finding` and `findings_persons`. "Finding" is Treebard's shortcut for what the user will call a "conclusion" (vs. Treebard's internal terminology "claim" for the user's "assertion".) To keep it simple, think of "finding" as an event recorded in the database for a person.

Some events are simple--what I've been calling generic events--like "residence" or "occupation", which are linked to one person. Couple events have been a challenge, and I won't try to even summarize all the ups and downs I've experienced in figuring out how to represent an event that has to be linked to two people. For example, marital events and offspring events. An offspring event is experienced by a mother and a father when a child is born. The child experiences the birth event and the parents' offspring event is auto-generated by Treebard if parents are known. Unlike other genieware, Treebard doesn't feel that fathers are able to experience something called "childbirth", so instead we're using the term "offspring". Maybe offbeat but it's more accurate.

In trying to create a table of immediate family to display on the current person tab, I finally realized the database tables I have are falling short. Here are the notes I'm working with as I begin another major revision of all the code that uses database queries involving couple events. The database tables have to be changed, a new table has to be added, and many changes will have to be made throughout the code base. Here's my third set of notes on the topic; the first two didn't cut the mustard:

Up till now I didn't realize I had no way to differentiate, in findings_persons, one couple from another, if one of the partners was unknown. Say for example James has two records of an offspring event with null partners in each. We don't know if the two children are siblings but Treebard will assume they are since the 2 partners are both null. This won't work. There has to be a particular ID for a particular couple, at least if the couple has offspring. The reason I changed from having a person_id column in findings_persons with a separate record for each of the partners in a couple, was that I needed a single row for both persons. But it has been a real pain to write the logic, since I have to check both partners, person_id1 and person_id2, and parse order 1_2 or 2_1 etc. Now I find this complification isn't even capable of doing what needs to be done, so have to change the structure again. Instead of a person_id1 and person_id2 column in findings_persons, there has to be a single FK for a persons_persons table wherein each couple will have a single row and single ID. This might solve all my problems except now I have to rewrite all the code that tried to deal with findings_persons, so have saved a copy of the existing files up to now and will rewrite all that code now after creating the new table and fixing findings_persons table. Also I was hoping to start using M and F with couples since we're talking about biological couples, to avoid the person1 and person2 designations which require extra code. It would be easier to order by MF or FM but this is wrong. If the gender of neither partner was known, then it stops data from being input, if gender is used to do any logic. Yeah but, for example, I've already got hard-coded inputs for Mother and Father so I'm already using gender, just haven't admitted it's necessary. And how many couples are there in which neither parent's gender is known? I think the only way to proceed is to assume that one partner's gender will always be known or guessable, otherwise a lot of work will be done to accomodate a very rare edge case. So the problem remains of what to do with the age and kin_type_id fields in findings_persons. They can't go in persons_persons. Gender is already in the person table since it's a one-to-one relationship with the person. Just have to change the queries to obtain gender from person table? There can now only be one FK for person in the findings_persons table, and it's really an FK for the couple since each row in persons_persons references the person table twice. Although it seems undesirable, I don't see any way around leaving the 2 age columns and 2 kin_type columns in findings_persons, since it is a findings table and that is what's needed. But it has to be renamed with _m & _f instead of _1 & _2.

[Uncle Buddy]

In case anyone's interested, Treebard allows the user to create his own kin types and event types, without restriction as to gender. Treebard also does not automatically use the term "spouse" to refer to members of a couple that had offspring. The user can choose "spouse" or "partner" or whatever.

I don't see any reason to imagine new genders into existence, when it comes to designating parentage. So far I have every reason to believe that every person on earth is born exactly once, has exactly one male father and one female mother. This means I can use "male" and "female" in a limited way, i.e. to designate biological parentage. Treebard doesn't have any rules about who can marry who, what gender combinations can adopt children, etc.

As for actual gender categories, so far I've been using only 'male', 'female', 'unknown' and 'other'. I can't imagine what 'other' might refer to, and not sure I want to imagine it, but in case there's something out there that I don't know about, certainly I must leave room for others to imagine different genders into existence. It's none of Treebard's business, this whole gender thing.

But writing an interface for relationships is not easy, and to make it practically possible, I will have to use "male" and "female" to designate biological parentage, unless someone can come up with (1) a reason why this isn't valid, and (2) another way of dealing with it that doesn't require an extra mountain of code.

[Uncle Buddy]

Now that I'm actually facing the database schema, I see that the age and kin type columns that have to be in findings_persons table can't be _m and _f, at least not as regards couple events. Treebard doesn't care about gender of couples. So the question is: do I use the existing columns age1/age2 and kin_type_1/kin_type_2 only for couple events and create new columns _f & _m for use only with offspring events?

In a way it seems silly to have the extra columns but it does convey information, and due to the strict roles dictated by biological parenthood, it seems like it would be better to do this than to forever be figuring out whether someone is a mother or a father.

On the other hand, gender of a person is already available in the gender column of the person table, so it could be gotten from there.

So let's say a person_id is referenced in a person_id1 column of the persons_persons table (which has an ID for each couple and 2 foreign keys, one for each partner.) Designating gender in the findings_persons table when it's already recorded in the person table would, I believe, denormalize the database. By this I mean it would break one of the rules for database design which states that you should not be recording the same thing in two different places. In this case, if you found out that Michael or Frank was a girl, and changed it in the gender column of the person table, you'd have to remember to change it in the foreign key table too. This is a big no-no. This sort of thing leads to all kinds of trouble.

So the good news is, apparently, that I don't have to change the columns in the findings_persons table. I just have to move person_id1 and person_id2 columns to a new table called persons_persons. But it looks like using _m and _f instead of _1 and _2 is not a good idea.

I still don't like the two age columns and the two kin type columns in findings_persons, and this is why. The _1 and _2 in persons_persons have to correlate to the _1 and _2 in finding_persons. This seems odd and unseemly if not denormalizish. But I have to proceed with it unless something better occurs to me in the next few minutes. The columns also do seem to belong in findings_persons because each record refers to a specific finding (event) so I guess it will be usable if not perfect.

[Uncle Buddy]

As far as I can tell, the operation was a success. I committed to the repo to clear my head. Now to proceed with the tricky procedure of dismantling a partially-finished feature that was threatening to bury me.

I've been working on what I've been calling the "nukes table" for some time. This is a table of the current person's nuclear family. His parents, partners, and children.

I just read something in a blog by Mr. Tamura Jones which asserts we must build multi-parent functionality into our genieware by design instead of (for example) tacking adoptive parents onto a tree as an afterthought. Hmmmm... not a bad directive. Should I redesign everything to... do the impossible? I mean you can only fit so many tree structures onto one view and have it work visually.

Be that as it may (I have to give it some thought), the plan was to bury myself forever in a tangle of user options within the nukes table (which I should rename the "immediate family" table to keep robots from canceling me). But the plan to bury myself in a tangled mess of user options has been rethought and currently I'm of the opinion that the nukes table should be as simple as possible, in keeping with the current person model: if you want to change the current person's parent, you make the parent the current person. If you want to change the person's partner or child, you make the partner or child the current person. Basically, most changes should be doable to the current person only. Anything else gets confusing fast, and people don't enjoy (and seldom bother to learn) confusing software.

Genieware is kind of an exception since all of it is confusing. I believe this isn't necessary so I'm in this race to finish a usable foundation for Treebard GPS before I turn 100.

The nukes table shows, all at once without any further clicking, the current person's parents, partners and children. (Yes Mr. Jones, it should show non-biological parents too, but what if a person was adopted or fostered multiple times? Hmmm... I guess that's what scrollbars are for?) As a minimum exception to the current-person-only-can-be-changed rule, I'm looking at being able to unlink a parent or a partner or a child from the current person. This seems to really not be an exception now that I think about it. It should be pretty easy to do, but the hard part is deciding how much unlinking to do when the user presses delete on a parent, partner or child and says OK to the confirmation dialog.

So I looked at the two geniewares that I own, and got two different ways of doing it that I do not want to emulate.

Starting with Genieware B, if you click a parent, partner or child, an edit screen opens up for that person without making them the current person. Now you have the equivalent of two current persons open at once, which creates instant confusion. I tried changing a fella's wife to a different person but it just changed the name, leaving all the original wife's data intact under the new name. This is not what I want. Of course there's a right way to do it which is learnable and which I could get used to. But the point is that a genieware should be intuitively obvious the first time I try to do anything. When I click a name on a nuclear family table, nothing should happen; the name field should just become the focused field. Then I want to click delete or backspace to open a dialog that tells me what will change if I click OK. I just want to unlink the parent, partner or child from the current person. I don't want to change any existing people or their names or their attributes, without first making that person current.

Genieware A is better because it works in the way I just outlined, but when the dialog pops up I learn that the unlink process will do more than I want. Pressing delete over the current person's father will remove the current person from the "family" represented by the current person's two current parents. To lose the father, he has to lose the mother too. I don't think this is right. In fact, this same software marries parents without any marital events, on the basis of their having a child together. This is very wrong. A spouse is a spouse, not a parent. These are not the same roles.

I looked up an excellent Tamura Jones quote to elucidate how I feel, and how I feel is that the basic element of genealogy is the individual, not the family. The simple reason for this is that individuals are biological facts while families are social agreements, and within even a single culture, there is much disagreement. Genieware based on family units is dishonest, as Mr. Jones has stated in his blog:

Some software is not just based on the simplistic model of traditional genealogy, but on an even more inflexible model and does not even support exceptions, but instead goes out of its way to try and enforce its inadequate understanding of the world on your genealogies. Some ostensible genealogy software still being offered and promoted today does not even support a basic capability such as partnerships other than marriage. FamilySearch PAF is infamous for this unrealistic limitation. It even aggressively asserts, whenever you enter a child, that its parents are married, even if you did not enter any marriage information at all. Software that bluntly asserts all parents are married isn't genealogy software, it is anti-genealogy software.

Current genealogy software is heavily influenced by GEDCOM. Many vendors have been reasonably successful in working around GEDCOM limitations by using its extensions mechanisms.

More than one vendor has complained about the fact that GEDCOM has not been modelled around individuals, events and relationships as a genealogy standard should be, but has instead been modelled around the idea of a family, which is simply not a very practical approach. What's worse, GEDCOM is not really modelled around the concept of family, but around the genealogically limiting model of a nuclear family in a way that does not support other families, forcing vendors to be creative in extending GEDCOM.

Getting back to Genieware A, when I tried to unlink a child from the current person--and the dialog led me to believe that was all that would be auto-done--the child actually lost both his parents. This doesn't seem right. Maybe there's a reason for it, but I suspect that the reason for it is that the software is using the couple as the basic element instead of the individual. If I'm wrong, I'll find out the hard way by trying to write the code like this:

• If the user presses delete over a parent, the current person will be unlinked from only that one parent.
• If the user presses delete over a partner, the current person will be unlinked from that partner. Possibly the marital events between the two people will be deleted, but it might be better to remove the unlinked partner from the events and keep the events linked to the current person. A similar routine might take place with unlinking the couple's children only from the deleted partner, not from the current person. I say "deleted" partner but a person should not be deleted from the tree unless the user makes that person the current person and specifically uses a "delete from tree completely" command.
• Deleting (I mean unlinking) a child should unlink the child from the current person, not the current person's partner.

If this single-person-unlinking approach is wrong, I believe the right way to find out is the hard way: by trying it. I think adding people to the tree should be very easy, but changing relationships should be a little stricter so convoluted mistakes aren't made by convoluted auto-actions taken by the software. If I could unlink a partner from the current person without deleting the marital-type events, I would, but I don't know how that would work. But at least the user has to be informed of what specific events are about to be unlinked from the deleted partner, if he presses OK.

[Uncle Buddy]

The database for person_id 1's biological parents looks like this:
select * from findings_persons where finding_id = 1;
findings_persons_id|finding_id|age1|kin_type_id1|age2|kin_type_id2|persons_persons_id
3|1|33|2|42|1|3
select * from finding where finding_id = 1;
finding_id|date|particulars|age|person_id|event_type_id|date_sorter
1|-1884-ap-7-------||0|1|1|1884,4,7
select * from persons_persons where persons_persons_id = 3;
persons_persons_id|person_id1|person_id2
3|2|3

Treebard auto-creates an offspring event for each biological parent based on the finding_id where event_type_id is 1 (birth event). Such auto-created events use the same finding_id as the child's birth event.

Unlike birth, adoption and fosterage refer to either parents (the fosterers) or child (the one fostered), so a distinguishing word pair corresponding to birth/offspring (for child/parents) isn't necessary in English but would be desirable. It could be correct to say that these are events with up to three participants: a child and two parents. The event could be displayed as fosterage, adoption, or guardianship for all the parties involved. But this would be ambiguous because you'd have to guess based on age which is the child. So the right way is to use more contrived but less ambiguous terms in both the GUI and the code so everyone's role in the event can be instantly understood.

To keep the code symmetrical, the events adoption, guardianship, and fosterage will be treated like birth, as the event experienced by the child. The more contrived terms will be used for the parents: adopted a child, granted guardianship, fostered a child.

Since it's possible for a person to be adopted or fostered by more than one guardian or couple of guardians, the functionality of the kintips will have to be expanded to show guardians when the user points at an adoption, fosterage, or guardianship. The kintips will name the child when the user points as the contrived events listed above. The kintips are in addition to the inclusion of the pertinent names in the immediate family table.

In the case of a guardianship, there's no semantic distinction between the male and female partner if the guardianship is granted to a couple, as there is with terminology like adoptive mother or foster father. If two persons are listed in the same row of persons_persons that's referenced by the finding_id which refers to the adoption event, they would be displayed together as parents. Otherwise, they'd be displayed on separate lines.

The user will have the option of displaying adoptive parents, foster parents, and guardians with the roles feature. For example the user might choose to show adoptive parents who raised a child for many years as parents in the immediate family table, but show temporary foster parents in the roles dialog. The user has to be free to do it either way in any case, because an unofficial foster parent or a legal guardian could raise a child from birth to majority, or for some substantial portion of his childhood, in which case the guardians should be displayed as parents. It has to be up to the user, but to show a pair of persons as parents, the parent system will have to be used, rather than the role system.