Dentro de um aplicativo da Web em que estou trabalhando, todas as operações de banco de dados são abstraídas usando alguns repositórios genéricos definidos sobre o Entity Framework ORM.
No entanto, para ter um design simples para os repositórios genéricos, todas as tabelas envolvidas devem definir um inteiro único ( Int32em C#, intem SQL). Até agora, esse sempre foi o PK da mesa e também o IDENTITY.
As chaves estrangeiras são muito usadas e fazem referência a essas colunas inteiras. Eles são necessários para a consistência e para a geração de propriedades de navegação pelo ORM.
A camada de aplicação normalmente faz as seguintes operações:
- carga de dados inicial da tabela (*) -
SELECT * FROM table - Atualizar -
UPDATE table SET Col1 = Val1 WHERE Id = IdVal - Excluir -
DELETE FROM table WHERE Id = IdVal - Inserir -
INSERT INTO table (cols) VALUES (...)
Operações menos frequentes:
- Inserção em massa -
BULK INSERT ... into tableseguida (*) por todo o carregamento de dados (para recuperar identificadores gerados) - Exclusão em massa - esta é uma operação de exclusão normal, mas "volumosa" da perspectiva do ORM:
DELETE FROM table where OtherThanIdCol = SomeValue - Atualização em massa - esta é uma operação de atualização normal, mas "volumosa" da perspectiva do ORM:
UPDATE table SET SomeCol = SomeVal WHERE OtherThanIdCol = OtherValue
*todas as tabelas pequenas são armazenadas em cache no nível do aplicativo e quase todas SELECTsnão alcançarão o banco de dados. Um padrão típico é a carga inicial e muitos INSERTs, UPDATEs e DELETEs.
Com base no uso atual do aplicativo, há uma chance muito pequena de atingir 100 milhões de registros em qualquer uma das tabelas.
Pergunta: Do ponto de vista de um DBA, existem problemas significativos que posso encontrar por ter essa limitação de design de tabela?
[EDITAR]
Depois de ler as respostas (obrigado pelo ótimo feedback) e os artigos referenciados, sinto que tenho que adicionar mais detalhes:
Especificidades do aplicativo atual - não mencionei sobre o aplicativo da web atual, porque quero entender se o modelo pode ser reutilizado para outros aplicativos também. No entanto, meu caso particular é um aplicativo que extrai muitos metadados de um DWH. Os dados de origem são bastante confusos (desnormalizados de maneira estranha, com algumas inconsistências, sem identificador natural em muitos casos etc.) e meu aplicativo está gerando entidades separadas claras. Além disso, muitos dos identificadores gerados (
IDENTITY) são exibidos, para que o usuário possa utilizá-los como chaves de negócios. Isso, além de uma refatoração de código massiva, exclui o uso de GUIDs ."eles não devem ser a única maneira de identificar exclusivamente uma linha" (Aaron Bertrand♦) - esse é um conselho muito bom. Todas as minhas tabelas também definem uma UNIQUE CONSTRAINT para garantir que duplicatas de negócios não sejam permitidas.
Design orientado a aplicativos de front-end versus design orientado a banco de dados - a escolha do design é causada por esses fatores
Limitações do Entity Framework - PKs de várias colunas são permitidos, mas seus valores não podem ser atualizados
Limitações personalizadas - ter uma única chave inteira simplifica muito as estruturas de dados e o código não SQL. Ex.: todas as listas de valores possuem uma chave inteira e valores exibidos. Mais importante, garante que qualquer tabela marcada para armazenamento em cache poderá ser colocada em um
Unique int key -> valuemapa.
Consultas de seleção complexas - isso quase nunca acontecerá porque todos os dados de tabelas pequenas (< 20-30 K registros) são armazenados em cache no nível do aplicativo. Isso torna a vida um pouco mais difícil ao escrever o código do aplicativo (mais difícil de escrever LINQ), mas o banco de dados é atingido muito melhor:
Visualizações de lista - não gerarão
SELECTconsultas no carregamento (tudo é armazenado em cache) ou consultas que se parecem com isso:SELECT allcolumns FROM BigTable WHERE filter1 IN (val1, val2) AND filter2 IN (val11, val12)Todos os outros valores necessários são buscados por meio de pesquisas de cache (O(1)), portanto, nenhuma consulta complexa será gerada.
Editar visualizações - gerará
SELECTinstruções como esta:SELECT allcolumns FROM BigTable WHERE PKId = value1
(todos os filtros e valores são ints)
Além de espaço em disco adicional (e, por sua vez, uso de memória e E/S), não há nenhum mal em adicionar uma coluna IDENTITY mesmo a tabelas que não precisam de uma (um exemplo de uma tabela que não precisa de uma coluna IDENTITY é uma tabela de junção simples, como mapear um usuário para suas permissões).
Eu protesto contra adicioná-los cegamente a todas as tabelas em um post de blog de 2010:
Mas as chaves substitutas têm casos de uso válidos - apenas tome cuidado para não assumir que elas garantem exclusividade (que às vezes é o motivo pelo qual são adicionadas - elas não devem ser a única maneira de identificar exclusivamente uma linha). Se você precisar usar uma estrutura ORM e sua estrutura ORM exigir chaves inteiras de coluna única, mesmo nos casos em que sua chave real não é um número inteiro, não é uma única coluna ou nenhuma delas, certifique-se de definir restrições/índices exclusivos para suas chaves reais também.
Pela minha experiência, a principal e esmagadora razão para usar um ID separado para cada tabela é a seguinte:
Em quase todos os casos meu cliente fez um juramento de sangue na fase de concepção que algum campo externo, "natural"
XYZBLARGH_IDpermanecerá único para sempre, e nunca mudará para uma determinada entidade, e nunca será reutilizado, eventualmente surgiram casos em que o As propriedades da chave primária foram quebradas. Simplesmente não funciona assim.Então, do ponto de vista do DBA, as coisas que tornam um banco de dados lento ou inchado certamente não são 4 bytes (ou qualquer outra coisa) por linha, mas coisas como índices errados ou ausentes, reorganizações esquecidas de tabela/índice, parâmetros de ajuste de RAM/espaço de tabela errados , negligenciando o uso de variáveis de ligação e assim por diante. Esses podem desacelerar o banco de dados por fatores de 10, 100, 10000... não uma coluna de ID adicional.
Portanto, mesmo que houvesse uma desvantagem técnica e mensurável de ter 32 bits adicionais por linha, não é uma questão de otimizar o ID, mas se o ID será essencial em algum momento, o que será mais provável do que não. E não vou contar todos os benefícios "soft" de uma postura de desenvolvimento de software (como seu exemplo ORM, ou o fato de facilitar para desenvolvedores de software quando todos os IDs por design têm o mesmo tipo de dados e assim por diante) .
NB: note que você não precisa de um ID separado para
n:mtabelas de associação porque para essas tabelas os IDs das entidades associadas devem formar uma chave primária. Um contra-exemplo seria uman:massociação estranha que permite várias associações entre as mesmas duas entidades por qualquer motivo bizarro - elas precisariam de sua própria coluna de ID para criar um PK. Existem bibliotecas ORM que não podem lidar com PKs de várias colunas, então isso seria um motivo para ser tolerante com os desenvolvedores, se eles tiverem que trabalhar com essa biblioteca.Se você invariavelmente adicionar uma coluna extra sem sentido a cada tabela e referenciar apenas essas colunas como chaves estrangeiras, quase inevitavelmente você tornará o banco de dados mais complexo e difícil de usar. Efetivamente, você removerá dados de interesse dos usuários dos atributos de chave estrangeira e forçará o usuário/aplicativo a fazer uma junção extra para recuperar essas mesmas informações. As consultas se tornam mais complexas, o trabalho do otimizador se torna mais difícil e o desempenho pode ser prejudicado.
Suas tabelas serão mais esparsamente preenchidas com dados "reais" do que seriam de outra forma. O banco de dados será, portanto, mais difícil de compreender e verificar. Você também pode achar difícil ou impossível impor certas restrições úteis (onde as restrições envolveriam vários atributos que não estão mais na mesma tabela).
Eu sugiro que você escolha suas chaves com mais cuidado e as torne inteiras somente se/quando você tiver boas razões para isso. Baseie seus projetos de banco de dados em boa análise, integridade de dados, praticidade e resultados verificáveis, em vez de confiar em regras dogmáticas.
Na minha experiência com vários bancos de dados, uma chave primária Integer é sempre melhor do que os aplicativos que não possuem nenhuma chave definida. Ou que tem chaves que unem meia dúzia de colunas varchar de maneiras estranhas que não são lógicas... (suspiro)
Eu vi aplicativos que mudaram de PKs inteiros para GUIDs. A razão para isso foi porque havia a necessidade de mesclar dados de vários bancos de dados de origem em certos casos. Os desenvolvedores mudaram todas as chaves para GUIDs para que as mesclagens pudessem acontecer sem medo de colisões de dados, mesmo em tabelas que não faziam parte da mesclagem (apenas no caso dessas tabelas se tornarem parte de uma mesclagem futura).
Eu diria que um PK inteiro não vai mordê-lo, a menos que você planeje mesclar dados de fontes separadas ou possa ter dados que vão além dos limites de tamanho inteiro - é tudo divertido e divertido até que você fique sem espaço para inserções .
Direi, no entanto, que pode fazer sentido definir seu índice clusterizado em uma coluna diferente do seu PK, se a tabela for consultada com mais frequência dessa maneira. Mas esse é um caso atípico, especialmente se a maior parte das atualizações e seleções forem baseadas nos valores de PK.
Pondo de lado:
Desde que você esteja usando a exclusão/atualização em massa quando apropriado e tenha índices para dar suporte a essas operações, não acho que você terá problemas devido ao padrão PK usado.
É possível que, se mais tarde você tiver o EF gerar consultas com junções, etc, elas não serão tão eficientes quanto seriam com um repositório baseado em chave natural, mas eu não sei o suficiente sobre essa área para dizer com certeza de qualquer maneira.
Você tem alguns fatores para ajudar a guiá-lo,
Definição e especificação.
Se algo é definido como único pela tarefa ou pelas leis da física, você está desperdiçando seu tempo com uma chave substituta.
Singularidade.
Para sanidade pessoal, junções e funcionalidade de banco de dados de nível superior, você precisará de (a) coluna exclusiva, (b) série exclusiva de colunas
Todos os esquemas suficientemente normalizados (1NF) fornecem um dos seguintes. Se não, você deve sempre criar um. Se você tem uma lista de pessoas definidas para serem voluntárias no domingo, e inclui sobrenome e nome, você vai querer saber quando você tem dois Joe Bobs.
Implementação e otimização.
Um int tende a ser um pequeno formulário de dados que é rápido para comparação e igualdade. Compare isso com uma string Unicode cujo agrupamento pode depender da localidade (local e idioma). Armazenar um 4242 em uma string ASCII/UTF8 é de 4 bytes. Armazenando-o como um inteiro cabe em 2 bytes.
Então, quando se trata de desvantagens, você tem alguns fatores.
Confusão e ambiguidade.
Espaço.
Os inteiros ainda adicionam espaço à linha. E, se você não os estiver usando, não há propósito.
Agrupamento.
Você só pode solicitar seus dados de uma maneira. Se você impõe uma chave substituta que não é necessária, você agrupa dessa maneira ou da maneira da chave natural?