Existe uma prática recomendada para saber se uma chave estrangeira entre tabelas deve ser vinculada a uma chave natural ou a uma chave substituta? A única discussão que realmente encontrei (a menos que meu google-fu esteja faltando) é a resposta de Jack Douglas a esta pergunta , e seu raciocínio parece sólido para mim. Estou ciente da discussão além de que as regras mudam, mas isso seria algo que precisaria ser considerado em qualquer situação.
O principal motivo para perguntar é que tenho um aplicativo legado que faz uso de FKs com chaves naturais, mas há um forte impulso dos desenvolvedores para mudar para um OR/M (NHibernate em nosso caso), e um fork já produziu alguns quebrando as alterações, então estou procurando colocá-los de volta nos trilhos usando a chave natural ou mover o aplicativo herdado para usar chaves substitutas para o FK. Meu instinto diz para restaurar o FK original, mas sinceramente não tenho certeza se esse é realmente o caminho certo a seguir.
A maioria de nossas tabelas já possui uma chave substituta e uma chave natural já definidas (por meio de restrição exclusiva e PK), portanto, adicionar colunas extras não é um problema para nós neste caso. Estamos usando o SQL Server 2008, mas espero que seja genérico o suficiente para qualquer banco de dados.
Nem o SQL nem o modelo relacional são perturbados por chaves estrangeiras que fazem referência a uma chave natural. Na verdade, fazer referência a chaves naturais geralmente melhora drasticamente o desempenho. Você ficaria surpreso com a frequência com que as informações necessárias estão completamente contidas em uma chave natural; referenciar essa chave troca uma junção por uma tabela mais ampla (e, consequentemente, reduz o número de linhas que você pode armazenar em uma página).
Por definição, a informação que você precisa está sempre completamente contida na chave natural de cada tabela "lookup". (O termo tabela de consulta é informal. No modelo relacional, todas as tabelas são apenas tabelas. Uma tabela de códigos postais dos EUA pode ter linhas como esta: {AK, Alaska}, {AL, Alabama}, {AZ, Arizona} , etc. A maioria das pessoas chamaria isso de tabela de pesquisa.)
Em sistemas grandes, não é incomum encontrar tabelas com mais de uma chave candidata. Também não é incomum que tabelas que atendem a uma parte da empresa façam referência a uma chave candidata e tabelas que atendam a outra parte da empresa façam referência a uma chave candidata diferente. Esse é um dos pontos fortes do modelo relacional e é uma parte do modelo relacional que o SQL suporta muito bem.
Você encontrará dois problemas ao fazer referência a chaves naturais em tabelas que também possuem uma chave substituta.
Primeiro, você vai surpreender as pessoas. Embora eu geralmente faça forte pressão pelo Princípio da Menor Surpresa , esta é uma situação em que não me importo de surpreender as pessoas. Quando o problema é que os desenvolvedores se surpreendem com o uso lógico das chaves estrangeiras, a solução é a educação, não o redesenho.
Em segundo lugar, os ORMs geralmente não são projetados em torno do modelo relacional e, às vezes, incorporam suposições que não refletem as melhores práticas. (Na verdade, muitas vezes eles parecem ser projetados sem nunca receber a entrada de um profissional de banco de dados.) Exigir um número de ID em cada tabela é uma dessas suposições. Outra é assumir que o aplicativo ORM "possui" o banco de dados. (Portanto, é gratuito criar, descartar e renomear tabelas e colunas.)
Trabalhei em um sistema de banco de dados que forneceu dados para centenas de programas aplicativos escritos em pelo menos duas dúzias de idiomas por um período de 30 anos. Esse banco de dados pertence à empresa, não a um ORM.
Uma bifurcação que introduz alterações importantes deve ser um impedimento.
Eu medi o desempenho com chaves naturais e chaves substitutas em uma empresa em que trabalhei. Há um ponto de inflexão em que as chaves substitutas começam a superar as chaves naturais. (Assumindo nenhum esforço adicional para manter alto o desempenho da chave natural, como particionamento, índices parciais, índices baseados em função, espaços de tabela extras, uso de discos de estado sólido etc.) Pelas minhas estimativas para essa empresa, eles atingirão esse ponto de inflexão em por volta de 2045. Nesse ínterim, eles obtêm melhor desempenho com chaves naturais.
Outras respostas relevantes: Em Database Schema Confusing
A principal razão pela qual eu apoio as chaves substitutas é que as chaves naturais geralmente estão sujeitas a alterações e isso significa que todas as tabelas relacionadas devem ser atualizadas, o que pode sobrecarregar o servidor.
Além disso, nos 30 anos em que tenho usado uma variedade de bancos de dados em muitos tópicos, a verdadeira chave natural costuma ser bastante rara. As coisas são supostamente únicas (SSN) não são, as coisas que são únicas em um determinado momento podem se tornar não únicas mais tarde e algumas coisas como endereços de e-mail e números de telefone podem ser únicas, mas podem ser reutilizadas para pessoas diferentes posteriormente encontro. É claro que algumas coisas simplesmente não têm um bom identificador exclusivo, como nomes de pessoas e empresas.
Quanto a evitar junções usando uma chave natural. Sim, isso pode acelerar as instruções select que não precisam das junções, mas fará com que os locais onde você ainda precisa das junções sejam mais lentos, pois as junções int geralmente são mais rápidas. Ele provavelmente também diminuirá a velocidade de inserções e exclusões e causará problemas de desempenho nas atualizações quando a chave for alterada. Consultas complexas (que são mais lentas de qualquer maneira) serão ainda mais lentas. Portanto, consultas simples são mais rápidas, mas relatórios e consultas complexas e muitas ações no banco de dados podem ser mais lentas. É um ato de equilíbrio, que pode pender para um lado ou para o outro, dependendo de como seu banco de dados é consultado.
Portanto, não há uma resposta única para todos. Depende do seu banco de dados e como ele será consultado e que tipo de informação está armazenado nele. Pode ser necessário fazer alguns testes para descobrir o que funciona melhor em seu próprio ambiente.
Se você não sabe a resposta, vá com o substituto. Aqui está o porquê - se suposições são feitas sobre regras de negócios, e essas suposições são falsas ou as regras mudam, seus dados são lixo. Aqui está um exemplo:
Pessoa, Papel, PersonRole
a regra de negócios atual afirma que uma Pessoa tem um Papel. Você cria uma tabela que vincula Person e Role onde PersonRole (PersonName, PersonBirthDate, PersonMotherMaidenName, ..., RoleCode)
Agora você é um verdadeiro purista quando se trata de Natural Keys! Mas, falando sério, e se a organização decidir que uma pessoa agora pode assumir várias funções? Quais são os efeitos a jusante de apoiar a mudança nas necessidades de negócios?