Вопрос по mysql, database-design – Лучший способ хранить упорядоченные списки в базе данных?
Каков наилучший способ хранения «упорядоченных списков»? в базе данных, так что их обновление (добавление, удаление и изменение порядка записей) легко сделать?
Рассмотрим базу данных, где у вас есть таблица для пользователей и фильмов. У каждого пользователя есть список любимых фильмов.
Поскольку многим пользователям может нравиться один и тот же фильм, я разделил таблицы пользователей и фильмы и использовал третью таблицу для их соединения - пользовательские фильмы.
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, добавить фильмы, удалить их и изменить порядок списка?
SELECT name FROM movies INNER JOIN usermovie ON usermovie.movie_id = movies.id AND usermovie.user_id = ? ORDER BY usermovie.order_number, И я не вижу, что сложно с нынешним подходом? Чтобы добавить фильм пользователю, вы просто вставляете новую запись в таблицу ссылок, а для ее удаления вы удаляете запись.
Cyclonecode
ассоциации между фильмами и пользователями является стандартным способом реализации ассоциации «многие-многие» с классом ассоциации, поэтому то, что вы сделали, кажется правильным.
Что касается простоты вставки / обновления / удаления, вам придется управлять всей ассоциацией (всеми строками для FK пользовательского фильма) каждый раз при выполнении вставки / обновления / удаления. Вероятно, нет волшебного / более простого способа сделать это.
Сказав это, вам также нужно будет выполнить эти операции в транзакции и, что более важно, иметь «версию». столбец в этой соединительной таблице, если ваше приложение поддерживает несколько пользователей.
что сказали другие, переупорядочение существующих избранных можно выполнить в одном выражении UPDATE, как объяснено.Вот.
Связанный ответ объясняет изменение порядка двух элементов, но может быть легко обобщен на любое количество элементов.
двигайтесь вверх / вниз & 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 = ?;
вы можете использовать один запрос:
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 таблица, связанная с пользователем.