Por que bancos de dados relacionais não suportam retornar informações em um formato aninhado?

C. J. Date entra em detalhes sobre isso no Capítulo 7 e no Apêndice B da SQL e Teoria Relacional . Você está certo, não há nada na teoria relacional que proíba o tipo de dados de um atributo de ser uma relação em si, contanto que seja o mesmo tipo de relação em cada linha. Seu exemplo se qualificaria.

Mas Date diz que estruturas como esta são "geralmente - mas não invariavelmente - contra-indicadas" (ou seja, uma Idéia Ruim) porque as hierarquias de relações são assimétricas . Por exemplo, uma transformação de estrutura aninhada para uma estrutura "plana" familiar nem sempre pode ser revertida para recriar o aninhamento.

Consultas, restrições e atualizações são mais complexas, mais difíceis de escrever e mais difíceis para o RDBMS suportar se você permitir atributos com valor de relação (RVA).

Isso também confunde os princípios de design do banco de dados, porque a hierarquia das relações melhor não é tão clara. Devemos projetar uma relação de Fornecedores com um RVA aninhado para peças fornecidas por um determinado Fornecedor? Ou uma relação de peças com um RVA aninhado para fornecedores que fornecem uma determinada peça? Ou armazene os dois para facilitar a execução de diferentes tipos de consultas?

Este é o mesmo dilema que resulta do banco de dados hierárquico e do modelos de banco de dados orientados a documentos . Eventualmente, a complexidade e o custo de acessar estruturas de dados aninhadas levam os designers a armazenarem dados de forma redundante para facilitar a pesquisa por diferentes consultas. O modelo relacional desencoraja a redundância, de modo que os RVAs podem trabalhar contra os objetivos da modelagem relacional.

Pelo que entendi (não os usei), Rel e Dataphor são projetos de RDBMS que suportam atributos com valor de relação.

Re comentário de @dportas:

Os tipos estruturados fazem parte do SQL-99, e o Oracle os suporta. Mas eles não armazenam múltiplas tuplas na tabela aninhada por linha da tabela base. O exemplo comum é um atributo "endereço" que parece ser uma única coluna da tabela base, mas tem outras sub-colunas para rua, cidade, código postal, etc.

tabelas aninhadas também são suportados pelo Oracle, e eles permitem várias tuplas por linha da tabela base. Mas eu não estou ciente de que isso faz parte do SQL padrão. E lembre-se da conclusão de um blog: "Nunca usarei uma tabela aninhada em uma instrução CREATE TABLE. Você gasta todo o seu tempo UN-NESTING para torná-los úteis novamente!"

Alguns dos primeiros sistemas de banco de dados foram baseados no modelo de banco de dados hierárquico . Isso representava dados em uma estrutura parecida com uma árvore com pais e filhos, como você está sugerindo aqui. Os HDMS foram amplamente substituídos por bancos de dados baseados no modelo relacional. As principais razões para isso eram que o RDBMS poderia modelar relacionamentos "muitos para muitos" que eram difíceis para bancos de dados hierárquicos e que o RDBMS poderia facilmente executar consultas que não faziam parte do design original, enquanto o HDBMS restringia a consulta por caminhos especificados em tempo de design.

Ainda existem alguns exemplos de sistemas de bancos de dados hierárquicos, particularmente o registro do Windows e o LDAP.

A cobertura abrangente deste assunto está disponível no seguinte artigo

Então você afirma que "mesmo se eu instruir meu ORM a carregar ansiosamente os comentários da postagem, o melhor que ele fará é enviar uma consulta para a postagem e, em seguida, uma segunda consulta para recuperar todos os comentários e, em seguida, colocá-los juntos no lado do cliente, que também é ineficiente ".

Não vejo nada de ineficiente em enviar duas consultas e receber dois lotes de resultados com:

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


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

Eu diria que é (quase) a maneira mais eficiente (quase, como você realmente não precisa do posts.id e nem todas as colunas do comments.* )

Como Todd apontou em seu comentário, você não deve pedir ao banco de dados para retornar dados prontos para exibição. É o trabalho do aplicativo fazer isso. Você pode escrever (uma ou algumas) consultas para obter os resultados necessários para cada operação de exibição, para que não haja duplicação desnecessária nos dados enviados pelo cabo (ou pelo barramento de memória) do banco de dados para o aplicativo.

Eu não posso falar sobre ORMs, mas talvez alguns deles possam fazer parte deste trabalho para nós.

Técnicas semelhantes podem ser usadas na entrega de dados entre um servidor da Web e um cliente. Outras técnicas (como o armazenamento em cache) são usadas para que o banco de dados (ou a web ou outro servidor) não seja sobrecarregado com solicitações duplicadas.

Meu palpite é que os padrões, como o SQL, são melhores se eles se especializarem em uma área e não tentarem cobrir todas as áreas de um campo.

Por outro lado, o comitê que define o padrão SQL pode pensar bem no futuro e fornecer padronização para esse recurso adicional. Mas não é algo que pode ser projetado em uma noite.

Eu não sou capaz de responder com uma resposta adequada e argumentada, então sinta-se à vontade para me enganar no esquecimento se estiver errado (mas, por favor, corrija-me para que possamos aprender algo novo). Eu acho que a razão é que os bancos de dados relacionais são centrados no modelo relacional, que por sua vez é baseado em algo que eu não sei nada sobre chamado de "lógica de primeira ordem". O que você pode perguntar provavelmente não se encaixa conceitualmente no banco de dados matemático / lógico que os bancos de dados relacionais são construídos. Além disso, o que você pergunta geralmente é resolvido facilmente por bancos de dados de gráficos, dando mais dicas de que é a conceitualização subjacente do banco de dados que está em conflito com o que você deseja alcançar.

Eu sei que pelo menos o SQLServer suporta consultas aninhadas quando você 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')

O problema aqui não é a falta de suporte do RDBMS, mas a falta de suporte de tabelas aninhadas em tabelas.

Além disso, o que impede você de usar uma junção interna?

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

Você pode realmente olhar para a junção interna como uma tabela aninhada, apenas o conteúdo dos dois primeiros campos é repetido um tempo possível. Eu não me preocuparia muito com o desempenho da junção, a única parte lenta em uma consulta como essa é o io do banco de dados para o cliente. Isso só será um problema quando o conteúdo contiver uma grande quantidade de dados. Nesse caso, eu sugeriria duas consultas, uma com select id, content e outra com uma associação interna e select posts.id, comments.* . Isso é escalável mesmo com vários posts, já que você ainda usa apenas 2 consultas.

Na verdade, o Oracle suporta o que você quer, mas você precisa envolver a subconsulta com a palavra-chave "cursor". Os resultados são buscados via cursor aberto. Em Java, por exemplo, os comentários apareceriam como conjuntos de resultados. Mais sobre isso, veja a documentação da Oracle em "CURSOR Expression"

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

Alguns suportam aninhamento (hierárquico).

Se você quisesse uma consulta, poderia ter uma tabela com referências próprias. Alguns RDMS suportam esse conceito. Por exemplo, com o SQL Server, pode-se usar Expressões de Tabelas Comuns (CTEs) para uma consulta hierárquica.

No seu caso, os Posts estariam no Nível 0 e todos os comentários estariam no Nível 1.

As outras opções são 2 consultas ou uma associação com algumas informações extras para cada registro retornado (que outros mencionaram).

Exemplo de hierárquica:

link

No link acima, EmpLevel mostra o nível do aninhamento (ou hierarquia).

Desculpe, não sei se entendi exatamente o seu problema.

No MSSQL, você pode simplesmente executar 2 instruções SQL.

SELECT id, content
FROM posts
WHERE id = 7

SELECT * FROM comments WHERE post_id = 7

E ele retornará seus 2 conjuntos de resultados simultaneamente.

Os RDBMs são baseados em teoria e se ater à teoria. Isso permite uma boa consistência e confiabilidade comprovada matematicamente.

Como o modelo é simples e, novamente, baseado na teoria, torna-se mais fácil para as pessoas fazerem otimização e muitas implementações. Isso é diferente do NoSQL, onde todo mundo faz isso um pouco diferente.

Houve tentativas no passado de criar bancos de dados hierárquicos, mas o IIRC (parece que o google não) tem havido problemas (ciclos e igualdade vêm à mente).

Você tem uma necessidade específica. Seria preferível extrair dados de um banco de dados no formato desejado, para que você possa fazer o que quiser.

Algumas bases de dados não funcionam tão bem, mas não é uma impossibilidade de construí-las de qualquer maneira. Deixar a formatação para outros aplicativos é a recomendação atual, mas não justifica por que isso não pode ser feito.

O único argumento que tenho contra a sua sugestão é ser capaz de lidar com esse resultado definido de maneira "sql". Seria uma má idéia criar um resultado no banco de dados que não seja capaz de trabalhar com ele ou manipulá-lo de alguma forma. Digamos que eu criei uma visão construída da maneira que você sugere, como incluí-la em outra instrução select? Bancos de dados gostam de obter resultados e fazer coisas com eles. Como eu iria me juntar a outra mesa? Como eu compararia seu conjunto de resultados a outro?

Em seguida, o benefício do RDMS é a flexibilidade do sql. A sintaxe para selecionar dados de uma tabela é bem próxima de uma lista de usuários ou outros objetos no sistema (pelo menos essa é a meta). Não tenho certeza se há algum ponto para fazer algo completamente diferente. Eles nem sequer os levaram ao ponto de manipular códigos / cursores procedurais ou BLOBS de dados de forma muito eficiente.

Na minha opinião, é principalmente por causa do SQL e da forma como as consultas agregadas são realizadas - as funções agregadas e o agrupamento são executados em conjuntos de linhas bidimensionais grandes para retornar resultados. É assim que tem sido desde o começo e é muito rápido (a maioria das soluções NoSQL são bastante lentas com agregação e dependem de um esquema desnormalizado em vez de consultas complexas)

É claro que o PostgreSQL possui alguns recursos do banco de dados orientado a objetos. De acordo com esses e-mails ( message ), você pode conseguir o que precisa criando um agregado personalizado.

O PostgreSQL suporta uma variedade de tipos de dados estruturados, incluindo Matrizes e JSON . Usando SQL ou uma das linguagens procedurais incorporadas, você pode construir valores com estruturas arbitrariamente complexas e retorná-los ao seu aplicativo. Você também pode criar tabelas com colunas de qualquer um dos tipos estruturados, embora você deva considerar cuidadosamente se está desnormalizando desnecessariamente seu design.