Лучший способ хранения упорядоченных списков в базе данных?

Как лучше всего хранить "упорядоченные списки" в базе данных, чтобы их можно было легко обновлять (добавлять, удалять и изменять порядок записей)?

Рассмотрим базу данных, в которой у вас есть таблица для пользователей и фильмов. У каждого пользователя есть список любимых фильмов.

Поскольку многим пользователям может понравиться один и тот же фильм, я заставил пользователей и фильмы разделить таблицы и использовать третью таблицу для их подключения, usermovies.

usermovies содержит идентификатор пользователя и фильма и "номер заказа". Номер заказа используется для заказа списка фильмов для пользователей.

Например, пользователь Josh может иметь следующий список:

Prometheus
Мужчины в черном 3
Диктатор

и пользователь Jack может иметь список вроде:

Диктатор
Prometheus
Броненосец
Белоснежка и охотник

Итак, у них есть несколько избранных, но необязательно в том же порядке.

Я могу получить список идентификаторов фильмов для каждого пользователя с помощью запроса:

SELECT movie_id FROM usermovies WHERE user_id =? ORDER BY order_number

Затем, с упорядоченными movie_ids, я могу получить список фильмов, используя другой запрос

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

Итак, запросы работают, но обновление списков кажется действительно сложным сейчас - есть ли лучшие способы хранения этой информации, чтобы было легко получить список фильмов для пользователя x, добавить фильмы, удалить их и изменить порядок список?

Ответ 1

Таблица соединений/ссылок с дополнительными столбцами для атрибутов связи между видеороликами и пользователями является стандартным способом реализации ассоциации многих-многих с классом ассоциации, поэтому все, что вы сделали, кажется правильным.

Что касается удобства вставки/обновления/удаления, вам придется управлять всей ассоциацией (все строки для FK файлов пользователя) каждый раз, когда вы выполняете вставку/обновление/удаление. Вероятно, это не волшебный/более простой способ сделать это.

Сказав это, вам также нужно будет выполнить эти операции в транзакции и, что еще важнее, иметь столбец "версия" в этой таблице соединений, если ваше приложение поддерживает многопользовательский режим.

Ответ 2

В дополнение к тому, что говорили другие, переупорядочение существующих избранных может быть сделано в одном заявлении UPDATE, как описано здесь.

Связанный ответ объясняет переупорядочение двух элементов, но может быть легко обобщен на любое количество элементов.

Ответ 3

Для извлечения избранных вами фильмов вы можете использовать один запрос:

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

Чтобы добавить/удалить любимый фильм, просто добавьте/удалите связанную запись в таблице usermovies.
Чтобы изменить порядок фильма, просто измените все order_number в таблице user_movies, связанной с пользователем.

Ответ 4

Если вы не ищете "подходящее решение для перемещения вверх/вниз", а затем по умолчанию добавляете в нижней части списка, вот еще несколько указателей:

Вставка новых строк в определенную позицию может быть выполнена следующим образом: (вставка в позицию 3)

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

И вы можете удалить аналогичным образом: (удаление позиции 6)

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