Digamos que temos várias tabelas primárias em nosso banco de dados (a, bec) e depois outra (x) que armazena um objeto JSONb complexo e semiprevisível que possui referências a todas as tabelas primárias. No meu caso, o JSONb é mais ou menos assim:
{
entries: [
[
[
{table: 'a', id: '1'},
{table: 'b', id: '4', entries: [
{table: 'a', id: '3'},
{table: 'a', id: '1'}
...
]},
{table: 'c', id: '5', entries: [
{table: 'a', id: '2'},
{table: 'b', id: '4', entries: [
{table: 'a', id: '1'},
{table: 'a', id: '6'},
...
]},
...
]},
...
],
...
],
...
]
}
Ao selecionar registros da tabela xqueremos filtrar os resultados pelas propriedades das outras tabelas - por exemplo. apenas xregistros que contêm um aregistro que possui um campo contendo um valor enum específico.
É possível ou tem algum desempenho fazer isso em uma única consulta usando essa estrutura de dados JSONb? Parece que seria necessária uma agregação séria de IDs, e fazer isso para cada consulta parece muito trabalhoso.
A alternativa que estava considerando é manter o campo JSONb como está, mas também criar tabelas de junção (posso estar com o nome errado aí) para rastrear todas as xdependências de registros da tabela. Então você teria tabelas x_a, x_b, x_c, etc. e armazenaria apenas um único registro de junção exclusivo para cada ID a, b, ou c, que aparece no xregistro. Dessa forma, ao escrever as consultas, uma abordagem simples e não JSON poderia ser usada para fazer um filtro de junção regular.
Como um programador SQL iniciante a intermediário, isso parece que pelo menos levaria a um código mais legível, no entanto, não tenho certeza se isso conta como uma violação da regra "inserir os dados apenas uma vez".
Toda e qualquer contribuição é bem-vinda, incluindo material de leitura sobre como tomar essas decisões.
A linguagem é PostgresQl
Sim, este é o caminho. Seu campo JSONb está desnormalizado e terá um desempenho ruim se você tentar unir tabelas a partir dele. Provavelmente não é possível fazer isso de maneira sargável e será necessário desempacotar o JSON e verificar a tabela inteira sempre que você ingressar nela.
As tabelas de "junção" às quais você se referiu são chamadas de tabelas de ponte ou tabelas de ligação. Eles provavelmente também ajudarão a desduplicar seus dados do campo JSON. Eles serão indexáveis em seus campos-chave e deverão ter bom desempenho quando associados. JSON é, na verdade, uma maneira muito ruim de armazenar quaisquer dados que precisem ser operados, em geral. É realmente uma prática recomendada usá-lo apenas para dados que são apenas gravados e lidos, não operados, quando esses dados não puderem ser facilmente normalizados de outra forma.
Você não deseja manter os mesmos dados em dois lugares, quando possível, corrija. Mas por que continuar usando a coluna JSON? O consumidor não deveria se preocupar com o objeto que contém os dados. E se isso acontecer, você pode simplesmente escrever uma consulta que crie um conjunto de resultados estruturados JSON fora das tabelas. Você pode até salvar essa consulta em um objeto de visualização para poder referenciá-la facilmente conforme necessário. Isso permite que você não precise manter os dados em dois locais, o que poderia levar a problemas de integridade e gerenciamento de dados.