News for hackers. Stuff that matters.

A comment on Making Database Systems Usable. Parent comment

Re:Making Database Systems Usable by Terr_ on Wednesday August 21, 2024 @02:22AM (#41307330)
> Join table that maps to an entity in the middle.

I'm not not sure what you mean. Are you saying that instead of FK/PK relations like:

    user.residence_country_id        = country.id 
    plus user.citizenship_country_id = country.id
You would reify every edge like:

    user.residence_link_id           = link_user_residence.user_id
    link_user_residence.country_id   = country.id
    user.citizenship_link_id         = link_user_citizenship.user_id
    link_user_citizenship.country_id = country.id
Even then, the reverse "I have a country gimme a user" request is ambiguous.
[ Parent | on HN ]
Re:Making Database Systems Usable by Izkata on Wednesday August 21, 2024 @08:02PM (#41315396)
GP misunderstood what an "ambiguous join" is. Rather than a join that returns multiple results, they're more like when you have relationships like this [0]:

  user.residence1 -> country
  user.residence2 -> country

  user.join(country)
You have to specify somewhere which foreign key to follow, it can't be figured out automatically. GP's suggestion doesn't work because there's no way to automatically determine which one to use.

I'm only really familiar with Django, and it handles this type of thing by making you specify the "residence1" name instead of the table name "country".

[0] To refer back to the top of this comment chain, that user is complaining they can't do something like also have a "country->continent" relationship and do "user.join(continent)" and have the system figure out the two joins needed.

[ Parent | on HN ]
Re:Making Database Systems Usable by rawgabbit on Thursday August 22, 2024 @11:57PM (#41326074)
Putting on my dogmatic 3NF purist hat. Instead of denormalized

   user.residence1 -> country.id
   user.residence2 -> country.id
I would create a many to many bridge table. To accommodate multiple residences for a given user.

   bridge.userid-> user.id
   bridge.countryid-> country.id
[ Parent | on HN ]
Re:Making Database Systems Usable by Izkata on Friday August 23, 2024 @06:25PM (#41333609)
Oh definitely, I just couldn't think of a better example off the top of my head. But also not really relevant: You could also have another table and the relationships be A -> B -> D and A -> C -> D, with the desired join between A and D.

Remember this whole thread is about the system figuring out the JOIN criteria automatically.

[ Parent | on HN ]