Вопрос по mysql, database-design – Лучший способ хранить упорядоченные списки в базе данных?

18

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

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

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

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

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

Prometheus Men in Black 3 The Dictator

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

The Dictator Prometheus Battleship Snow White and the Huntsman

Таким образом, они разделяют некоторые избранные, но не обязательно в том же порядке.

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

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, добавить фильмы, удалить их и изменить порядок списка?

Спасибо! Это пригодится, хотя моя настоящая проблема здесь - обновление. wannabeartist
Чтобы получить все фильмы для пользователя, вы можете использовать один запрос:SELECT name FROM movies INNER JOIN usermovie ON usermovie.movie_id = movies.id AND usermovie.user_id = ? ORDER BY usermovie.order_number, И я не вижу, что сложно с нынешним подходом? Чтобы добавить фильм пользователю, вы просто вставляете новую запись в таблицу ссылок, а для ее удаления вы удаляете запись. Cyclonecode

Ваш Ответ

4   ответа
5

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

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

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

По факту,there is такой "волшебный" способ сделать задачу с изменением только одной строки вместо всех строк:stackoverflow.com/a/3399334/2947812
@MikhailBatcer решение, которое требует перераспределения всей таблицы после (порядка) 44 переупорядочений, явно меньше, чем магическое.
4

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

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

5

двигайтесь вверх / вниз & quot; своего рода решение, а затем по умолчанию добавление в нижней части списка, вот еще несколько указателей:

Вставка новых строк в определенную позицию может быть выполнена следующим образом: (вставка в позиции 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 = ?;
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 таблица, связанная с пользователем.

Будет ли тогда иметь смысл заново создавать список после обновлений, а не обновлять отдельные строки? Например, пользователь x берет свой список, вносит изменения и нажимает кнопку "сохранить". - & GT; 1. предыдущие записи удаляются одним движением 2. новые записи вставляются одним движением wannabeartist
Спасибо, я все еще не уверен, как обрабатывать изменения: например, удаление первого фильма также будет означать изменение порядковых номеров всех других фильмов, не так ли? wannabeartist
@wannabeartist: да, конечно

Похожие вопросы