Path: news.eternal-september.org!eternal-september.org!feeder3.eternal-september.org!border-4.nntp.ord.giganews.com!border-2.nntp.ord.giganews.com!nntp.giganews.com!Xl.tags.giganews.com!local-4.nntp.ord.giganews.com!news.giganews.com.POSTED!not-for-mail
NNTP-Posting-Date: Tue, 08 Jul 2025 14:31:41 +0000
From: Joe Gwinn <joegwinn@comcast.net>
Newsgroups: sci.electronics.design
Subject: Re: RDBMS design issue
Date: Tue, 08 Jul 2025 10:31:18 -0400
Message-ID: <jpaq6kd1bad0esrh8qqhsoecsgqlm13q0p@4ax.com>
References: <104hvve$3ap41$1@dont-email.me>
User-Agent: ForteAgent/8.00.32.1272
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 62
X-Usenet-Provider: http://www.giganews.com
X-Trace: sv3-aVGPX2WPdE2B1IiKRpFV5neG9yQ1LCf6BX1AeiqZSa6+Igq7owi/W+xCw4FEjGhlVZo65sudcfADXWE!0aQH7xvrSEI6vika+WHkRNi3vA90y/BTjlusqUMhAUzd94vJL5vhuSW26c5hE0se5w==
X-Complaints-To: abuse@giganews.com
X-DMCA-Notifications: http://www.giganews.com/info/dmca.html
X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers
X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly
X-Postfilter: 1.3.40

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