0

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.

1 Answer 1

0

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:

2
  • I know it would be m:n relationship with join table. The question is if it's better to store ordering in JSON array on Playlist or in join table itself.
    – Damaon
    Commented Feb 1, 2022 at 11:59
  • You're using an RDBMS, using a relational data model is the way to go. You might end up with not enough data to see the difference or maybe your database will grow so much you'll see the difference...
    – Arkhena
    Commented Feb 1, 2022 at 12:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.