Beyond Foreign Keys (lackofimagination.org)
from Aijan@programming.dev to programming@programming.dev on 11 Apr 2024 10:13
https://programming.dev/post/12638514

#programming

threaded - newest

solrize@lemmy.world on 11 Apr 2024 10:21 next collapse

NoSQL has been a thing since before there was SQL.

Aijan@programming.dev on 11 Apr 2024 10:36 collapse

AFAIK, no NoSQL database fully supports SQL, and only some offer support for transactions and joins. The idea here is to augment a relational database by adding capabilities for dynamic relationships.

Kache@lemm.ee on 11 Apr 2024 21:55 collapse

So… a polymorphic many-to-many join table?

Aijan@programming.dev on 11 Apr 2024 22:26 collapse

Yes, that’s correct. Here’s how an entry in the join table looks like:

{
  "id": 6,
  "sourceComp": "user",
  "sourceId": 2,
  "targetComp": "post",
  "targetId": 3,
  "type": "author",
  "createdAt": "2024-03-28T13:28:59.175Z",
  "updatedAt": "2024-03-28T13:28:59.175Z"
}
Kache@lemm.ee on 11 Apr 2024 22:52 next collapse

Fine for prototyping, but adds a scaling tech debt “time bomb” for a live system. Those associations had better be really sparse.

Aijan@programming.dev on 11 Apr 2024 23:05 collapse

There’s certainly the danger of creating too many ad-hoc or sparse relationships, which can cause issues. That said, when used for supplementing foreign keys, Tie-in can be a useful tool in a production system as well.

jkrtn@lemmy.ml on 12 Apr 2024 01:49 collapse

Don’t you want a graph database at this point?

mvirts@lemmy.world on 12 Apr 2024 03:12 next collapse

i was thinking the same thing

Aijan@programming.dev on 12 Apr 2024 08:46 collapse

That idea crossed my mind too, but you can’t really use the full capabilities of SQL in graph databases, and that’s a deal breaker for me.