How Many Database Tables For Couple Events?
Apr 9, 2022 3:57:17 GMT -8
Post by Uncle Buddy on Apr 9, 2022 3:57:17 GMT -8
(nomenclature: "finding" is the word used in Treebard code for "conclusion". In Treebard, events and attributes--or what some programs call "facts"--have two identities. Instead of pretending that we know what a fact is, in Treebard we acknowledge that there are conclusions made by the researcher based on assertions made by the sources. The conclusions go on the events table, which should be called the conclusions table, and each conclusion or event/attribute is linked to any number of supporting assertions which are linked to any number of sources. Conclusions and assertions are called findings and claims in the code to save on the typing.)
The most confusing part of recording couples is not being able to use gender to delineate partners into separate roles. Since you can't do this part of the time, you can't do it any of the time, with the only exception being that Treebard tries to call male biological parents "fathers" and female biological parents "mothers". But in the database, we have to use columns like person_id1 and person_id2. Since fathers and mothers do arrange according to two basic biological genders, we use 1 for fathers and 2 for mothers, and in the GUI we use left for fathers and right for mothers, because everyone else does, so it would be confusing not to. When there is a male and a female we use left for male and right for female. In the case of same-sex partners, left and right are interchangeable, so a partner recorded into the left side of the families table will go into the column labeled 1, e.g. person_id1 or kin_type_id1, with no regard for gender. Since he's input to that column, on loading him into a new families table he will be displayed where he was input, on the left. Partners input on the right will be displayed on the right. So, for example, if the user inputs a foster father on the right and a foster mother on the left, that's how they will always display. In fact, if the user inputs biological parents backwards, they will display backwards. So the sky's the limit, just input your males and females wherever you want them to display, and that's where they will display. If Dad is Glenda and Mom is Richard, Treebard doesn't care.
The real confusion comes about as to why there are three database tables (finding, findings_persons, persons_persons) for recording a single couple finding. Each finding_id is used in only one record of findings_persons, and each persons_persons_id is used in only one record of findings_persons. So it seems like this is a one-to-one relationship and the tables should be combined. It could be that this design, or mal-design if that's what it is, exists because originally there had to be two rows in findings_persons for each finding_id. Then I changed it so that there was a person_id1 and a person_id2 in the same row of findings_persons, which means there's now one use of a finding_id in findings_persons. Then I changed the design again so that person_id1 and person_id2 were in a single row of persons_persons. Presumably this means that the same persons_persons_id can be used in many rows of findings_persons, e.g. for each child of a given couple. But is this necessary? The current tables look like this:
finding_id | date | particulars | age | person_id | event_type_id | date_sorter
findings_persons_id | finding_id | age1 | kin_type_1 | age2 | kin_type_id2 | persons_persons_id
persons_persons_id | person_id1 | person_id2
What's wrong with a single table that looks like this, wherein age and person_id will be used for generic events. But for couple events and birth events, we'd leave age and person_id null and use age1/age2/person_id1/person_id2/kin_type_id1/kin_type_id2... all in the same finding table. There could also be father_id and mother_id columns, and the person_id1/person_id2 columns would be used for couple events and alt parentage events. I don't see what's wrong with this, assuming that there are only two biological parents for an offspring, two alt parents for a fosterage/adoption/guardianship, and two partners in any kind of couple. I'm not quite ready to start tracking love triangles, group love, or sex cults, but in case the sexual roles in society get any more complicated than "three's a crowd", I doubt anyone in the field of genealogy is going to want to keep track of it. Currently the roles functionality in Treebard could be used for oddball stuff like that.
It would simplify the existing code greatly to get rid of the three-table arrangement. I'm pretty sure I went down that road due to an early and wrong assumption that each person in findings_persons needed his own row, and instead of canning that by putting the two couple-person rows into the finding table, I created a third table which helped but it was a bit off, and I keep noticing over and over that finding_id, findings_persons_id, and persons_persons_id seem to have a one-to-one relationship. It's possible that, in my neophytical confusion, I've forgotten a good reason I once had for this 3-table arrangement, and if so, I'll update this discussion with what that reason is.
Actually you could track each distinct couple in something equivalent to persons_persons table but I don't think I'm doing it and I don't want to. I don't see any purpose (other than being like GEDCOM) to giving couples and families the status of elements. They are by definition not elements of genealogy, because individuals are genealogical elements and couples/families are made up of individuals. Hydrogen and oxygen are elements; water is not. To find out everything you need to know about a couple, you'd just query the finding table to see where any distinct pair of individuals occur together in the same row. As for the family unit, there's no such thing. It's a social fiction, an agreement that can, should, and does change depending on who's observing it.
I know, this is heresy because it will make GEDCOM more obsolete than ever. But until someone takes the trouble to create a file-sharing utility that conforms to reality instead of forcing genealogy to conform to GEDCOM's archaic, in-built oversimplifications, genealogists should face the fact that their hard work is not accurately importable or exportable among different programs. And as long as app developers base their designs on GEDCOM--which is not a database and doesn't work like one--many complex relationships can't even be recorded to begin with, much less shared to a different application.
We have to stop fooling ourselves. GEDCOM is not about genealogical accuracy. It's about expediency at the expense of accuracy. Treebard is being created as an alternative to GEDCOM, and until there are two programs as good as Treebard, there's actually no need for a file-sharing utility. Treebard's data structure is intended to be so much better than the others--and so much more accessible--that it only serves to underscore one of our slogans down here at Treebard University: "Forget GEDCOM. Just share the whole program."
The most confusing part of recording couples is not being able to use gender to delineate partners into separate roles. Since you can't do this part of the time, you can't do it any of the time, with the only exception being that Treebard tries to call male biological parents "fathers" and female biological parents "mothers". But in the database, we have to use columns like person_id1 and person_id2. Since fathers and mothers do arrange according to two basic biological genders, we use 1 for fathers and 2 for mothers, and in the GUI we use left for fathers and right for mothers, because everyone else does, so it would be confusing not to. When there is a male and a female we use left for male and right for female. In the case of same-sex partners, left and right are interchangeable, so a partner recorded into the left side of the families table will go into the column labeled 1, e.g. person_id1 or kin_type_id1, with no regard for gender. Since he's input to that column, on loading him into a new families table he will be displayed where he was input, on the left. Partners input on the right will be displayed on the right. So, for example, if the user inputs a foster father on the right and a foster mother on the left, that's how they will always display. In fact, if the user inputs biological parents backwards, they will display backwards. So the sky's the limit, just input your males and females wherever you want them to display, and that's where they will display. If Dad is Glenda and Mom is Richard, Treebard doesn't care.
The real confusion comes about as to why there are three database tables (finding, findings_persons, persons_persons) for recording a single couple finding. Each finding_id is used in only one record of findings_persons, and each persons_persons_id is used in only one record of findings_persons. So it seems like this is a one-to-one relationship and the tables should be combined. It could be that this design, or mal-design if that's what it is, exists because originally there had to be two rows in findings_persons for each finding_id. Then I changed it so that there was a person_id1 and a person_id2 in the same row of findings_persons, which means there's now one use of a finding_id in findings_persons. Then I changed the design again so that person_id1 and person_id2 were in a single row of persons_persons. Presumably this means that the same persons_persons_id can be used in many rows of findings_persons, e.g. for each child of a given couple. But is this necessary? The current tables look like this:
finding_id | date | particulars | age | person_id | event_type_id | date_sorter
findings_persons_id | finding_id | age1 | kin_type_1 | age2 | kin_type_id2 | persons_persons_id
persons_persons_id | person_id1 | person_id2
What's wrong with a single table that looks like this, wherein age and person_id will be used for generic events. But for couple events and birth events, we'd leave age and person_id null and use age1/age2/person_id1/person_id2/kin_type_id1/kin_type_id2... all in the same finding table. There could also be father_id and mother_id columns, and the person_id1/person_id2 columns would be used for couple events and alt parentage events. I don't see what's wrong with this, assuming that there are only two biological parents for an offspring, two alt parents for a fosterage/adoption/guardianship, and two partners in any kind of couple. I'm not quite ready to start tracking love triangles, group love, or sex cults, but in case the sexual roles in society get any more complicated than "three's a crowd", I doubt anyone in the field of genealogy is going to want to keep track of it. Currently the roles functionality in Treebard could be used for oddball stuff like that.
It would simplify the existing code greatly to get rid of the three-table arrangement. I'm pretty sure I went down that road due to an early and wrong assumption that each person in findings_persons needed his own row, and instead of canning that by putting the two couple-person rows into the finding table, I created a third table which helped but it was a bit off, and I keep noticing over and over that finding_id, findings_persons_id, and persons_persons_id seem to have a one-to-one relationship. It's possible that, in my neophytical confusion, I've forgotten a good reason I once had for this 3-table arrangement, and if so, I'll update this discussion with what that reason is.
Actually you could track each distinct couple in something equivalent to persons_persons table but I don't think I'm doing it and I don't want to. I don't see any purpose (other than being like GEDCOM) to giving couples and families the status of elements. They are by definition not elements of genealogy, because individuals are genealogical elements and couples/families are made up of individuals. Hydrogen and oxygen are elements; water is not. To find out everything you need to know about a couple, you'd just query the finding table to see where any distinct pair of individuals occur together in the same row. As for the family unit, there's no such thing. It's a social fiction, an agreement that can, should, and does change depending on who's observing it.
I know, this is heresy because it will make GEDCOM more obsolete than ever. But until someone takes the trouble to create a file-sharing utility that conforms to reality instead of forcing genealogy to conform to GEDCOM's archaic, in-built oversimplifications, genealogists should face the fact that their hard work is not accurately importable or exportable among different programs. And as long as app developers base their designs on GEDCOM--which is not a database and doesn't work like one--many complex relationships can't even be recorded to begin with, much less shared to a different application.
We have to stop fooling ourselves. GEDCOM is not about genealogical accuracy. It's about expediency at the expense of accuracy. Treebard is being created as an alternative to GEDCOM, and until there are two programs as good as Treebard, there's actually no need for a file-sharing utility. Treebard's data structure is intended to be so much better than the others--and so much more accessible--that it only serves to underscore one of our slogans down here at Treebard University: "Forget GEDCOM. Just share the whole program."