Relaciones many to many ordenadas
Crear relaciones many to many ordenadas no es fácil por la complejidad de mantenimiento en la base de datos.
Relaciones many to many
A continuación recojo algunas reflexiones para crear relaciones fácilmente. Para gestionar el guardado de datos podemos tomar un ejemplo basado un Kanban. Pueden existir diferentes aproximaciones que explico más abajo.
Crear Relaciones ordenadas
Opción 1 – Guardado en tablas diferentes y con el orden correspondiente
- Primera tabla: listado de columnas.
- Segunda tabla: listado de de tareas.
- Tercera tabla: relación de columnas y tareas y con un campo para el orden de cada columna.
Pro: Fácil de entender y conocer la estructura de la base de datos para hacer consultas. Mediante esta estrategia no habrá tareas huérfanas en los casos de borrados y siempre tendrán relaciones puras de claves externas.
Contras: en el caso de añadir, mover o borrar una tarea de una columna se debe recalcular el orden dentro de la columna.
- Si es una tarea nueva puede que sea más sencillo: bastaría saber cual es el último orden de de dicha columna y añadir la nueva tarea incrementando dicho valor.
- Al quitar una tarea de la columna, habría que reordenar todos los afectados (siempre y cuando no sea la última). Existen opciones en las bases de datos para hacer dicha actualización del campo de una forma un tanto automático.
- En el caso de mover una tarea a una nueva columna habría que hacer en la columna «anterior» el mismo proceso que en el punto anterior de quitarlo de una columna. En la nueva columna, se deberán recalcular los órdenes que correspondan.
Consideraciones: En la tabla relacional se podría definir con Primary-Key las columnas denominadas column_id y order, de tal forma que siempre será un elemento único en dicha tabla. Para llevar a cabo esta opción se podrían usar algunas funciones en la base de datos para ejecutar modificaciones del orden. Con Supabase existe la opción de definir funciones Postgress (RPC: Remote Procedure Calls) que ejecutarán arreglos en el orden:
Opción 2 – Guardado del orden en un array relativo a la columna
- Tabla de columnas. Tendrán un campo que guardará en un array el orden de las tareas por su ID.
- Tabla de tareas.
Pro: Se reduce la necesidad de tabla relacional de tareas y columnas. Cuando se añade una tarea nueva bastará con añadirlo al array de «orden» de la columna asociada (1 campo). En el caso de borrar una tarea es fácil tener un campo con Array de las tareas asociadas y actualizarlo. Si se mueve una tarea entre columnas habría que actualizar el campo de dos columnas: la vieja y la nueva.
Contras: Cuando se borra una tarea fuera del proceso habitual pueden existir problemas de que el campo de arrays con las tareas quede con tareas huérfanas. Es decir: que contenga el ID de tareas que ya no existan. Por lo tanto es importante que el flujo sea muy limpio a la hora de borrar tareas. No existe a día de hoy una opción de crear un array basado en claves externas de otra tabla. En PostgreSQL parece que puede haber algo en desarrollo desde hace años pero sin clara liberación al mercado.
Consideraciones: Quizás sea la forma más rápida y que puede dar menos dolores de cabeza durante el desarrollo. El principal problema es que no mantiene una congruencia de los datos almacenados por tener IDs que no tienen una relación sólida en la base de datos.
Conclusiones
A priori con las soluciones actuales, no creo que una opción sea mejor que la otra académicamente hablando. Quizás dependiendo de la implementación se debería optar por una solución u otra. En cualquiera de los casos hay algunas lógicas adicionales que hay que desarrollar para evitar los problemas mencionados o al menos para traducirlos en el mostrado de los datos en el propio frontend de la aplicación.
Como referencia, revisando la documentación de la API de Notion para las propiedades de la Database, se puede ver como devuelve un array que incluye los IDs de los elementos. A priori, parece que correspondería con la Opción 2 comentada anteriormente. Es probable que la respuesta esté modifica para que aparezca así, pero esté almacenada como la Opción 1 mediante tablas relacionales.