¿Por qué las bases de datos relacionales no admiten la devolución de información en un formato anidado?

46

Supongamos que estoy creando un blog en el que quiero publicar publicaciones y comentarios. Así que creo dos tablas, una tabla de 'publicaciones' con una columna 'id' de entero autoincremento y una tabla de 'comentarios' que tiene una clave externa 'post_id'.

Luego quiero ejecutar lo que probablemente será mi consulta más común, que es recuperar una publicación y todos sus comentarios. Al ser bastante nuevo en las bases de datos relacionales, el enfoque que me parece más obvio es escribir una consulta que se parezca a algo como:

SELECT id, content, (SELECT * FROM comments WHERE post_id = 7) AS comments
FROM posts
WHERE id = 7

Lo que me daría el ID y el contenido de la publicación que quiero, junto con todas las filas de comentarios relevantes empaquetadas cuidadosamente en una matriz (una representación anidada como la que usarías en JSON). Por supuesto, SQL y las bases de datos relacionales no funcionan de esta manera, y lo más cerca que pueden llegar es hacer una unión entre 'publicaciones' y 'comentarios' que devolverá mucha duplicación innecesaria de datos (con la misma información de publicación repetida en cada fila), lo que significa que el tiempo de procesamiento se gasta tanto en la base de datos para ponerlo todo junto como en mi ORM para analizar y deshacer todo.

Incluso si le pido a mi ORM que cargue con entusiasmo los comentarios de la publicación, lo mejor que haré es enviar una consulta para la publicación, y luego una segunda consulta para recuperar todos los comentarios, y luego unirlos al cliente lado, que también es ineficiente.

Entiendo que las bases de datos relacionales son tecnología probada (demonios, son más antiguas que yo) y que se han realizado muchas investigaciones a lo largo de las décadas, y estoy seguro de que hay una muy buena razón por la que (y el estándar SQL) están diseñados para funcionar como lo hacen, pero no estoy seguro de por qué el enfoque que describí anteriormente no es posible. Me parece que es la forma más simple y obvia de implementar una de las relaciones más básicas entre los registros. ¿Por qué las bases de datos relacionales no ofrecen algo como esto?

(Descargo de responsabilidad: principalmente escribo aplicaciones web utilizando almacenes de datos Rails y NoSQL, pero recientemente he estado probando Postgres, y en realidad me gusta mucho. No pretendo atacar bases de datos relacionales, solo estoy perplejo. )

No estoy preguntando cómo optimizar una aplicación de Rails, o cómo solucionar mi problema en una base de datos en particular. Me pregunto por qué el estándar SQL funciona de esta manera cuando me parece contradictorio y me desperdicia. Debe haber alguna razón histórica por la cual los diseñadores originales de SQL quisieran que sus resultados se vieran así.

    
pregunta PreciousBodilyFluids 06.07.2011 - 23:05

12 respuestas

42

C. J. La fecha se detalla en esto en el Capítulo 7 y el Apéndice B de SQL y Teoría relacional . Tienes razón, no hay nada en la teoría relacional que prohíba que el tipo de datos de un atributo sea una relación en sí misma, siempre que sea el tipo de relación igual en cada fila. Tu ejemplo calificaría.

Pero Date dice que las estructuras como esta están "generalmente contraindicadas, pero no invariablemente" (es decir, una mala idea) porque las jerarquías de relaciones son asimétricas . Por ejemplo, una transformación de estructura anidada a una estructura "plana" familiar no siempre se puede revertir para recrear el anidamiento.

Las consultas, restricciones y actualizaciones son más complejas, más difíciles de escribir y más difíciles de admitir para el RDBMS si se permiten atributos con valores de relación (RVA).

También confunde los principios de diseño de la base de datos, porque la jerarquía de relaciones best no es tan clara. ¿Deberíamos diseñar una relación de proveedores con un RVA anidado para piezas suministradas por un proveedor determinado? ¿O una relación de Partes con un RVA anidado para proveedores que suministran una Parte dada? ¿O almacenar ambos, para facilitar la ejecución de diferentes tipos de consultas?

Este es el mismo dilema que resulta de la base de datos jerárquica y modelos de bases de datos orientadas a documentos . Eventualmente, la complejidad y el costo de acceder a las estructuras de datos anidadas impulsa a los diseñadores a almacenar datos de forma redundante para facilitar la búsqueda por parte de diferentes consultas. El modelo relacional desalienta la redundancia, por lo que los RVA pueden trabajar en contra de los objetivos del modelado relacional.

Por lo que entiendo (no los he usado), Rel y Dataphor son proyectos RDBMS que admiten atributos con valores de relación.

Re comentario de @dportas:

Los tipos estructurados son parte de SQL-99, y Oracle los admite. Pero no almacenan varias tuplas en la tabla anidada por fila de la tabla base. El ejemplo común es un atributo de "dirección" que parece ser una sola columna de la tabla base, pero tiene otras sub-columnas para calle, ciudad, código postal, etc.

Tablas anidadas también son compatibles con Oracle, y estos sí permiten múltiples tuplas por fila de la tabla base. Pero no soy consciente de que esto es parte de SQL estándar. Y tenga en cuenta la conclusión de un blog: "Nunca usaré una tabla anidada en una declaración CREAR TABLA. ¡Pasa todo su tiempo DESESCRIBIENDO en ellos para que sean útiles de nuevo!"

    
respondido por el Bill Karwin 07.07.2011 - 00:45
15

Algunos de los primeros sistemas de base de datos se basaron en el modelo de base de datos jerárquica . Esto representó los datos en una estructura similar a un árbol con padres e hijos, como usted sugiere aquí. HDMS fueron reemplazados en gran medida por bases de datos construidas sobre el modelo relacional. Las razones principales de esto fueron que RDBMS podía modelar relaciones "de muchos a muchos" que eran difíciles para las bases de datos jerárquicas y que RDBMS podía realizar fácilmente consultas que no formaban parte del diseño original, mientras que HDBMS lo obligó a realizar consultas a través de rutas especificadas en el momento del diseño.

Todavía hay algunos ejemplos de sistemas de bases de datos jerárquicas en la naturaleza, en particular el registro de Windows y LDAP.

La amplia cobertura de este tema está disponible en el siguiente artículo

    
respondido por el Steve Weet 07.07.2011 - 00:05
10

Supongo que su pregunta realmente se centra en el hecho de que, si bien las bases de datos se basan en una lógica sólida y en una base terapéutica, hacen un muy buen trabajo al almacenar, manipular y recuperar datos en conjuntos (bidimensionales) al tiempo que garantizan la referencia. integridad, concurrencia y muchas otras cosas, no proporcionan una característica (adicional) de enviar (y recibir) datos en lo que podríamos llamar formato orientado a objetos o formato jerárquico.

Luego afirma que "incluso si le pido a mi ORM que cargue con entusiasmo los comentarios de la publicación, lo mejor que haré es enviar una consulta para la publicación y luego una segunda consulta para recuperar todos los comentarios. y luego júntelos en el lado del cliente, lo que también es ineficaz ".

No veo nada ineficaz en el envío de 2 consultas y la recepción de 2 lotes de resultados con:

--- Query-1-posts
SELECT id, content 
FROM posts
WHERE id = 7


--- Query-2-comments
SELECT * 
FROM comments 
WHERE post_id = 7

Yo diría que es (casi) la forma más eficiente (casi, ya que realmente no necesitas el posts.id y no todas las columnas de comments.* )

Como Todd señaló en su comentario, no debe pedir a la base de datos que devuelva datos listos para su visualización. Es el trabajo de la aplicación hacer eso. Puede escribir (una o unas pocas) consultas para obtener los resultados que necesita para cada operación de visualización, de modo que no haya una duplicación innecesaria en los datos enviados a través del cable (o el bus de memoria) desde la base de datos a la aplicación.

Realmente no puedo hablar de ORM, pero quizás algunos de ellos pueden hacer parte de este trabajo por nosotros.

Se pueden utilizar técnicas similares en la entrega de datos entre un servidor web y un cliente. Se utilizan otras técnicas (como el almacenamiento en caché) para que la base de datos (o la web u otro servidor) no esté sobrecargada con solicitudes duplicadas.

Mi opinión es que los estándares, como SQL, son mejores si se mantienen especializados en un área y no tratan de cubrir todas las áreas de un campo.

Por otra parte, el comité que establece el estándar de SQL bien puede pensar lo contrario en el futuro y proporcionar una estandarización para esa característica adicional. Pero no es algo que pueda diseñarse en una noche.

    
respondido por el yper-crazyhat-cubeᵀᴹ 07.07.2011 - 00:53
5

No puedo responder con una respuesta adecuada y argumentada, así que siéntete libre de decirme si me equivoco (pero, por favor, corrígeme para que podamos aprender algo nuevo). Creo que la razón es que las bases de datos relacionales se centran en el modelo relacional, que a su vez se basa en algo que no conozco llamado "lógica de primer orden". Lo que puede pedir probablemente no se ajuste conceptualmente en el marco matemático / lógico. Las bases de datos relacionales se basan en ellas. Además, lo que pides generalmente se resuelve fácilmente mediante bases de datos de gráficos, lo que da más pistas de que es la conceptualización subyacente de la base de datos la que entra en conflicto con lo que quiere lograr.

    
respondido por el Stefano Borini 06.07.2011 - 23:25
5

Sé que al menos SQLServer admite consultas anidadas cuando usa FOR XML.

SELECT id, content, (SELECT * FROM comments WHERE post_id = posts.id FOR XML PATH('comments'), TYPE) AS comments
FROM posts
WHERE id = 7
FOR XML PATH('posts')

El problema aquí no es la falta de soporte de RDBMS, sino la falta de soporte de tablas anidadas en tablas.

Además, ¿qué te impide usar una combinación interna?

SELECT id, content, comments.*
FROM posts inner join comments on comments.post_id = posts.id
WHERE id = 7

Puede ver la combinación interna como una tabla anidada, solo el contenido de los primeros 2 campos se puede repetir una vez. No me preocuparía mucho el rendimiento de la unión, la única parte lenta en una consulta como esta es la io de la base de datos al cliente. Esto solo será un problema cuando el contenido contenga una gran cantidad de datos. En ese caso sugeriría dos consultas, una con select id, content y otra con una unión interna y select posts.id, comments.* . Esto se puede escalar incluso con varias publicaciones, ya que solo usaría 2 consultas.

    
respondido por el Dorus 07.07.2011 - 11:23
5

En realidad, Oracle admite lo que usted desea, pero necesita envolver la subconsulta con la palabra clave "cursor". Los resultados se obtienen a través del cursor abierto. En Java, por ejemplo, los comentarios se mostrarían como conjuntos de resultados. Más sobre esto, consulte la documentación de Oracle en "Expresión CURSOR"

SELECT id, content, cursor(SELECT * FROM comments WHERE post_id = 7) AS comments
FROM posts
WHERE id = 7
    
respondido por el Dilshod Tadjibaev 10.07.2011 - 01:40
1

Algunos admiten el anidamiento (jerárquico).

Si quisiera una consulta, podría tener una tabla que se haga referencia a sí misma. Algunos RDMS soportan este concepto. Por ejemplo, con SQL Server se pueden usar expresiones de tabla comunes (CTE) para una consulta jerárquica.

En tu caso, las Publicaciones estarán en el Nivel 0 y luego todos los comentarios estarán en el Nivel 1.

Las otras opciones son 2 consultas o una inscripción con información adicional para cada registro devuelto (que otros hayan mencionado).

Ejemplo de jerárquico:

enlace

En el enlace anterior, EmpLevel muestra el nivel del anidamiento (o jerarquía).

    
respondido por el Jon Raynor 07.07.2011 - 02:18
0

Lo siento, no estoy seguro de entender exactamente tu problema.

En MSSQL solo puedes ejecutar 2 declaraciones SQL.

SELECT id, content
FROM posts
WHERE id = 7

SELECT * FROM comments WHERE post_id = 7

Y devolverá tus 2 conjuntos de resultados simultáneamente.

    
respondido por el Biff MaGriff 06.07.2011 - 23:33
0

RDBMs se basan en la teoría y se adhieren a la teoría. Esto permite una buena consistencia y una confiabilidad matemáticamente comprobada.

Debido a que el modelo es simple y de nuevo basado en la teoría, facilita la optimización y muchas implementaciones para las personas. Esto es diferente a NoSQL donde todos lo hacen un poco diferente.

En el pasado, se han realizado intentos para crear bases de datos jerárquicas, pero IIRC (parece que no se puede buscar en Google) ha habido problemas (los ciclos y la igualdad vienen a la mente).

    
respondido por el Adam Gent 06.07.2011 - 23:54
0

Tienes una necesidad específica. Se preferiría extraer datos de una base de datos en el formato que desee, para que pueda hacer lo que quiera con ellos.

Algunas bases de datos de cosas no funcionan tan bien, pero no es imposible construirlas para hacerlo de todos modos. Dejar la aplicación a otras aplicaciones es la recomendación actual, pero no justifica por qué no se puede hacer.

El único argumento que tengo en contra de su sugerencia es poder manejar este conjunto de resultados de forma "sql". Sería una mala idea crear un resultado en la base de datos y no poder trabajar con él ni manipularlo hasta cierto punto. Digamos que creé una vista creada de la forma que sugiere, ¿cómo la incluyo en otra declaración de selección? A las bases de datos les gusta tomar resultados y hacer cosas con ellos. ¿Cómo lo uniría a otra mesa? ¿Cómo compararía tu conjunto de resultados con otro?

Entonces el beneficio de RDMS es la flexibilidad de sql. La sintaxis para seleccionar datos de una tabla está bastante cerca de una lista de usuarios u otros objetos en el sistema (al menos ese es el objetivo). No estoy seguro de que haya algún punto para hacer algo completamente diferente. Ni siquiera los han llevado al punto de manejar códigos / cursores de procedimientos o BLOBOS de datos de manera muy eficiente.

    
respondido por el JeffO 26.09.2011 - 19:16
0

En mi opinión, se debe principalmente a SQL y la forma en que se realizan las consultas agregadas: las funciones agregadas y la agrupación se ejecutan en grandes conjuntos de filas bidimensionales para obtener resultados. Así ha sido desde el principio y es muy rápido (la mayoría de las soluciones NoSQL son bastante lentas con la agregación y dependen de esquemas desnormalizados en lugar de consultas complejas)

Por supuesto, PostgreSQL tiene algunas características de la base de datos orientada a objetos. De acuerdo con estos correos ( mensaje ) puede lograr lo que necesita creando un agregado personalizado.

Personalmente estoy usando marcos como Doctrine ORM (PHP) que hacen la agregación de la aplicación y admiten funciones como la carga lenta para aumentar el rendimiento.

    
respondido por el Daimon 06.07.2011 - 23:46
0

PostgreSQL admite una variedad de tipos de datos estructurados, incluyendo Arrays y JSON . Al usar SQL o uno de los lenguajes de procedimiento incrustados, puede crear valores con una estructura arbitrariamente compleja y devolverlos a su aplicación. También puede crear tablas con columnas de cualquiera de los tipos estructurados, aunque debe considerar detenidamente si está desnormalizando innecesariamente su diseño.

    
respondido por el Jonathan Rogers 13.09.2016 - 09:31

Lea otras preguntas en las etiquetas