I have a system that requires data models which are 'siblings', all part of the same collection that users should be able to reorder.
Some concrete examples would be: A playlist containing many songs, an album containing many photos, a blog containing many posts, etc.
Because of other constraints, these data models are stored inside a relational SQL database.
However, relational databases do not naturally allow 'siblings' to be stored. I am looking for the correct approach to model the required invariants for this system in a relational database, especially when concurrent inserts/updates/deletions might happen.
I know of the following approaches:
1) Every song has a order integer column.
On insert: All songs part of a given playlist increment their order, the new song's order is set to 0.
On delete: The required song column is removed; All songs above the given song's order's order are decremented.
On changing the order: Increment/Decrement (depending on the new order value being lower/higher than the old order value) the order of all songs in-between the old and new position of the song column, and alter the order of the required song to the new index.
Main problem is that, unless you use serializeable transactions, the ordering might get messed up when multiple songs are added/moved at the same time, right? Any way to prevent this?
2) Every song has a previous_song_id column which points to its previous song, building something akin to a linked-list.
It seems to me that this is an antipattern that will cause N+1-query problems, but I am not sure of the queries involved of doing this in practice.
3) Store the order in an array field on the playlist. This does not have the problems of 1) or 2), but I am not sure how to only fetch the first N songs if the order depends on this array field. (i.e. how to write: SELECT * FROM songs WHERE songs.playlist_id = 1, ORDER_BY ???, LIMIT 10?)
As mentioned above, all of these approaches have their drawbacks.
I wonder if there are other ways to map a system like this to a relational database? Or how the drawbacks of one or more of these techniques can be mitigated?
These kinds of relations seem relatively common to me; I have encountered them multiple times but still do not know the proper approach to implement them. Nor do I actually know how to properly call such a system, other than something like 'ordered siblings relation'.
Help is greatly appreciated.