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.

  • kevincox@lemmy.ml
    link
    fedilink
    arrow-up
    8
    ·
    6 months ago

    There is no concrete difference between the two options. But in general they will be similar. I think you are talking about these options:

    struct Person;
    struct Skill;
    
    struct PersonSkills {
        person: PersonId,
        skill: SkillId,
    }
    

    vs

    struct Person {
        skills: SkillId[],
    }
    
    struct Skill;
    

    The main difference that I see is that there is a natural place to put data about this relationship with the “join table”.

    struct PersonSkills {
        person: PersonId,
        skill: SkillId,
        acquired: Timestamp,
        experience: Duration,
    }
    

    You can still do this at in the second one, but you notice that you are basically heading towards an interleaved join table.

    struct PersonSkills {
        skill: SkillId,
        acquired: Timestamp,
        experience: Duration,
    }
    
    struct Person {
        skills: PersonSkills[],
    }
    

    There are other less abstract concerns. Such as performance (are you always loading the list of skills, what if it is long) or correctness (if you delete a Person do you want to delete these relationships, it comes “for free” if they are stored on the Person) But which is better will depend on your use case.