Início / dba / Perguntas / 17808
Accepted
Sarawut Positwinyu
Asked: 2012-05-14 20:27:55 +0800 CST

O uso de várias chaves estrangeiras separadas por vírgulas é errado e, em caso afirmativo, por quê?

  • 772

Existem duas tabelas: Deale DealCategories. Um negócio pode ter muitas categorias de negócios.

Portanto, a maneira correta deve ser fazer uma tabela chamada DealCategoriescom a seguinte estrutura:

DealCategoryId (PK)
DealId (FK)
DealCategoryId (FK)

No entanto, nossa equipe terceirizada armazenou as várias categorias na Dealtabela desta forma:

DealId (PK)
DealCategory -- In here they store multiple deal ids separated by commas like this: 18,25,32.

Sinto que o que eles fizeram está errado, mas não sei como explicar claramente por que isso não está certo.

Como devo explicar a eles que isso está errado? Ou talvez seja eu quem esteja errado e isso seja aceitável?

database-design foreign-key

3 respostas

  1. Best Answer

    Sim, é uma ideia terrível.

    Ao invés de ir:

    SELECT Deal.Name, DealCategory.Name
FROM Deal
  INNER JOIN
     DealCategories ON Deal.DealID = DealCategories.DealID
  INNER JOIN
     DealCategory ON DealCategories.DealCategoryID = DealCategory.DealCategoryID
WHERE Deal.DealID = 1234

    Agora você tem que ir:

    SELECT Deal.ID, Deal.Name, DealCategories
FROM Deal
WHERE Deal.DealID = 1234

    Em seguida, você precisa fazer coisas no código do aplicativo para dividir essa lista de vírgulas em números individuais e, em seguida, consultar o banco de dados separadamente:

    SELECT DealCategory.Name
FROM DealCategory
WHERE DealCategory.DealCategoryID IN (<<that list from before>>)

    Esse antipadrão de design decorre de um mal-entendido completo da modelagem relacional (você não precisa ter medo de tabelas. As tabelas são suas amigas. Use-as) ou de uma crença bizarramente equivocada de que é mais rápido pegar uma lista separada por vírgulas e dividi-la no código do aplicativo do que adicionar uma tabela de links ( nunca é). A terceira opção é que eles não são confiantes/competentes o suficiente com SQL para configurar chaves estrangeiras, mas se for esse o caso, eles não devem ter nada a ver com o design de um modelo relacional.

    SQL Antipatterns (Karwin, 2010) dedica um capítulo inteiro a esse antipadrão (que ele chama de 'Jaywalking'), páginas 15-23. Além disso, o autor postou uma pergunta semelhante no SO . Os pontos-chave que ele observa (conforme aplicado a este exemplo) são:

    • Consultar todos os negócios em uma categoria específica é bastante complicado (a maneira mais fácil de resolver esse problema é uma expressão regular, mas uma expressão regular é um problema em si).
    • Você não pode impor integridade referencial sem relacionamentos de chave estrangeira. Se você excluir DealCategory nr. #26, você então, em seu código de aplicativo, deve passar por cada transação procurando por referências à categoria #26 e excluí-las. Isso é algo que deve ser tratado na camada de dados, e ter que lidar com isso em seu aplicativo é uma coisa muito ruim .
    • Consultas agregadas ( COUNT, SUMetc), novamente, variam de 'complicadas' a 'quase impossíveis'. Pergunte aos seus desenvolvedores como eles obteriam uma lista de todas as categorias com uma contagem do número de negócios nessa categoria. Com um design adequado, são quatro linhas de SQL.
    • As atualizações se tornam muito mais difíceis (ou seja, você tem um acordo em cinco categorias, mas deseja remover duas e adicionar outras três). São três linhas de SQL com um design adequado.
    • Eventualmente, você encontrará VARCHARlimitações de tamanho de lista. Embora se você tiver uma lista separada por vírgulas com mais de 4.000 caracteres, é provável que a análise desse monstro seja lenta como o inferno de qualquer maneira.
    • Puxar uma lista do banco de dados, dividi-la e voltar ao banco de dados para outra consulta é intrinsecamente mais lento do que uma consulta.

    TLDR: É um projeto fundamentalmente defeituoso, não escala bem, introduz complexidade adicional até mesmo nas consultas mais simples e, pronto para uso, reduz a velocidade do seu aplicativo.

    • 49

  2. No entanto, nossa equipe terceirizada armazenou as várias categorias na tabela de negócios desta forma:

    DealId (PK) DealCategory -- Aqui eles armazenam vários ids de negócios separados por vírgulas como este: 18,25,32.

    Na verdade, esse é um bom design se você precisar apenas consultar as categorias de um determinado negócio.

    Mas é terrível se você quiser saber todas as ofertas de uma determinada categoria.

    E também torna muito difícil e sujeito a erros fazer qualquer outra coisa - como atualizações, contagens, junções, etc.

    A desnormalização tem seu lugar, mas você deve ter em mente que ela otimiza para um tipo de consulta em detrimento de todas as outras que você pode fazer com os mesmos dados. Se você sabe que sempre estará consultando em um padrão, pode ser uma vantagem usar o design desnormalizado. Mas se houver alguma chance de você precisar de mais flexibilidade nos tipos de consultas, fique com um design normalizado.

    Como qualquer outra forma de otimização, você precisa saber quais consultas serão executadas antes de decidir se a desnormalização é justificada.

    • 4

  3. Vários valores em uma coluna são contra a 1ª forma normal.

    Também não há absolutamente nenhum ganho de velocidade, já que as tabelas devem ser vinculadas no banco de dados. Você deve primeiro ler e analisar uma string e, em seguida, selecionar todas as categorias para o "negócio".

    A implementação correta seria uma tabela de junção como "DealDealCategories", com DealId e DealCategoryId.

    Implementação de hierarquia ruim?

    Além disso, um FK em DealCategories para outro DealCategory parece uma implementação ruim de uma hierarquia/árvore de DealCategories. Trabalhar com árvores por meio de uma relação de ID pai (a chamada lista de adjacência) é uma dor!

    Verifique os conjuntos aninhados (bons de ler, mas difíceis de modificar) e tabelas de fechamento (melhor desempenho geral, mas possivelmente alto uso de memória - provavelmente não muito para suas DealCategories) ao implementar hierarquias!

    • 1

relate perguntas