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.

  • mox@lemmy.sdf.org
    link
    fedilink
    arrow-up
    4
    ·
    6 months ago

    Conceptually, the benefit of a join table is to allow many-to-many relations. That’s it.

    If I understand you correctly, your relations are one-to-many, so a join table would just be needless complexity.

    • matcha_addict@lemy.lolOP
      link
      fedilink
      English
      arrow-up
      1
      ·
      6 months ago

      The list would still allow a many-to-many relationship. Let demonstrate:

      entity A and entity B both have 2 members: A-1, A-2, B-1, and B-2.

      we add a “relations” field to entity A, which is a list of IDs from B, describing the list of B’s that A is related to.

      A-1 has the relations field as: [B-1, B-2] and A-2 has [B-1, B-2].

      As you can see, this is a many-to-many relationship. Each of our entities is tied to multiple entities. So this is many on both sides, hence many to many

      • mox@lemmy.sdf.org
        link
        fedilink
        arrow-up
        1
        ·
        edit-2
        6 months ago

        That’s overly complicated to my eyes, and not really relevant. The point I was trying to make is just that a join table is unnecessary in the situation you originally described.

        • matcha_addict@lemy.lolOP
          link
          fedilink
          English
          arrow-up
          1
          ·
          6 months ago

          What I described in the comment above is the same thing I originally described, but expanded.

          A dependency relation can still be many to many (and in my case, it is). The comment above gives an example to prove it.