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.

    • deegeese@sopuli.xyz
      link
      fedilink
      arrow-up
      3
      ·
      6 months ago

      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@lemmy.stuart.fun
        link
        fedilink
        English
        arrow-up
        2
        ·
        6 months ago

        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.