Benefits of a join table vs. array to express relations? (outside of SQL)

I am building an application that is using JSON / XML files to persist data. This is why I indicated “outside of SQL” in the title.

I understand one benefit of join tables is it makes querying easier with SQL syntax. Since I am using JSON as my storage, I do not have that benefit.

But are there any other benefits when using a separate join table when expressing a many-to-many relationship? The exact expression I want to express is one entity’s dependency on another. I could do this by just having a “dependencies” field, which would be an array of the IDs of the dependencies.

This approach seems simpler to me than a separate table / entity to track the relation. Am I missing something?

Feel free to ask for more context.

abbadon420,

One example could be to add a value to the relationship, like a rating or a ranking.

For example a Movie can be seen by many Users and a User can see many Movies. A user can rate the movie they’ve seen between 0 and 10. So, the join table would have 3 collumns:

  • he FK for User
  • the FK for Movie
  • the numerical value of the rating by that particular User for that particualr Movie.
matcha_addict,

That’s a good point!

deegeese,

The JSON version of this is to store an array of relation objects which express the weights.

In my opinion the main advantage of a “join table” in your situation is the ability to look up the relationship from either direction while only storing a single copy of it.

If you store the relation in the object, becomes very easy for A’s relation to B to get out of sync from B’s relation to A.

andrew,
@andrew@lemmy.stuart.fun avatar

The other related advantage is being able to update data about a given B once, instead of everywhere it occurs as a child in A.

  • All
  • Subscribed
  • Moderated
  • Favorites
  • •
  • programming@programming.dev
  • kavyap
  • ngwrru68w68
  • tacticalgear
  • DreamBathrooms
  • mdbf
  • magazineikmin
  • thenastyranch
  • Youngstown
  • khanakhh
  • everett
  • slotface
  • Durango
  • rosin
  • osvaldo12
  • megavids
  • normalnudes
  • cubers
  • GTA5RPClips
  • InstantRegret
  • cisconetworking
  • anitta
  • ethstaker
  • Leos
  • modclub
  • tester
  • provamag3
  • JUstTest
  • lostlight
  • All magazines
    •