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@lemm.ee
    7·
    5 months ago

    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.