unir una todos tabla sentencias misma los lista entre diferencias datos crear consultas como comandos sql mysql database database-design data-modeling

una - todos los comandos de sql



única tabla fija con múltiples columnas versus tablas abstractas flexibles (5)

Me preguntaba si tienes un sitio web con una docena de diferentes tipos de listados (Tiendas, Restaurantes, Clubes, Hoteles, Eventos) que requieren diferentes campos, ¿hay algún beneficio de crear una tabla con columnas definidas como tal?
Ejemplo de tienda:

shop_id | name | X | Y | city | district | area | metro | station | address | phone | email | website | opening_hours

O un enfoque más abstracto similar a esto:

object_id | name --------------- 1 | Messy Joe''s 2 | Bate''s Motel type_id | name --------------- 1 | hotel 2 | restaurant object_id | type_id --------------- 1 | 2 2 | 1 field_id | name | field_type --------------- 1 | address | text 2 | opening_hours | date 3 | speciality | text type_id | field_id --------------- 1 | 1 1 | 2 2 | 1 2 | 3 object_id | field_id | value 1 | 1 | 1st street.... 1 | 3 | English Cuisine

Por supuesto, puede ser más abstracto si los valores están predefinidos (Ejemplo: las especialidades pueden tener su propia lista)

Si tomo el enfoque abstracto, puede ser muy flexible, pero las consultas serán más complejas con muchas combinaciones. Pero no sé si esto afecta el rendimiento al ejecutar estas consultas ''más complejas''.

Me interesaría saber cuáles son las ventajas y desventajas de ambos métodos. Puedo imaginarme por mí mismo, pero no tengo la experiencia para confirmar esto.

¿Cómo tiene los listados representados en el código? Supongo que Listing como un supertipo, con Shop , Restuarant , etc. como subtipos?

Asumiendo eso, este es un caso de cómo asignar subtipos a una base de datos relacional. En general, hay tres opciones:

  • Opción 1: tabla única por subtipo, con atributos comunes repetidos en cada tabla (nombre, id, etc.).
  • Opción 2: tabla única para todos los objetos (su enfoque de tabla única)
  • Opción 3: tabla para el supertipo y una para cada subtipo

No hay una solución universalmente correcta. Mi preferencia generalmente es comenzar con la opción 3; proporciona una estructura intuitiva para trabajar, está bastante bien normalizada y puede ampliarse fácilmente. Significa una unión única para recuperar cada instancia, pero RDBMS está bien optimizado para hacer combinaciones, por lo que realmente no causa problemas de rendimiento en la práctica.

La opción 2 puede ser más eficaz para las consultas (sin uniones) pero causa problemas si otras tablas necesitan hacer referencia a todas las instancias de supertipo (proliferación de claves externas).

La opción 1 parece ser a primera vista la más eficiente, aunque con dos advertencias: (1) No es flexible para cambiar. Si agrega un nuevo subtipo (y, por lo tanto, atributos diferentes), deberá cambiar la estructura de la tabla y migrarla. (2) Puede ser menos eficiente de lo que parece. Debido a que la población de la tabla es escasa, algunos DB no la almacenan de manera particularmente eficiente. Como consecuencia, puede ser menos eficaz que la opción 1, ya que el motor de consultas puede hacer combinaciones más rápido de lo que puede buscar espacios de tablas escasos e inflados.

Cuál elegir realmente se reduce a conocer los detalles de su problema. Sugiero leer un poco sobre las opciones: este es un buen lugar para comenzar.

hth

Ciertos asuntos necesitan ser aclarados y resueltos antes de que podamos entrar en una discusión razonable.

Resolución de requisitos previos

  1. Etiquetas
    En una profesión que exige precisión, es importante que usemos etiquetas precisas para evitar confusiones y para que podamos comunicarnos sin tener que usar descripciones y calificadores largos.
    .
    Lo que ha publicado como FixedTables, no está normalizado . Justo lo suficiente, puede tratarse de un intento en la tercera forma normal, pero de hecho es un archivo plano, no normalizado (no "denormalizado"). Lo que has publicado como AbstractTables es, para ser precisos, Entity-Attribute-Value , que es casi , pero no del todo, sexta forma normal, y por lo tanto, está más normalizada que 3NF. Suponiendo que se hace correctamente, por supuesto.

    • El archivo plano Unnormalised no está "denormalizado". Está repleto de duplicaciones (no se ha hecho nada para eliminar grupos repetidos y columnas duplicadas o para resolver dependencias) y nulos, es un problema de rendimiento de muchas maneras y evita la concurrencia.

    • Para ser Denormlaised, primero debe ser Normalizado, y luego la Normalización retrocedió un poco por alguna buena razón. Como no está Normalizado en primer lugar, no se puede desnormalizar. Simplemente no está normalizado.

    • No se puede decir que se denormaliza "para el rendimiento", porque ser un cerdo de rendimiento es la antítesis del rendimiento. Bueno, necesitan una justificación para la falta de diseño formal], y "para el rendimiento" es eso. Incluso el escrutinio formal más pequeño expuso la tergiversación (pero muy pocas personas pueden proporcionarlo, por lo que permanece oculto, hasta que obtienen un extraño para abordar, lo adivinaste, el problema del rendimiento masivo).

    • Las estructuras normalizadas funcionan mucho mejor que las estructuras no normalizadas. Las estructuras más normalizadas (EAV / 6NF) funcionan mejor que las estructuras menos normalizadas (3NF / 5NF).

    • Estoy de acuerdo con el impulso de los Potros OMG, pero no sus etiquetas y definiciones

    • en lugar de decir " no hacer" desnormalizar "a menos que sea necesario" , estoy diciendo, "Normalizar fielmente, punto" y "si hay un problema de rendimiento, no se ha Normalizado correctamente" .
      .

  2. Wiki
    Las entradas de Normal Forms y Normalization son una broma completa. Específicamente, las definiciones son incorrectas; confunden las Formas Normales; no tienen idea del proceso de Normalización; y otorgan el mismo peso a NF absurdas o cuestionables que han sido desacreditadas hace mucho tiempo. El resultado es que Wiki agrega a un tema ya confuso y raramente entendido. Así que no pierdas tu tiempo.
    .
    Sin embargo, para progresar, sin que esa referencia represente un obstáculo, permítanme decir esto.

    • La definición de 3NF es estable y no ha cambiado.
    • Existe una gran confusión entre los NF entre 3NF y 5NF. La verdad es que esta es un área que progresó en los últimos 15 años; y muchas organizaciones, académicos y proveedores con sus productos con limitaciones, saltaron a crear un nuevo "Formulario normal" para validar sus ofertas. Todos sirven intereses comerciales y académicamente poco sólidos. 3NF en su estado original no templado pretendía y garantizaba ciertos atributos.
    • La suma total es, 5NF es hoy, lo que 3NF pretendía ser hace 15 años, y se puede saltear la broma comercial y los doce o más NF "especiales" (comerciales y pseudoacadémicos) intermedios, algunos de los cuales son identificado en Wiki, e incluso eso en términos confusos.
      .

  3. Como ha podido comprender e implementar el EAV en su publicación, no tendrá problemas para comprender lo siguiente. Por supuesto, un verdadero modelo relacional es un requisito previo, claves fuertes, etc. La quinta forma normal es, ya que estamos salteando el Cuarto:

    • Tercera forma normal
      • que en términos definitivos simples es, cada columna que no es clave en cada tabla tiene una relación 1 :: 1 con la clave principal de la tabla,
      • y a ninguna otra columna que no sea clave
    • Duplicación cero de datos (el resultado, si la normalización se progresa diligentemente, no se logra solo por inteligencia o experiencia, o trabajando hacia ella como un objetivo sin el proceso formal)
    • no hay Anomalías de actualización (cuando actualiza una columna en alguna parte, no tiene que actualizar la misma columna ubicada en otro lugar, la columna existe en un solo lugar).
      .

  4. La Sexta Forma Normal es, por supuesto, la Quinta Forma Normal, más:

    • Eliminación de datos faltantes (columnas). Esta es la única solución verdadera al Problema nulo (también llamado Manejo de valores perdidos), y el resultado es una base de datos sin nulos. (Se puede hacer a 5NF con estándares y sustitutos nulos, pero eso no es óptimo.) La forma en que interpreta y muestra los valores perdidos es otra historia.
      .
  5. EAV vs Sexta Forma Normal
    Todas las bases de datos que he escrito, excepto una, son 5NF puras. He trabajado con (administrado, reparado, mejorado) un par de bases de datos EAV, y he implementado una verdadera base de datos 6NF. EAV es una implementación flexible de 6NF, a menudo realizada por personas que no tienen una buena comprensión de la normalización y las NF, pero que pueden ver el valor y la necesidad de contar con la flexibilidad de EAV. Eres un ejemplo perfecto. La diferencia es esta: porque es flexible, y como los implementadores no tienen una referencia (6NF) para ser fieles, solo implementan lo que necesitan y lo escriben todo en código; eso termina siendo un modelo inconsistente.
    .
    Mientras que, una implementación pura de 6NF tiene un punto de referencia puramente académico, y por lo tanto, generalmente es más estricto y consistente. Por lo general, esto aparece en dos elementos visibles:
    • 6NF tiene un catálogo para contener metadatos, y todo está definido en metadatos, no en código. EAV no tiene uno, todo está en el código (los implementadores realizan un seguimiento de los objetos y atributos). Obviamente, un catálogo facilita la adición de columnas, navegación y permite que se formen utilidades.
    • 6NF cuando se entiende, proporciona la verdadera solución al problema nulo. Los implementadores de EAV, dado que están ausentes del contexto de 6NF, manejan datos faltantes en el código, inconsistentemente, o peor, permiten Nulos en la base de datos. Los implementadores de 6NF no permiten valores nulos y manejan los datos faltantes de manera consistente y elegante, sin requerir construcciones de código (para el manejo de nulos; aún así, debe codificar los datos faltantes, por supuesto).
      .
      P.ej. Para bases de datos 6NF con un catálogo, tengo un conjunto de procs que [re] generarán el SQL requerido para realizar todos los SELECT, y proporciono Vistas en 5NF para todos los usuarios, por lo que no necesitan saber o entender la estructura 6NF subyacente . Ellos son expulsados ​​del catálogo. Por lo tanto, los cambios son fáciles y automatizados. Los tipos de EAV lo hacen manualmente, debido a la ausencia del catálogo.

Ahora, podemos comenzar el

Discusión

"Por supuesto, puede ser más abstracto si los valores están predefinidos (Ejemplo: las especialidades pueden tener su propia lista)"

Por supuesto. Pero no te pongas demasiado "abstracto". Mantenga la coherencia e implemente dichas listas de la misma manera EAV (o 6NF) que otras listas.

"Si tomo el enfoque abstracto, puede ser muy flexible, pero las consultas serán más complejas con muchas combinaciones. Pero no sé si esto afecta el rendimiento al ejecutar estas consultas ''más complejas''".

  1. Las uniones son peatonales en bases de datos relacionales. El problema no es la base de datos, el problema es que SQL es engorroso cuando se manejan combinaciones, especialmente claves compuestas.
  2. Las bases de datos EAV y 6NF tienen más uniones, que igual de peatonas, ni más ni menos. Si tiene que codificar cada SELECCIÓN manualmente, claro, el engorroso se vuelve realmente engorroso.
  3. El problema completo puede eliminarse (a) yendo con 6NF sobre EAV y (b) implementando un catálogo, desde el cual puede (c) generar todo el SQL básico. Elimina toda una clase de errores también.
  4. Es un mito común que las uniones tienen un costo. Totalmente falso La unión se implementa en tiempo de compilación, no hay nada de sustancia para ''costar'' ciclos de CPU. El problema es el tamaño de las tablas que se unen, no el costo de la combinación entre esas mismas tablas. Unir dos tablas con millones de filas cada una, en una relación PK⇢FK correcta, cada una de las cuales tiene los índices apropiados (Único en el lado padre [FK]; Único en el lado Niño) es instantáneo; ; donde el índice secundario no es único, pero al menos la columna principal es válida, es más lenta; donde no existe un índice útil, por supuesto que es muy lento. Nada de eso tiene que ver con el costo de la unión. Donde se devuelven muchas filas, el cuello de botella será la red y el diseño del disco; no el procesamiento de unión
  5. Por lo tanto, puede ser tan "complejo" como desee, no hay costo, SQL puede manejarlo.

Me interesaría saber cuáles son las ventajas y desventajas de ambos métodos. Puedo imaginarme por mí mismo, pero no tengo la experiencia para confirmar esto.

  1. 5NF (o 3NF para aquellos que no han progresado) es el más fácil y el mejor, en términos de implementación, facilidad de uso (desarrolladores y usuarios), mantenimiento. El inconveniente es que, cada vez que agrega una columna, debe cambiar la estructura de la base de datos (tabla DDL). Eso está bien, en algunos casos, pero no en la mayoría de los casos, debido al control de cambios implementado, es bastante oneroso. En segundo lugar, debe cambiar el código existente (el código que maneja la nueva columna no cuenta, porque es un imperativo): donde se implementan buenos estándares, eso se minimiza; donde están ausentes, el alcance es impredecible.

  2. EAV (que es lo que ha publicado), permite agregar columnas sin cambios de DDL. Esa es la única razón por la que las personas lo eligen. (El código que maneja la nueva columna no cuenta, porque es un imperativo). Si se implementa bien, no afectará el código existente; si no, lo hará. Pero necesitas desarrolladores con capacidad EAV. Cuando EAV se implementa mal, es abominable, un desastre peor que 5NF hecho mal, pero no es peor que Unnormalised, que es lo que la mayoría de las bases de datos existen (tergiversadas como "Denormalizadas para el rendimiento"). por supuesto, es aún más importante (que en 5NF / 3NF) mantener un fuerte contexto de transacción, porque las columnas están mucho más distribuidas. Del mismo modo, es esencial conservar la Integridad Referencial Declarativa: los problemas que he visto se debieron en gran parte a que los desarrolladores eliminaron DRI porque se volvió "demasiado difícil de mantener", el resultado fue, como se puede imaginar, una madre de datos montón con filas y columnas duplicadas de 3NF / 5NF en todo el lugar. Y manejo incoherente de nulos.

  3. No hay diferencia en el rendimiento, suponiendo que el servidor ha sido razonablemente configurado para el propósito previsto. (De acuerdo, hay optimizaciones específicas que solo son posibles en 6NF, que no son posibles en otras NF, pero creo que están fuera del alcance de este hilo.) Y nuevamente, EAV hecho mal puede causar cuellos de botella innecesarios, no más que Desnormalizado.

  4. Por supuesto, si vas con EAV, estoy recomendando más formalidad; comprar el total de quid; ir con 6NF; implementar un catálogo; utilidades para producir SQL; Puntos de vista; manejar los datos faltantes consistentemente; eliminar nulos por completo. Esto reduce su vulnerabilidad a la calidad de sus desarrolladores; Pueden olvidarse de las emisiones esotéricas de EAV / 6NF, usar Vistas y concentrarse en la lógica de la aplicación.

Perdonen la larga publicación.

Cuando comienza a requerir una gran cantidad de entidades diferentes (o incluso antes ...), una solución nosql sería mucho más simple que cualquiera de las dos opciones. Simplemente almacene cada entidad / registro con los campos exactos que necesita.

{ "id": 1, "type":"Restaurant", "name":"Messy Joe", "address":"1 Main St.", "tags":["asian","fusion","casual"] }

El enfoque "abstracto" se conoce mejor como "Normalización", se parece a la 3ra Forma Normal (3NF).

El otro se llama "Desnormalizado", y puede ser una opción de rendimiento válida ... cuando ha encontrado problemas de velocidad utilizando el enfoque Normalizado, no antes.

En su pregunta, ha presentado al menos dos problemas importantes al mismo tiempo. Esos dos problemas son EAV y gen-spec.

Primero, hablemos de EAV. Su última tabla (object_id, field_id, value) es esencialmente un EAV. Hay una ventaja para EAV y una desventaja para EAV. Lo bueno es que la estructura es tan genérica que puede acomodar casi cualquier cuerpo de datos que describan casi cualquier tema. Eso significa que puede proceder al diseño e implementación sin análisis de datos y sin comprender el tema, y ​​no preocuparse por suposiciones erróneas. El inconveniente es que en el momento de la recuperación, debe hacer el análisis de datos que omitió antes de construir la base de datos, para poder encontrar consultas que signifiquen algo. Esto es mucho más serio que solo la eficiencia de recuperación. Pero también vas a tener terribles problemas con la eficiencia de recuperación. Solo hay dos maneras de aprender sobre este escollo: vivirlo o leer sobre él de aquellos que lo tienen. Recomiendo la lectura.

En segundo lugar, tienes un caso gen-spec. Su tabla (object_id, type_id) captura un patrón gen-spec (generalización-especialización), junto con las tablas relacionadas. Si tuviera que generalizar entre hoteles y restaurantes, podría llamarlo algo así como "alojamientos públicos" o "lugares". Pero no estoy seguro de entender tu caso, y puedes conducir por algo aún más general de lo que sugieren esos dos nombres. Después de todo, has incluido "eventos" en tu lista, y un evento no es un tipo de lugar en mi mente.

He referido a otras personas a lecturas sobre gen-espec y el modelo relacional en respuestas anteriores.
Cuando dos tablas son muy similares, ¿cuándo deberían combinarse?

Pero dudo en enviarlo en la misma dirección, porque no me queda claro que quiera elaborar un modelo relacional de los datos antes de construir su base de datos. Un modelo relacional de un cuerpo de datos y un modelo EAV de los mismos datos están casi totalmente en desacuerdo entre sí. Me parece que debes tomar esa decisión antes de explorar cómo expresar gen-espec en el modelo relacional de datos.