CODEWITHSUNDEEP
mysqldatabase-design
wannabeartist
wannabeartist

Reputation: 2833

Best way to store ordered lists in a database?

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?

Upvotes: 30

Views: 19178

Answers (4)

Ryan Fernandes
Ryan Fernandes

Reputation: 8526

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.

Upvotes: 7

Kerbocat
Kerbocat

Reputation: 388

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 = ?;

Upvotes: 9

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

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.

Upvotes: 4

Marco
Marco

Reputation: 57573

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.

Upvotes: 4

Related Questions