Re: RDBMS design issue

On Mon, 7 Jul 2025 19:32:13 -0700, Don Y <blockedofcourse@foo.invalid>
wrote:

I'm refactoring my the tables that I use to track entities
(most commonly people).
>
I had naively assumed 1:1 relations between certain entries
where that isn't really guaranteed /in all such cases/.
>
E.g., everyone has a (birth) mother and (birth) father.
But, can have various mother or father *roles* at
different times in their lives.  (Bob fathered Tom;
but, Joe ended up being the father figure in Tom's
life -- until Larry replaced Joe as his "mother's"
love interest, etc.)
>
Likewise a person can have multiple spouses (which SHOULD
/legally/ be separated in time, but that's not a known)
>
Anyway, it seems to make sense to add fields for BirthMother
and BirthFather to all People entities.  But, handle all
other relationships with a separate relation -- one that defines
a TYPE of relationship and the TWO parties involved.
>
E.g.,
   Joe   FatherFigureTo  Tom  startdate  enddate
   Larry FatherFigureTo  Tom  startdate  enddate
>
I.e., no need for
   Mom   BirthMotherTo   Tom  startdate   forever
because EVERY "People" has exactly one birthmother and birthfather
(even if not known) just as they have a single birth date, sex,
etc.
>
This then can be extended for non-human entities:
   CompanyA   EmployerOf   Tom   startdate  enddate
>
I can't see any way of avoiding this complexity and still
covering all the bases (e.g, putting an "Employer" field
in each person's record allows for only a single employer
to be tracked)
>
Sadly, I don't see commercial "contact" databases that even
attempt to handle these things intuitively.  E.g., we remember
facts as relationships.  So, if I wanted to find the name of
Tom's coworkers, I would look for his employer and then any
People who have an "EmployeeOf" relationship with that employer.
>
Or, to find the name of his brother-in-law, go to his spouse
and then find her parents and anyone who is male and claims
either of them as a parent.
>
Etc.
>
[I guess when you write code, the idea of chasing pointers
is highly intuitive]

Very complicated for sure.  But what is the *purpose* of this database
to be?  This will lead to what needs to be tracked and how precisely.
And to what can be elided.

Joe