when tipo generar from failed dato converting convert conversion sql database normalization lookup

generar - tipo de dato uniqueidentifier sql server



Decisión entre almacenar la id de la tabla de búsqueda o datos puros (8)

Encuentro que esto surge mucho, y no estoy seguro de la mejor manera de abordarlo.

La pregunta que tengo es cómo tomar la decisión entre utilizar claves externas para buscar tablas o usar valores de tabla de búsqueda directamente en las tablas que lo solicitan, evitando por completo la relación de la tabla de búsqueda.

Puntos a tener en cuenta:

  • Con el segundo método, necesitaría hacer actualizaciones masivas a todos los registros que hacen referencia a los datos si se cambian en la tabla de búsqueda.

  • Esto se enfoca más hacia las tablas que tienen una gran cantidad de columnas que hacen referencia a muchas tablas de búsqueda. Por lo tanto, muchas claves externas significan muchas combinaciones cada vez que consulta la tabla.

  • Esta información vendría de listas desplegables que se extraerían de las tablas de búsqueda. Para hacer coincidir los datos al recargar, los valores deben estar en la lista existente ( relacionada con el primer punto ).

¿Hay una mejor práctica aquí, o algún punto clave a considerar?

Dado que nadie más ha abordado su segundo punto: cuando las consultas se vuelven largas y difíciles de leer y escribir debido a todas esas uniones, una vista generalmente resolverá eso.

En casos de valores atómicos simples, tiendo a estar en desacuerdo con la sabiduría común en este caso, principalmente en el frente de la complejidad. Considera una mesa que contenga sombreros. Puedes hacer la forma "desnormalizada":

CREATE TABLE Hat ( hat_id INT NOT NULL PRIMARY KEY, brand VARCHAR(255) NOT NULL, size INT NOT NULL, color VARCHAR(30) NOT NULL /* color is a string, like "Red", "Blue" */ )

O puede normalizarlo más haciendo una tabla de "colores":

CREATE TABLE Color ( color_id INT NOT NULL PRIMARY KEY, color_name VARCHAR(30) NOT NULL ) CREATE TABLE Hat ( hat_id INT NOT NULL PRIMARY KEY, brand VARCHAR(255) NOT NULL, size INT NOT NULL, color_id INT NOT NULL REFERENCES Color(color_id) )

El resultado final de este último es que ha agregado cierta complejidad, en lugar de:

SELECT * FROM Hat

Ahora debes decir:

SELECT * FROM Hat H INNER JOIN Color C ON H.color_id = C.color_id

¿Es eso extra unirse un gran trato? No, de hecho, esa es la base del modelo de diseño relacional: la normalización le permite evitar posibles inconsistencias en los datos. Pero cada situación como esta agrega un poco de complejidad, y a menos que haya una buena razón, vale la pena preguntar por qué lo estás haciendo. Considero posibles "buenas razones" para incluir:

  • ¿Hay otros atributos que "cuelguen" de este atributo? ¿Estás capturando, por ejemplo, tanto el "nombre del color" como el "valor hexadecimal", de manera que el valor hexadecimal siempre depende del nombre del color? Si es así, definitivamente quieres una tabla de colores separada, para evitar situaciones donde una fila tiene ("Rojo", "# FF0000") y otra tiene ("Rojo", "# FF3333"). Múltiples atributos correlacionados son la señal # 1 de que una entidad debe ser normalizada.
  • ¿El conjunto de valores posibles cambiará con frecuencia? El uso de una tabla de búsqueda normalizada facilitará los cambios futuros en los elementos del conjunto, ya que solo está actualizando una sola fila. Sin embargo, si no es frecuente, no se acobarde con las afirmaciones que tienen que actualizar muchas filas en la tabla principal; las bases de datos son bastante buenas en eso. Haz algunas pruebas de velocidad si no estás seguro.
  • ¿El conjunto de valores posibles será administrado directamente por los usuarios? Es decir, ¿hay una pantalla donde puedan agregar / eliminar / reordenar los elementos en la lista? Si es así, una mesa separada es obligatoria, obviamente.
  • ¿La lista de valores distintos potenciará algún elemento de UI? Por ejemplo, ¿es "color" una lista desplegable en la interfaz de usuario? Entonces será mejor que lo tenga en su propia mesa, en lugar de hacer SELECT DISTINCT sobre la mesa cada vez que necesite mostrar la lista desplegable.

Si ninguno de ellos se aplica, sería difícil encontrar otra razón (buena) para normalizar. Si solo quiere asegurarse de que el valor sea uno de un cierto conjunto (pequeño) de valores legales, es mejor que use una RESTRICCIÓN que diga que el valor debe estar en una lista específica; mantiene las cosas simples, y siempre puede "actualizar" a una mesa separada más adelante si surge la necesidad.

Incluso puede establecer una regla para programar siempre en contra de las vistas, teniendo la vista obtener las búsquedas.

Esto permite optimizar la vista y hacer que su código sea resistente a los cambios en las tablas.

En Oracle, incluso podría convertir la vista en una vista materializada si alguna vez lo necesita.

La normalización se considera de forma bastante universal como parte de las mejores prácticas en las bases de datos, y la normalización dice que sí, que extrae los datos y se refiere a ellos por medio de la clave.

Regla de oro: normalice la base de datos, el punto de referencia y la desnormalización solo si es necesario para lograr un buen rendimiento. Nunca desnormalizar sin evaluación comparativa.

Si tenía que desnormalizar, siempre preservar la integridad de los datos usando restricciones y factores desencadenantes.

Una cosa que nadie ha considerado es que no se uniría a la tabla de búsqueda si los datos que contiene pueden cambiar con el tiempo y los registros a los que se une son históricos. El ejemplo es una tabla de partes y una tabla de órdenes. Los vendedores pueden soltar piezas o cambiar los números de parte, pero la tabla de pedidos debe tener exactamente lo que se ordenó en el momento en que se ordenó. Por lo tanto, debe buscar los datos para hacer el registro insertado, pero nunca debe unirse a la tabla de búsqueda para obtener información sobre un pedido existente. En cambio, el número de parte y la descripción y el precio, etc. deben almacenarse en la tabla de pedidos. Esto es especialmente crítico para que los cambios de precios no se propaguen a través de datos históricos y para que sus registros financieros sean inexactos. En este caso, también querrás evitar el uso de cualquier clase de actualización en cascada.

Puede usar una tabla de búsqueda con una clave primaria VARCHAR, y su tabla de datos principal usa una LLAVE EXTRAÑA en su columna, con actualizaciones en cascada.

CREATE TABLE ColorLookup ( color VARCHAR(20) PRIMARY KEY ); CREATE TABLE ItemsWithColors ( ...other columns..., color VARCHAR(20), FOREIGN KEY (color) REFERENCES ColorLookup(color) ON UPDATE CASCADE ON DELETE SET NULL );

Esta solución tiene las siguientes ventajas:

  • Puede consultar los nombres de los colores en la tabla de datos principal sin necesidad de unirse a la tabla de búsqueda.
  • Sin embargo, los nombres de los colores están limitados al conjunto de colores en la tabla de búsqueda.
  • Puede obtener una lista de nombres de colores únicos (incluso si ninguno está actualmente en uso en los datos principales) consultando la tabla de búsqueda.
  • Si cambia un color en la tabla de búsqueda, el cambio se transferirá automáticamente a todas las filas de referencia en la tabla de datos principal.

Me sorprende que tantas otras personas en este hilo parezcan tener ideas equivocadas sobre lo que es la "normalización". ¡Usar claves sustitutas (el "id" omnipresente) no tiene nada que ver con la normalización!

Comentario de @MacGruber:

Sí, el tamaño es un factor. En InnoDB, por ejemplo, cada índice secundario almacena el valor de la clave primaria de la (s) fila (s) donde ocurre un valor de índice dado. Por lo tanto, cuantos más índices secundarios tenga, mayor será la sobrecarga para usar un tipo de datos "voluminoso" para la clave principal.

También esto afecta las claves externas; la columna de clave externa debe ser del mismo tipo de datos que la clave principal a la que hace referencia. Es posible que tenga una pequeña tabla de búsqueda, por lo que cree que el tamaño de la clave principal en una tabla de 50 filas no importa. ¡Pero esa tabla de búsqueda podría estar referenciada por millones o miles de millones de filas en otras tablas!

No hay una respuesta correcta para todos los casos. Cualquier respuesta puede ser correcta para diferentes casos. Simplemente aprende sobre las compensaciones y trata de tomar una decisión informada caso por caso.