Best way to store ordered lists in a database?

Question

What's the best way to store "ordered lists" in a database, so that updating them (adding, removing and changing the order of entries) is easily done?

Consider a database where you have a table for users and movies. Each user has a list of favorite movies.

Since many users can like the same movie, I made users and movies separate tables and uses a third table to connect them, usermovies.

usermovies contains an id of a user and a movie and an "order number". The order number is used to order the list of movies for users.

For example, user Josh might have the following list:

1. Prometheus
2. Men in Black 3
3. The Dictator

and user Jack might have a list like:

1. The Dictator
2. Prometheus
3. Battleship
4. Snow White and the Huntsman

So, they share some favorites, but not necessarily in the same order.

I can get the list of movie IDs for each user using a query:

SELECT movie_id FROM usermovies WHERE user_id =? ORDER BY order_number

Then, with the ordered movie_ids, I can get the list of movies using another query

SELECT name FROM movies WHERE id in (?,?,?) ORDER BY FIELD (id, ?,?,?)

So queries work, but updating the lists seems really complex now - are there better ways to store this information so that it would be easy to get the list of movies for user x, add movies, remove them and change the order of the list?

Answer 1

If you are not looking for a "move up / move down" kinda solution, and then defaulting to adding at the bottom of the list, here are a few more pointers:

Inserting new rows into a specific position can be done like this: (inserting at position 3)

UPDATE usermovies SET order_number = ordernumber + 1 
   WHERE ordernumber > 3 and user_id = ?;
INSERT INTO usermovies VALUES (?, 3, ?);

And you can delete in a similar fashion: (deleting position 6)

DELETE usermovies WHERE order_numer = 6 and user_id=?;
UPDATE usermovies SET order_number = ordernumber - 1 
   WHERE ordernumber > 6 and user_id = ?;

Answer 2

A junction/link table with additional columns for the attributes of the association between movies and users is the standard way of realizing a many-many association with an association class - so what you have done seems correct.

Regarding the ease of insert/update/delete, you'll have to manage the entire association (all rows for the user-movie FKs) every time you perform an insert/update/delete. There probably isn't a magical/simpler way to do this.

Having said this, you'll also need to run these operations in a transaction and more importantly have a 'version' column on this junction table if your application is multi-user capable.