Storing order of relation as JSON array



  • Let's assume I have Playlist, Song and playlist_song tables.

    Where's it's better to store song ordering data?

    In Playlist as JSON songs_order = [song_id_1, song_id_3, song_id_2] or better on playlist_song.order.

    I am more tempted by storing it on Playlist but would like some opinions about this. I prefer it because in second option there's another issue how to limit updates - https://softwareengineering.stackexchange.com/questions/304593/how-to-store-ordered-information-in-a-relational-database

    Constraint is that Playlist will have no more than 1000 songs.



  • Normally, a song can have several ordering data and the playlist can have several ordering data for the same song (as a song can be several times on the same playlist). In the following model, I decided a playlist could have 0 sog, you could easily change that to a mandatory rule to have at list one song in a playlist and wouldn't change how the problem would be solved.

    Here's how to model your problem:

    Data model for playlists-songs relationship

    That's a classical 0n-0n relationship (which belongs to the many-to-many relationship kind). We'll solve that by creating a new table belong(playlist_id, song_id).

    More details here:

    • https://docs.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many
    • https://www.vertabelo.com/blog/many-to-many-relationship/



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2