database - stripcomments - liquibase sql transaction

¿La mejor representación de una lista ordenada en una base de datos?

Question

Sé que esto va en contra de los principios de una base de datos relacional, pero permítame describir la situación.

Tengo una página donde el usuario colocará una serie de elementos.

 ________________
| -Item1         |
| -Item2         |
| -Item3         |
| -Item4         |
|________________|

Estos artículos deben permanecer en el orden en que el usuario los entrega. Sin embargo, este orden puede ser cambiado un número arbitrario de veces por el usuario.

 ________________
| -Item1         |
| -Item4         |
| -Item2         |
| -Item3         |
|________________|

Enfoque 1

Mi idea original era dar a los elementos un índice para representar su lugar en la lista

Page           Item
-----------    ---------------
FK | pid       FK | pid 
   | name      PK | iid 
                  | index
                  | content 

Con esta solución, puede seleccionar elementos where pid = Page.pid y order by index cual es conveniente. Sin embargo, cada vez que cambie el orden, tendrá que cambiar de un elemento a otro (el mejor de los casos) y todos los demás (el peor de los casos).

Enfoque 2

También consideré hacer una "lista vinculada" como la estructura de datos donde cada elemento apunta al siguiente elemento de la lista.

Page           Item
-----------    ---------------
FK | pid       FK | pid 
   | name      PK | iid 
                  | next
                  | content 

Esto potencialmente hace que el cambio de orden sea menos costoso, pero tendríamos que confiar en la programación de front-end para extraer el pedido.

¿Hay un enfoque que no he pensado? Por favor hagamelo saber.

Answer 1

Creo que @ a1ex07 está en el camino correcto aquí (+1). No creo que las brechas en itemOrder violen 3NF, pero sí me preocupa una violación diferente de 3NF (más sobre esto más adelante). También tenemos que estar atentos a los datos incorrectos en el campo del itemOrder del itemOrder . Así es como empezaría:

create table pages (
  pid int,
  primary key (pid)
);

create table users (
  uid int,
  primary key (uid)
);

create table items (
  iid int,
  primary key (iid)
);

create table details (
  pid int not null references pages(pid),
  uid int not null references users(uid),
  iid int not null references items(iid), 
  itemOrder int,
  primary key (pid, uid, iid),
  unique (pid, uid, itemOrder)
);

La clave principal garantiza que para cada página, para cada usuario, haya elementos únicos. La restricción única garantiza que para cada página, para cada usuario, haya pedidos de elementos únicos. Esta es mi preocupación acerca de 3NF: en este escenario, itemOrder no depende completamente de la clave principal; Depende solo de las partes (pid, uid) . Eso ni siquiera es 2NF; y eso es un problema Podríamos incluir itemOrder en la clave principal, pero luego me preocupa que no sea mínimo, como deben ser las PK. Podríamos necesitar descomponer esto en más tablas. Sigue pensando . . .

[EDITAR - Más pensando en el tema. . . ]

Suposiciones

1. Hay usuarios.

2. Hay paginas

3. Hay articulos

4. (página, usuario) identifica un SET de elementos.

5. (página, usuario) identifica una LISTA ordenada de ranuras en las que podemos almacenar artículos si lo deseamos.

6. No deseamos tener elementos duplicados en una lista de (página, usuario).

Plan A

Mata la tabla de details , arriba.

Agregue una tabla, ItemsByPageAndUser , para representar el CONJUNTO de elementos identificados por (página, usuario).

create table ItemsByPageAndUser (
   pid int not null references pages(pid),
   uid int not null references users(uid),
   iid int not null references items(iid),
  primary key (pid, uid, iid)   
)

Agregue la tabla, SlotsByPageAndUser , para representar la LISTA ordenada de ranuras que pueden contener elementos.

create table SlotsByPageAndUser (
   pid       int not null references pages(pid),
   uid       int not null references users(uid),
   slotNum   int not null,
   iidInSlot int          references items(iid),
 primary key (pid, uid, slotNum),   
 foreign key (pid, uid, iid) references ItemsByPageAndUser(pid, uid, iid),
 unique (pid, uid, iid)
)

Nota 1 : iidInSlot es anulable por lo que podemos tener espacios vacíos si queremos. Pero si hay un elemento presente, debe compararse con la tabla de elementos.

Nota 2 : Necesitamos el último FK para asegurarnos de no agregar ningún elemento que no esté en el conjunto de elementos posibles para esto (usuario, página).

Nota 3 : La restricción única en (pid, uid, iid) impone nuestro objetivo de diseño de tener elementos únicos en la lista (supuesto 6). Sin esto, podríamos agregar tantos elementos del conjunto identificado por (página, usuario) como queramos siempre y cuando se encuentren en ranuras diferentes.

Ahora hemos desacoplado muy bien los elementos de sus ranuras al tiempo que conservamos su dependencia común en (página, usuario).

Este diseño está ciertamente en 3NF y podría estar en BCNF, aunque me preocupa SlotsByPageAndUser en ese sentido.

El problema es que debido a la restricción única en la tabla SlotsByPageAndUser la cardinalidad de la relación entre SlotsByPageAndUser y ItemsByPageAndUser es de uno a uno. En general, las relaciones 1-1 que no son subtipos de entidad son incorrectas. Hay excepciones, por supuesto, y tal vez esta es una. Pero tal vez hay una manera aún mejor. . .

Plan B

1. Mata a la tabla SlotsByPageAndUser .

2. Agregue una columna ItemsByPageAndUser a ItemsByPageAndUser .

3. Agregue una restricción única en (pid, uid, iid) a ItemsByPageAndUser .

Ahora es:

create table ItemsByPageAndUser (
   pid     int not null references pages(pid),
   uid     int not null references users(uid),
   iid     int not null references items(iid),
   slotNum int,
 primary key (pid, uid, iid),   
 unique (pid, uid, slotNum)
)

Nota 4 : Al dejar slotNum conserva nuestra capacidad de especificar elementos en el conjunto que no están en la lista. Pero . . .

Nota 5 : Poner una restricción única en una expresión que involucre una columna anulable puede causar resultados "interesantes" en algunas bases de datos. Creo que funcionará como lo pretendemos en Postgres. (Consulte esta discusión aquí en SO). Para otras bases de datos, su millaje puede variar.

Ahora no hay una relación desordenada 1-1, así que eso es mejor. Sigue siendo 3NF, ya que el único atributo no clave ( slotNum ) depende de la clave, de toda la clave y de nada más que de la clave. (No puede preguntar sobre slotNum sin decirme de qué página, usuario y elemento está hablando).

No es BCNF porque [ (pid, uid, iid) -> slotNum ] y [ (pid,uid,slotNum) -> iid ]. Pero es por eso que tenemos la restricción única en (pid, uid, slotNum) que evita que los datos entren en un estado inconsistente.

Creo que esta es una solución viable.

Answer 2

Puede agregar una nueva columna de caracteres (nvarchar) a la tabla de Page llamada order que contiene una lista delimitada de iid en el orden que prefiera, es decir, 1,4,3,2 . La ventaja es solo un campo en una tabla para mantener: la desventaja obvia sería la necesidad de escribir una (s) función (es) de utilidad para convertir entre los caracteres y los tipos numéricos que en realidad probablemente no tomen mucho tiempo.

Answer 3

Si espera que la cantidad de elementos no sea enorme, puede usar una versión modificada en bits de su primer enfoque. Basta con hacer una brecha entre los índices consecutivos. Por ejemplo, el primer elemento tiene el índice 100, el segundo 200, etc. De esta manera, no tiene que actualizar todos los índices cada vez, solo si no puede encontrar un hueco

Answer 4

Solución: haga que el index una cadena (porque las cadenas, en esencia, tienen infinita "precisión arbitraria"). O si usa un index incremento de int en 100 en lugar de 1.

El problema de rendimiento es el siguiente: no hay valores "intermedios" entre dos elementos ordenados.

item      index
-----------------
gizmo     1
              <<------ Oh no! no room between 1 and 2.
                       This requires incrementing _every_ item after it
gadget    2
gear      3
toolkit   4
box       5

En su lugar, haz esto (mejor solución a continuación):

item      index
-----------------
gizmo     100
              <<------ Sweet :). I can re-order 99 (!) items here
                       without having to change anything else
gadget    200
gear      300
toolkit   400
box       500

Aún mejor: aquí es cómo Jira resuelve este problema. Su "rango" (lo que usted llama índice) es un valor de cadena que permite una tonelada de espacio de respiración entre los elementos clasificados.

Aquí hay un ejemplo real de una base de datos jira con la que trabajo

   id    | jira_rank
---------+------------
 AP-2405 | 0|hzztxk:
 ES-213  | 0|hzztxs:
 AP-2660 | 0|hzztzc:
 AP-2688 | 0|hzztzk:
 AP-2643 | 0|hzztzs:
 AP-2208 | 0|hzztzw:
 AP-2700 | 0|hzztzy:
 AP-2702 | 0|hzztzz:
 AP-2411 | 0|hzztzz:i
 AP-2440 | 0|hzztzz:r

Note este ejemplo hzztzz:i . La ventaja de un rango de cadena es que te quedas sin espacio entre dos elementos, aún no tienes que volver a clasificar nada más. Simplemente comienza a agregar más caracteres a la cadena para reducir el enfoque.

Answer 5

Use el Enfoque 1 y viva con las implicaciones de rendimiento de las actualizaciones de índice. A menos que esté tratando con millones de elementos por página, es poco probable que encuentre falta de rendimiento y retenga todo el poder de SQL al tratar con conjuntos de datos.

Además de ser mucho más difícil trabajar con el SQL puro no de procedimiento, el Enfoque 2 aún requerirá que recorra la lista para encontrar el lugar adecuado para volver a conectar los "enlaces" cuando reordena el elemento.