Storing order of relation as JSON array

Question

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.

Answer 1

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:

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://learn.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many
• https://www.vertabelo.com/blog/many-to-many-relationship/