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