27

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?

wannabeartist
  • 2,753
  • 6
  • 36
  • 49
  • 1
    To get all movies for a user you could use a single query like this: `SELECT name FROM movies INNER JOIN usermovie ON usermovie.movie_id = movies.id AND usermovie.user_id = ? ORDER BY usermovie.order_number`. And I don't see whats difficult with the current approach? To add a movie to a user you simply insert a new entry in your linktable and to remove it you delete the entry. – Cyclonecode Jun 13 '12 at 05:50
  • thanks! That will come handy, although my real problem here is the updating. – wannabeartist Jun 13 '12 at 05:52

4 Answers4

8

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 = ?;
Kerbocat
  • 378
  • 1
  • 9
7

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.

Ryan Fernandes
  • 8,238
  • 7
  • 36
  • 53
  • 1
    In fact, *there is* such a "magical" way to do the task with changing just one row instead of all rows: http://stackoverflow.com/a/3399334/2947812 – Mikhail Batcer Sep 14 '15 at 09:43
  • 2
    @MikhailBatcer a solution that requires a whole table redistribution after (on the order of) 44 reorderings is decidedly less than magical. – Patrick Dec 06 '18 at 00:08
4

To retrieve user favourites movies you could use a single query:

SELECT um.order_number, m.name FROM movies m
INNER JOIN usermovies um ON m.id = um.movie_id
WHERE um.user_id = ?
ORDER BY um.order_number

To add/remove a favourite movie simply add/remove related record in usermovies table.
To alter a movie order simply change all order_number field in user_movies table related to user.

Marco
  • 56,740
  • 14
  • 129
  • 152
  • Thanks, I'm still not sure how to handle the changes though: For example, deleting the first movie would also mean changing the ordernumbers of all other movies, doesn't it? – wannabeartist Jun 13 '12 at 06:10
  • Would it then make sense to re-create the list upon updates, rather than update individual rows? For example, user x takes his list, makes changes and hits "save" --> 1. previous entries get deleted with one go 2. new entries get inserted with one go – wannabeartist Jun 13 '12 at 06:40
4

In addition to what others have said, reordering existing favorites can be done in a single UPDATE statement, as explained here.

The linked answer explains reordering of two items, but can be easily generalized to any number of items.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167