Re: RDBMS design issue

On 7/8/2025 1:41 AM, Liz Tuddenham wrote:

Don Y <blockedofcourse@foo.invalid> wrote:
 [...]

I.e., no need for
     Mom   BirthMotherTo   Tom  startdate   forever
because EVERY "People" has exactly one birthmother and birthfather
(even if not known)

 It gets a bit complicated with transgender relationships.  I know a
young girl who has two mothers for all practical and social purposes.
One of them is her biological father - but it is totally irelevant to
her, and the rest of the world, which one that is.
 I presume the problem would also arise with same-sex couples who adopted
a child.  There would be different problems with a heterosexual couple
who adopt an orphan from a war zone where the parents aren't known or
with a single surviving relative who becomes the guardian of an orphaned
child.

Human beings are created by a male and a female -- father and mother.
If the *birth* parents are unknown (as in the case of adoption), then
these are "UNKNOWN".  The adoptive parents can then be indicated:
     Bob  ActsAsFatherTo   Tom ...
     Fred ActsAsFatherTo   Tom ...
I.e., there are no limits on the number of "relationships" that an individual
(or, PAIR of individuals) can be a part of.
But, a "People" (person) always has specific characteristics (fields in the
record):  sex, gender, birthdate, nameatbirth, etc.
A "People" may pass through life without ever having a spouse -- or, may
have many.  May have siblings -- or not.  I.e., these are 1-to-many relations
instead of the 1-to-1 mentioned immediately above.
The structure also lends itself to relationships between other entities
(e.g., businesses, etc.) as the types of relationships that can be
defined isn't constrained by some specific aspect of an entity.
     Bob IsHeirTo  Tom ...

If the actual biological relationship is not important, you could use
"Parent1" and "Parent2".  Years ago there were official forms which used
the terminology "Parent or Guardian".

     Bob IsGuardianOf  Tom ...
     Bob IsChauffeurTo Tom ...
     Bob IsStudentOf  Tom...
I.e., by creating "things" called "relationships" (unfortunately poor choice
of term for a relational database!) and using them to create arbitrary tuples,
you can augment a strict list of "entities" with all sorts of one-to-many
*relations*.