Chaves substitutas vs. naturais / comerciais

Aqui vamos nós novamente, o velho argumento ainda surge …

Seria melhor termos uma chave de negócios como chave primária ou preferiríamos ter um id substituto (ou seja, uma identidade do SQL Server) com uma restrição exclusiva no campo de chave comercial?

Por favor, forneça exemplos ou provas para apoiar sua teoria.

    Ambos. Tome seu bolo e coma.

    Lembre-se de que não há nada especial sobre uma chave primária, exceto que ela é rotulada como tal. Não é nada mais que uma restrição NOT NULL UNIQUE, e uma tabela pode ter mais de uma.

    Se você usar uma chave substituta, ainda desejará uma chave de negócios para garantir a exclusividade de acordo com as regras de negócios.

    Apenas algumas razões para usar chaves substitutas:

    1. Estabilidade : Alterar uma chave por causa de uma necessidade comercial ou natural afetará negativamente as tabelas relacionadas. As chaves substitutas raramente, ou nunca, precisam ser alteradas porque não há nenhum significado relacionado ao valor.

    2. Convenção : permite que você tenha uma convenção de nomenclatura de coluna de chave primária padronizada em vez de ter que pensar sobre como unir tabelas com vários nomes para seus PKs.

    3. Velocidade : Dependendo do tipo e do valor da PK, uma chave substituta de um número inteiro pode ser menor, mais rápida para indexar e pesquisar.

    Parece que ninguém disse nada em apoio a chaves não-substitutas (hesito em dizer “natural”). Então aqui vai …

    Uma desvantagem das chaves substitutas é que elas são sem sentido (citadas como uma vantagem por alguns, mas …). Isso às vezes força você a juntar muito mais tabelas à sua consulta do que deveria ser realmente necessário. Comparar:

    select sum(t.hours) from timesheets t where t.dept_code = 'HR' and t.status = 'VALID' and t.project_code = 'MYPROJECT' and t.task = 'BUILD'; 

    contra:

     select sum(t.hours) from timesheets t join departents d on d.dept_id = t.dept_id join timesheet_statuses s on s.status_id = t.status_id join projects p on p.project_id = t.project_id join tasks k on k.task_id = t.task_id where d.dept_code = 'HR' and s.status = 'VALID' and p.project_code = 'MYPROJECT' and k.task_code = 'BUILD'; 

    A menos que alguém pense seriamente no seguinte uma boa ideia:

     select sum(t.hours) from timesheets t where t.dept_id = 34394 and t.status_id = 89 and t.project_id = 1253 and t.task_id = 77; 

    “Mas” alguém dirá, “o que acontece quando o código para MYPROJECT ou VALID ou HR muda?” Para o qual minha resposta seria: “por que você precisaria mudar isso?” Estas não são chaves “naturais” no sentido de que algum corpo externo vai legislar que, doravante, “VALID” deve ser recodificado como “GOOD”. Apenas uma pequena porcentagem de chaves “naturais” realmente se enquadra nessa categoria – SSN e CEP são os exemplos usuais. Eu definitivamente usaria uma chave numérica sem sentido para tabelas como Person, Address – mas não para tudo , que por alguma razão a maioria das pessoas aqui parece defender.

    Veja também: minha resposta para outra pergunta

    Chave substituta nunca terá um motivo para mudar. Eu não posso dizer o mesmo sobre as chaves naturais. Sobrenomes, e-mails, ISBN nubmers – todos podem mudar um dia.

    Chaves substitutas (normalmente números inteiros) têm o valor agregado de tornar suas relações de tabela mais rápidas e mais econômicas em armazenamento e velocidade de atualização (melhor ainda, foreign keys não precisam ser atualizadas ao usar chaves substitutas, em contraste com campos de chave de negócios, que mudam agora e depois).

    A chave primária de uma tabela deve ser usada para identificar exclusivamente a linha, principalmente para fins de associação. Pense em uma tabela de Pessoas: os nomes podem mudar e não são garantidos como exclusivos.

    Think Companies: você é uma empresa feliz da Merkin que faz negócios com outras empresas na Merkia. Você é esperto o bastante para não usar o nome da empresa como chave primária, então você usa o ID exclusivo da empresa do governo da Merkia em sua totalidade de 10 caracteres alfanuméricos. Então Merkia muda os IDs da empresa porque eles acharam que seria uma boa ideia. Tudo bem, você usa o recurso de atualizações em cascata do seu mecanismo de db, para uma mudança que não deve envolver você em primeiro lugar. Mais tarde, seu negócio se expande e agora você trabalha com uma empresa na Freedonia. O ID da empresa Freedonian tem até 16 caracteres. Você precisa aumentar a chave primária do id da empresa (também os campos de chave estrangeira em Pedidos, Edições, MoneyTransfers etc), adicionando um campo País na chave primária (também nas foreign keys). Ai! Guerra civil na Freedonia, está dividida em três países. O nome do país do seu associado deve ser alterado para o novo; atualizações em cascata para o resgate. BTW, qual é a sua chave primária? (País, CompanyID) ou (CompanyID, Country)? O último ajuda a unir, o primeiro evita outro índice (ou talvez muitos, se você quiser que seus Pedidos sejam agrupados por país também).

    Tudo isso não é uma prova, mas uma indicação de que uma chave substituta para identificar exclusivamente uma linha para todos os usos, incluindo operações de junit, é preferível a uma chave comercial.

    Eu odeio surrogate keys em geral. Eles só devem ser usados ​​quando não houver chave natural de qualidade disponível. É um tanto absurdo quando você pensa sobre isso, pensar que adicionar dados sem sentido à sua tabela pode melhorar as coisas.

    Aqui estão minhas razões:

    1. Ao usar chaves naturais, as tabelas são agrupadas da maneira mais frequente, tornando as consultas mais rápidas.

    2. Ao usar chaves substitutas, você deve adicionar índices exclusivos em colunas de chave lógica. Você ainda precisa evitar dados lógicos duplicados. Por exemplo, você não pode permitir duas Organizações com o mesmo nome em sua tabela Organização, embora o pk seja uma coluna de ID substituta.

    3. Quando chaves substitutas são usadas como a chave primária, é muito menos claro quais são as chaves primárias naturais. Ao desenvolver, você quer saber qual conjunto de colunas torna a tabela única.

    4. Em uma a muitas cadeias de relacionamento, os principais cadeias lógicas. Por exemplo, as organizações têm muitas contas e contas com muitas faturas. Portanto, a chave lógica de Organization é OrgName. A chave lógica das contas é OrgName, AccountID. A chave lógica da fatura é OrgName, AccountID, InvoiceNumber.

      Quando as chaves substitutas são usadas, as cadeias de chaves são truncadas por ter apenas uma chave estrangeira para o pai imediato. Por exemplo, a tabela Fatura não possui uma coluna OrgName. Só tem uma coluna para o AccountID. Se você quiser procurar faturas para uma determinada organização, precisará ingressar nas tabelas Organização, Conta e Fatura. Se você usar chaves lógicas, poderá consultar diretamente a tabela Organização.

    5. Armazenar valores-chave substitutos de tabelas de consulta faz com que as tabelas sejam preenchidas com números inteiros sem sentido. Para visualizar os dados, devem ser criadas visualizações complexas que se unam a todas as tabelas de consulta. Uma tabela de consulta serve para manter um conjunto de valores aceitáveis ​​para uma coluna. Ele não deve ser codificado armazenando uma chave substituta inteira. Não há nada nas regras de normalização que sugira que você deve armazenar um inteiro substituto em vez do próprio valor.

    6. Eu tenho três livros de database diferentes. Nenhum deles mostra usando chaves substitutas.

    Eu quero compartilhar minha experiência com você nesta guerra sem fim: D no dilema chave natural versus surrogate. Eu acho que ambas as chaves substitutas (artificial auto-generated) e chaves naturais (compostas de coluna (s) com significado de domínio) têm prós e contras . Então, dependendo da sua situação, pode ser mais relevante escolher um método ou outro.

    Como parece que muitas pessoas apresentam chaves substitutas como a solução quase perfeita e as chaves naturais como praga, vou me concentrar nos argumentos do outro ponto de vista:

    Desvantagens de chaves substitutas

    Chaves substitutas são:

    1. Fonte de problemas de desempenho:
      • Eles geralmente são implementados usando colunas auto-incrementadas, o que significa:
        • Uma ida e volta ao database toda vez que você quiser obter um novo ID (eu sei que isso pode ser melhorado usando o cache ou algoritmos [seq] hilo, mas ainda assim esses methods têm suas próprias desvantagens).
        • Se um dia você precisar mover seus dados de um esquema para outro (isso acontece com bastante regularidade em minha empresa, pelo menos), você poderá encontrar problemas de colisão de identificação. E sim, eu sei que você pode usar UUIDs, mas aqueles últimos requer 32 dígitos hexadecimais! (Se você se importa com o tamanho do database, então isso pode ser um problema).
        • Se você estiver usando uma sequência para todas as suas chaves substitutas, então – com certeza – você terminará com a disputa em seu database.
    2. Erro propenso. Uma sequência tem um limite de max_value, portanto, como desenvolvedor, você deve prestar atenção aos seguintes pontos:
      • Você deve alternar sua sequência (quando o valor máximo for atingido, ele volta para 1,2, …).
      • Se você estiver usando a sequência como uma ordenação (ao longo do tempo) de seus dados, então você deve manipular o caso do ciclo (a coluna com Id 1 pode ser mais nova que a linha com Id max-value – 1).
      • Certifique-se de que seu código (e até mesmo suas interfaces de cliente, que não devem acontecer como deveria ser um Id interno), ofereça suporte a inteiros 32b / 64b que você usou para armazenar seus valores de sequência.
    3. Eles não garantem dados não duplicados. Você sempre pode ter duas linhas com os mesmos valores de coluna, mas com um valor gerado diferente. Para mim, esse é o problema das chaves substitutas do ponto de vista do design do database.
    4. Mais na Wikipedia …

    Mitos sobre chaves naturais

    1. Chaves compostas são menos ineficientes do que chaves substitutas. Não! Depende do mecanismo de database usado:
      • Oráculo
      • MySQL
    2. Chaves naturais não existem na vida real. Desculpe, mas eles existem! Na indústria da aviação, por exemplo, a tupla a seguir será sempre única em relação a um determinado voo programado (companhia aérea, departureDate, flightNumber, operationalSuffix). De maneira mais geral, quando um dataset corporativos é garantido como único por um determinado padrão , esse dataset é um candidato de chave natural [bom].
    3. As chaves naturais “poluem o esquema” das tabelas filhas. Para mim isso é mais um sentimento do que um problema real. Ter uma chave primária de 4 colunas de 2 bytes cada pode ser mais eficiente que uma única coluna de 11 bytes. Além disso, as 4 colunas podem ser usadas para consultar a tabela filho diretamente (usando as 4 colunas em uma cláusula where) sem ingressar na tabela pai.

    Conclusão

    Use chaves naturais quando for relevante e use chaves substitutas quando for melhor usá-las.

    Espero que isso tenha ajudado alguém!

    Sempre use uma chave que não tenha significado comercial. É apenas uma boa prática.

    EDIT: eu estava tentando encontrar um link para ele on-line, mas eu não podia. No entanto, em ‘Patterns of Enterprise Archtecture’ [Fowler], há uma boa explicação de por que você não deve usar nada além de uma chave sem outro significado além de ser uma chave. Tudo se resume ao fato de que deveria ter um emprego e apenas um emprego.

    As chaves substitutas são bastante úteis se você planeja usar uma ferramenta ORM para manipular / gerar suas classs de dados. Embora você possa usar chaves compostas com alguns dos mapeadores mais avançados (leia-se: hibernate), isso adiciona alguma complexidade ao seu código.

    (Claro, puristas de database argumentarão que até mesmo a noção de uma chave substituta é uma abominação).

    Eu sou um fã de usar uids para chaves substitutas quando adequado. A grande vitória com eles é que você conhece a chave antecipadamente, por exemplo, você pode criar uma instância de uma class com a ID já definida e garantida como única, ao passo que, digamos, uma chave inteira precisará ser 0 ou 1 e atualize para um valor apropriado quando você salvar / atualizar.

    Os UIDs têm penalidades em termos de pesquisa e velocidade de junit, portanto, depende do aplicativo em questão se eles são desejáveis.

    Usando uma chave substituta é melhor na minha opinião, pois não há chance de mudar. Quase tudo o que posso pensar que você pode usar como uma chave natural pode mudar (disclaimer: nem sempre é verdade, mas comumente).

    Um exemplo pode ser um DB de carros – à primeira vista, você pode pensar que a placa de carro poderia ser usada como chave. Mas isso poderia ser mudado, então seria uma má ideia. Você realmente não gostaria de descobrir isso depois de liberar o aplicativo, quando alguém vem a você querer saber por que eles não podem mudar sua placa de número para o seu shiny personalizado novo.

    Sempre use uma única coluna, chave substituta, se possível. Isso torna as junções, bem como as inserções / atualizações / exclusões muito mais limpas, pois você é o único responsável por rastrear uma única informação para manter o registro.

    Em seguida, conforme necessário, empilhe as chaves comerciais como restrições ou índices exclusivos. Isso manterá a integridade dos dados intacta.

    A lógica de negócios / chaves naturais podem mudar, mas a chave física de uma tabela NUNCA deve mudar.

    Em um cenário de datawarehouse, acredito que é melhor seguir o caminho da chave substituta. Duas razões:

    • Você é independente do sistema de origem e suas alterações – como uma alteração de tipo de dados – não afetarão você.
    • Seu DW precisará de menos espaço físico, pois você usará apenas tipos de dados inteiros para suas chaves substitutas. Além disso, seus índices funcionarão melhor.

    As chaves substitutas podem ser úteis quando as informações comerciais podem mudar ou ser idênticas. Nomes de empresas não precisam ser únicos em todo o país, afinal de contas. Suponha que você lide com duas empresas chamadas Smith Electronics, uma no Kansas e outra em Michigan. Você pode distingui-los pelo endereço, mas isso mudará. Até o estado pode mudar; E se a Smith Electronics de Kansas City, Kansas, atravessar o rio para Kansas City, Missouri? Não há nenhuma maneira óbvia de manter esses negócios distintos com informações de chave naturais, portanto, uma chave substituta é muito útil.

    Pense na chave substituta como um número ISBN. Geralmente, você identifica um livro por título e autor. No entanto, tenho dois livros intitulados “Pearl Harbor”, de HP Willmott, e são definitivamente livros diferentes, não apenas edições diferentes. Em um caso como esse, eu poderia me referir aos olhares dos livros, ou o anterior versus o posterior, mas é tão bom quanto tenho o ISBN para retroceder.

    Como lembrete, não é uma boa prática colocar índices agrupados em chaves substitutas aleatórias, isto é, GUIDs que leem XY8D7-DFD8S, pois o SQL Server não tem capacidade de classificar fisicamente esses dados. Em vez disso, você deve colocar índices exclusivos nesses dados, embora também seja vantajoso simplesmente executar o SQL Profiler para as operações da tabela principal e, em seguida, colocar esses dados no Orientador de Otimização do Mecanismo de Banco de Dados.

    Veja thread @ http://social.msdn.microsoft.com/Forums/en-us/sqlgetstarted/thread/27bd9c77-ec31-44f1-ab7f-bd2cb13129be

    Este é um daqueles casos em que uma chave substituta praticamente sempre faz sentido. Há casos em que você escolhe o que é melhor para o database ou o que é melhor para o modelo de object, mas, em ambos os casos, usar uma chave sem sentido ou GUID é uma ideia melhor. Isso torna a indexação mais fácil e rápida, e é uma identidade para o seu object que não muda.

    Cavalo para cursos. Para afirmar meu preconceito; Eu sou um desenvolvedor primeiro, então estou preocupado principalmente em dar aos usuários um aplicativo de trabalho.

    Eu trabalhei em sistemas com chaves naturais, e tive que gastar muito tempo certificando-me de que as mudanças de valor pudessem passar.

    Eu trabalhei em sistemas com apenas chaves substitutas, e a única desvantagem tem sido a falta de dados desordenados para particionamento.

    A maioria dos desenvolvedores de PL / SQL com os quais trabalhei não gostava de chaves substitutas por causa do número de tabelas por junit, mas nossos bancos de dados de teste e produção nunca exageravam; as junções extras não afetaram o desempenho do aplicativo. Com dialetos de database que não suportam cláusulas como “X inner join Y em Xa = Yb”, ou desenvolvedores que não usam essa syntax, as junções extras para chaves substitutas tornam as consultas mais difíceis de serem lidas e mais longas para digitar e digitar. verifique: veja o post do @Tony Andrews. Mas se você usar um ORM ou qualquer outra estrutura de geração de SQL, não notará isso. A digitação também atenua.

    Caso 1: sua tabela é uma tabela de pesquisa com menos de 50 tipos (inserções)

    Use negócios / chaves naturais . Por exemplo:

     Table: JOB with 50 inserts CODE (primary key) NAME DESCRIPTION PRG PROGRAMMER A programmer is writing code MNG MANAGER A manager is doing whatever CLN CLEANER A cleaner cleans ............... joined with Table: PEOPLE with 100000 inserts foreign key JOBCODE in table PEOPLE looks at primary key CODE in table JOB 

    Caso 2: sua tabela é uma tabela com milhares de inserções

    Use chaves substitutas / de incremento automático . Por exemplo:

     Table: ASSIGNMENT with 1000000 inserts joined with Table: PEOPLE with 100000 inserts foreign key PEOPLEID in table ASSIGNMENT looks at primary key ID in table PEOPLE (autoincrement) 

    No primeiro caso:

    • Você pode selecionar todos os programadores na tabela PEOPLE sem usar join com a tabela JOB, mas apenas com: “SELECT * FROM PESSOAS ONDE JOBCODE = ‘PRG'”

    No segundo caso:

    • Suas consultas ao database são mais rápidas porque sua chave primária é um número inteiro
    • Você não precisa se preocupar em encontrar a próxima chave única, porque o próprio database fornece o próximo incremento automático.

    Talvez não seja completamente relevante para este tópico, mas uma dor de cabeça que tenho lidado com chaves substitutas. A analítica de pré-entrega da Oracle cria SKs gerados automaticamente em todas as suas tabelas de dimensão no warehouse, e também armazena esses dados nos fatos. Assim, sempre que eles (dimensões) precisarem ser recarregados quando novas colunas forem adicionadas ou precisarem ser preenchidas para todos os itens na dimensão, os SKs atribuídos durante a atualização farão com que os SKs fiquem fora de sincronia com os valores originais armazenados no fato, forçando um recarregamento completo de todas as tabelas de fatos que se juntam a ele. Eu preferiria que mesmo se o SK fosse um número sem sentido, haveria alguma maneira que não pudesse mudar para registros originais / antigos. Como muitos sabem, o out-of-the-box raramente atende às necessidades de uma organização, e nós temos que personalizar constantemente. Agora temos 3 anos de dados no nosso depósito, e recargas completas dos sistemas Oracle Financial são muito grandes. Portanto, no meu caso, elas não são geradas a partir da input de dados, mas adicionadas em um warehouse para ajudar no desempenho do relatório. Eu entendo, mas a nossa muda, e é um pesadelo.

    No caso de database pontual, é melhor ter combinação de chaves substitutas e naturais. Por exemplo, você precisa acompanhar as informações de um sócio para um clube. Alguns atributos de um membro nunca mudam. Por exemplo, data de nascimento, mas o nome pode mudar. Portanto, crie uma tabela Member com uma chave substituta member_id e tenha uma coluna para DOB. Crie outra tabela chamada nome da pessoa e tenha colunas para member_id, member_fname, member_lname, date_updated. Nesta tabela, a chave natural seria member_id + date_updated.