soporta - optimizar conexiones mysql

¿La mejor manera de almacenar listas ordenadas en una base de datos?

Question

¿Cuál es la mejor manera de almacenar "listas ordenadas" en una base de datos, de modo que actualizarlas (agregar, eliminar y cambiar el orden de las entradas) se realice fácilmente?

Considere una base de datos donde tenga una tabla para usuarios y películas. Cada usuario tiene una lista de películas favoritas.

Como a muchos usuarios les puede gustar la misma película, he hecho que los usuarios y las películas separen las tablas y utiliza una tercera tabla para conectarlas, usermovies.

Usermovies contiene un ID de usuario y una película y un "número de pedido". El número de pedido se utiliza para ordenar la lista de películas para los usuarios.

Por ejemplo, el usuario Josh podría tener la siguiente lista:

1. Prometeo
2. Hombres de negro 3
3. El dictador

y el usuario Jack podría tener una lista como:

1. El dictador
2. Prometeo
3. Acorazado
4. Blancanieves y el cazador

Entonces, comparten algunos favoritos, pero no necesariamente en el mismo orden.

Puedo obtener la lista de ID de películas para cada usuario mediante una consulta:

SELECT movie_id FROM usermovies WHERE user_id =? ORDER BY order_number

Luego, con los Movie_ids ordenados, puedo obtener la lista de películas usando otra consulta

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

Así que las consultas funcionan, pero la actualización de las listas parece realmente compleja ahora. ¿Hay mejores maneras de almacenar esta información para que sea fácil obtener la lista de películas para el usuario x, agregar películas, eliminarlas y cambiar el orden de la lista?

Answer 1

Además de lo que otros han dicho, la reordenación de los favoritos existentes se puede hacer en una sola declaración de ACTUALIZACIÓN, como se explica here .

La respuesta vinculada explica el reordenamiento de dos elementos, pero puede generalizarse fácilmente a cualquier número de elementos.

Answer 2

Para recuperar las películas favoritas de los usuarios, puede utilizar una sola consulta:

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

Para agregar / eliminar una película favorita, simplemente agregue / elimine el registro relacionado en la tabla de usermovies .
Para alterar el orden de una película, simplemente cambie todo el campo user_movies en la tabla user_movies relacionada con el usuario.

Answer 3

Si no está buscando una solución de tipo "subir / bajar" y luego agregar de manera predeterminada al final de la lista, aquí hay algunos consejos más:

Insertar nuevas filas en una posición específica se puede hacer así: (insertando en la posición 3)

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

Y puede eliminar de una manera similar: (eliminando la posición 6)

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

Answer 4

Una tabla de unión / enlace con columnas adicionales para los atributos de la asociación entre películas y usuarios es la forma estándar de realizar una asociación de muchos con una clase de asociación, por lo que lo que ha hecho parece correcto.

En cuanto a la facilidad de inserción / actualización / eliminación, deberá administrar la asociación completa (todas las filas para los FK de películas de usuario) cada vez que realice una inserción / actualización / eliminación. Probablemente no haya una forma mágica / simple de hacer esto.

Dicho esto, también deberá ejecutar estas operaciones en una transacción y, lo que es más importante, tener una columna de "versión" en esta tabla de conexiones si su aplicación es compatible con múltiples usuarios.