Sorry it's taken me so long to get back to you re real world parents and children
It's the example you gave of Cache relationships - and I was a bit quick fire with my answer - sorry!.
Please let me expand.....
Real world parents and children is an interesting problem and a simple solution that you described is *not* the way I would model it!!
this is regardless of the actual implementation - eg parent/child or foreign keys etc etc
If i wanted to model family trees I would have a class "Person" and another class "PersonRelationship" - the second of these would have links to two instances in the "Person" table
Something like (in relationships rather than foreign keys - but that's implementation rather than the oo design)
PS - I am typing on the fly - so there may be errors!!!!
Class Person as %Persistent
Relationship rRelatedPerson as PersonRelationship [cardinality="many", inverseproperty="rLink"]
property Name as %string;
property pDoB as %Date;
And then PersonRelationship as %Persistent
Relationship1 as Person[cardinality="one", inverseproperty rRelatedPerson]
Relationship2 as Person[cardinality="one", inverseproperty rRelatedPerson]
property RelationshipType as SomeLookupTable;
The SomeLookupTable would describe (in words) the relationship eg "Son Of" and "Farther Of"
For me this has some beauty
- Construct a family tree of infinite depth both forwards and backwards
- Use recursive SQL programming to construct the tree
- a "child" can have multiple links - eg "gene father" and "step father"
- The record is complete - eg a woman might have a "gene father" then a "step father" and then go back and be linked to the same "gene father" (life happens)
- It can also model surrogate parents via AI fathers or surrogate mothers or same sex relationships
- It can be extended to, say, pets
Some care has to be taken in the database insert/amend eg
- avoid recursive relationships eg a Person is her own grandmother is not physically possible
- a person cannot have the same mother and father (well with AI and embryo manipulation tailoring this *may* become a reality - but no problemo the model will still work)
Hope this is clear and of interest - if you need any more info please ask
PS - I was involved around 30 years ago in computing an "In Breeding Coefficient" for rare breeds (think small populations of endangered species in zoos) the aim was to give a metric on how inbred an individual was - small populations where is was common for both grandparents to be the same individual - the logic was intense to get a metric - you could have the case where the same individual was all of the both grandfathers and all 4 great grandfathers- not so good for preserving the gene pool !