Desvantagens de usar uma chave estrangeira anulável em vez de criar uma tabela de interseção

Os dois modelos representam relacionamentos diferentes.

Usando uma tabela de junção , você está modelando um relacionamento muitos para muitos.

Ao usar uma chave estrangeira simples, você está modelando um relacionamento um para muitos.

A desvantagem de um estrangeiro anulável A chave é ser incapaz de modelar a relação de muitos para muitos, se é isso que você está tentando realizar.

Com base na sua edição da pergunta, você está efetivamente dividindo a tabela do aluno em duas tabelas com a mesma chave. Geralmente vejo isso em tabelas que têm muitos campos, então alguém os divide em dois para ser mais administrável (eu chamo de colocar batom em um porco).

Ao dividir a mesa do aluno, você está fazendo a segunda tabela é opcional porque um registro não precisa existir na segunda tabela. Que é muito semelhante a um campo que não precisa ser definido porque pode ser nulo.

Se você quiser um relacionamento um para muitos, é muito melhor usar uma única tabela e permitir o ID da escola ser nulo na tabela do aluno. Não há razão para evitar nulos em campos, mesmo para uma chave estrangeira. Isso significa que o relacionamento externo é opcional: desenvolvedores e DBAs entendem isso claramente, e o mecanismo de banco de dados subjacente certamente deve funcionar bem.

Se você está preocupado com junções, não se preocupe. Existem semânticas bem definidas sobre como as junções funcionam com campos nulos. Usando uma única tabela, você pode unir duas tabelas em vez de três.

Comentários

• Então, se eu estiver modelando um relacionamento um-para-muitos (com participação opcional no lado: 1), devo usar uma chave estrangeira apesar de poder ter NULL valores?
• @Tom sim, isso é exatamente como modelá-lo. Embora seja tecnicamente possível usar uma tabela de junção, o modelo de dados permite muitos para muitos, portanto, você precisará de gatilhos e lógica de banco de dados para evitar isso. Você fica melhor restringindo o relacionamento de uma forma que seja impossível adicionar dados incorretos.
• Eu editei minha pergunta.Eu apenas fiz student_id uma chave primária na tabela School_has_Student, que manteve o relacionamento como um para muitos. Quais são as desvantagens desse método em relação ao uso de chave estrangeira?
• @Tom editei minha resposta.

Você escreveu em um comentário acima:

o livro “Fundamentals of Database Systems” […] diz [.. .] que é recomendado usar uma tabela de interseção se houver muitos valores NULL na coluna da chave estrangeira (por exemplo: se 98% dos funcionários não gerenciam um departamento)

Quando há muitos valores NULL na coluna de chave estrangeira, seus programas terão que lidar com esta coluna quase vazia para cada registro que processam. A coluna provavelmente ocupará algum espaço em disco mesmo que em 98% de todos os casos esteja vazio, consultar o relacionamento significa consultar aquela coluna que lhe dá mais tráfego de rede, e se você estiver usando um ORM que gera classes a partir de suas tabelas, seus programas também precisarão de mais espaço no cliente lado do que o necessário. Usando um cruzamento Se a tabela de seleção evita isso, haverá apenas registros de ligação necessários onde a chave estrangeira equivalente não seria NULL de outra forma.

Oposto a isso, se você não tiver apenas alguns valores NULL, digamos 50% ou mais relações não são NULL, usar uma tabela de interseção dá a você o efeito oposto – mais espaço em disco, maior complexidade resultando em mais tráfego de rede etc.

Portanto, usar uma tabela de interseção é apenas uma forma de otimização, apenas sensata para um caso específico, e especialmente hoje em dia, onde o espaço em disco e a memória tornaram-se mais baratos, sendo necessários com muito menos frequência. Observe que “Fundamentals of Database Systems” foi originalmente escrito há mais de 20 anos (encontrei uma referência à segunda edição de 1994) e acho que essa recomendação já estava lá naquela época. Antes de 1994, a otimização do espaço era provavelmente muito mais importante do que hoje, já que o armazenamento em massa ainda era mais caro e os computadores e as redes eram muito mais lentos do que hoje.

Como uma nota lateral para um comentário exigente: o A afirmação acima é apenas uma tentativa de antecipar o que o autor de “Fundamentals of Database Systems” tinha em mente com sua recomendação. Acho que ele estava fazendo uma afirmação grosseira e geral, válida para a maioria dos sistemas. Em alguns bancos de dados, há outras otimizações possíveis, como “colunas esparsas”, que tornam o uso de uma tabela de interseção ainda mais obsoleto.

Portanto, não interprete mal essa recomendação. O livro não diz você preferir tabelas de interseção para {0,1}:n relacionamentos em geral, ou – como você escreveu – que esta é a “maneira correta”. Use otimizações como esta que tornarão seus programas mais complicados apenas quando você realmente precisa deles.

Comentários

• Você ‘ está supondo muito sobre a implementação do banco de dados, especialmente considerando que o OP não ‘ mencionou um específico. É ‘ mais do que provável que o banco de dados seja inteligente o suficiente para usar apenas uma pequena quantidade de espaço para colunas esparsas.
• @gardenhead: o que o faz acreditar que isso é ” mais do que provável “?
• O fato de que bancos de dados têm existem há décadas e são altamente otimizados, pois são um componente crítico da maioria das infraestruturas.
• @gardenhead: parece-me que você está fazendo suposições muito mais injustificadas do que eu. No entanto, veja minha edição.

O modelo conceitual será semelhante a este, que é muito pouco ortodoxo para dizer menos:

O modelo físico será semelhante a este, o que é confuso para dizer menos (as pessoas pensarão que é M: M, a menos que vejam de perto):

Minha sugestão:

Se você tiver muitas colunas (FK ou não), que não se aplicam à maioria dos alunos, separe as tabelas em tabelas de funções com rels 1: 1. Mas isso não é porque são FK, é porque as colunas não se aplicam à maioria das linhas.

Caso contrário, , FK anuláveis são uma parte normal de um banco de dados e as tabelas de junção são geralmente para rels M: M.

Os usos comuns de rels 1: 1 são para tabelas de funções com colunas que se aplicam apenas se a entidade for de um determinado tipo e extração de colunas BLOB para considerações de desempenho ou armazenamento. Avoding valores nulos em FKs não é um uso comum para isso.

Além de outras respostas, gostaria de salientar que um valor nulo para a chave estrangeira é ambíguo. Isso significa:

1) A escola do aluno (se houver) é desconhecida (este é o significado padrão de “nulo” – o valor é desconhecido)

2) É sabe se o aluno tem ou não uma escola, e eles não têm nenhuma

Se você usar o significado padrão de nulo, como você representaria “o aluno não tem escola” em seu modelo de chave estrangeira. Nesse caso, você provavelmente teria que criar uma entrada “sem escola”, com sua própria identificação na tabela da escola. (Não é o ideal)

Comentários

• O livro ” Fundamentos de sistemas de banco de dados ” menciona que existem 3 interpretações para NULL, pode significar: 1) Valor desconhecido. 2) Valor indisponível ou retido. 3) Atributo não aplicável (acho que esta interpretação significa que você pode especificar um NULL para uma chave estrangeira).
• Essa ‘ é uma lista útil, mas a semântica de nulo (ou qualquer valor realmente) é definível pelo usuário.pode significar o que quer que o designer diga, não se limitando a essa lista. A questão é como distinguir significados diferentes quando mais de um pode ser necessário (ou mesmo salvo acidentalmente)
• Então você está sugerindo que eu deveria criar uma tabela de interseção em vez de usar uma chave estrangeira anulável?
• @Tom Sim, acredito que seja melhor neste caso
• @BradThomas – para evitar a mesma ambigüidade ao usar uma mesa de interseção, você representaria o caso 2 (sabe-se que o aluno tem nenhuma escola) por um registro na tabela de interseção com um School_ID NULL?

As tabelas do banco de dados têm isso coisa legal chamada restrições. Portanto, é muito fácil fazer uma tabela de interseção que permite que apenas 1 de cada aluno apareça na tabela, mas muitas escolas nessa tabela. Dar-lhe efetivamente uma

Teoria é bom, mas no final você é vai modelar seu banco de dados após as perguntas que você está fazendo.

Se você quiser questionar frequentemente com a pergunta: “quais alunos estão na minha escola”, você realmente deseja consultar toda a tabela de alunos ou uma tabela de interseção fácil.

Em bancos de dados: otimize para as perguntas que você faz.

Lá é um caso de uso em que o uso de uma terceira tabela pode realmente fazer sentido. O exemplo pode parecer puramente hipotético, mas espero que ilustre bem meu ponto. Vamos supor que você adicione mais colunas à tabela students e, em algum ponto, decida impor exclusividade nos registros por meio de índice composto em várias colunas. É muito provável que você “Terei de incluir a coluna school_id também, e aqui as coisas começam a ficar complicadas. Devido à forma como o SQL foi projetado, inserir vários registros idênticos onde school_id é NULL será possível. Faz todo o sentido de uma perspectiva técnica, mas não é intuitivo e pode levar a resultados inesperados. Por outro lado, impor exclusividade no tabela de interseção é fácil.

Tive que modelar um relacionamento “opcional” recentemente, em que o requisito para uma restrição de exclusividade era devido a uma coluna de carimbo de data / hora. Deixar a chave estrangeira anulável na tabela de repente levou ao possibilidade de inserir registros com o mesmo carimbo de data / hora (vamos supor que seja um padrão, definido em registros que não foram auditados / aprovados ovado ainda) – e a única saída era remover a coluna anulável.

Então, como você pode ver, é “um caso bastante específico e, como outros observaram, na maioria das vezes você” estaria perfeitamente bem com todos os valores NULL. Realmente depende dos requisitos específicos do seu modelo.

Além das muitas boas sugestões já enviadas, pessoalmente eu “não sou fã de chaves estrangeiras, a menos que sejam realmente necessárias. Primeiro, há o relacionamento M: M que você está referenciando. Além disso, chamar uma chave estrangeira e, assim, puxar os dados da tabela para suas consultas, apresenta mais complexidade e dependendo de tamanho da tabela, desempenho mais lento. Como já foi dito, os campos FK anuláveis podem não ser suportados e podem criar problemas de integridade de dados.

Se você estiver definindo um estado em que a escola do aluno é desconhecida ou vazia, o valor NULL não irá diferenciar essas condições. (novamente, estamos de volta à integridade dos dados.) A sugestão da tabela de funções por Tulains é elegante e permite valores nulos de forma limpa.