Início / dba / Perguntas / 142825
Accepted
Nishant
Asked: 2016-07-03 00:45:28 +0800 CST

Quais são as melhores práticas em relação às tabelas de pesquisa em bancos de dados relacionais?

  • 772

As tabelas de pesquisa (ou tabelas de código , como algumas pessoas as chamam) geralmente são uma coleção dos possíveis valores que podem ser fornecidos para uma determinada coluna.

Por exemplo, suponha que temos uma tabela de consulta chamada party(destinada a armazenar informações sobre partidos políticos) que possui duas colunas:

  • party_code_idn, que contém valores numéricos gerados pelo sistema e (sem significado de domínio de negócios ) funciona como um substituto para a chave real.
  • party_code, é a chave real ou “natural” da tabela porque mantém valores que possuem conotações de domínio de negócios .

E digamos que tal tabela retenha os dados que seguem:

 +----------------+------------+
 | party_code_idn | party_code |
 +----------------+------------+
 |              1 | Republican |
 |              2 | Democratic |
 +----------------+------------+

A party_codecoluna, que mantém os valores 'Republican' e 'Democratic', sendo a chave real da tabela, é configurada com uma restrição UNIQUE, mas opcionalmente adicionei party_code_idne defini como o PK da tabela (embora, logicamente falando , party_codepode funcionar como a CHAVE PRIMÁRIA [PK]).

Pergunta

Quais são as melhores práticas para apontar para valores de pesquisa de tabelas de transação ? Devo estabelecer referências FOREIGN KEY (FK) (a) diretamente ao valor natural e significativo ou (b) a valores substitutos?

A opção (a) , por exemplo,

 +---------------+------------+---------+
 | candidate_idn | party_code |  city   |
 +---------------+------------+---------+
 |             1 | Democratic | Alaska  |
 |             2 | Republican | Memphis |
 +---------------+------------+---------+

tem as seguintes propriedades 1 :

  1. Legível para o usuário final (+)
  2. Fácil de importar-exportar entre sistemas (+)
  3. Difícil alteração do valor pois necessita alteração em todas as tabelas referentes (-)
  4. Adicionar novo valor não custa caro (=)

Acho que é quase como “ passar por valor ”, para fazer uma analogia da chamada de função no jargão de programação de aplicativos.

A opção (b) , por exemplo,

 +---------------+----------------+---------+
 | candidate_idn | party_code_idn |  city   |
 +---------------+----------------+---------+
 |             1 |              1 | Alaska  |
 |             2 |              2 | Memphis |
 +---------------+----------------+---------+

tem as propriedades abaixo:

  1. Não legível para o usuário final (-)
  2. Difícil de importar-exportar , pois precisamos desreferenciar (-)
  3. Fácil de alterar valores, pois estamos armazenando referências apenas em tabelas de transações (+)
  4. Adicionar novo valor não custa caro (=)

É muito semelhante a “ passar por referência ”, se comparar a chamada de função no jargão de programação de aplicativos.

A importação-exportação também pode ser feita de uma maneira diferente, ou seja, apenas preenchendo a tabela de consulta novamente e, em seguida , propagando novamente a coluna substituta. Espero estar acertando, isso é algo que acabei de ouvir como uma possibilidade.

1. Observe que +, -e =indique o benefício dessas propriedades.

Pergunta

Muito importante: existe uma diferença entre uma tabela de pesquisa (ou código ) e uma referência FK se formos apenas usar a última abordagem? Acho que funcionam do mesmo jeito.

Recursos relacionados

database-design foreign-key

3 respostas

  1. Existe uma terceira abordagem que tem algumas das vantagens de suas duas opções - coloque um código real na tabela de códigos. Com isso, quero dizer uma sequência curta de caracteres que captura a essência do valor total e é única. Para o seu exemplo dado, pode ser

    Idn: 1
Name: Democrats
Code: D      (or DEM)

    O Código é carregado em tabelas transacionais como uma chave estrangeira. É curto, inteligível e um tanto independente dos dados "reais". Mudanças incrementais em um nome não sugeririam uma mudança de código. Se os republicanos fugirem em massa , no entanto, uma mudança de código pode ser necessária, com seus problemas concomitantes que uma identidade substituta não incorreria.

    Este estilo foi denominado uma codificação de abreviação. Posso recomendar a escrita de Celko sobre isso. Os livros do Google possuem vários exemplos. Pesquise por "codificação Celko".

    Outros exemplos: codificação de 2 ou 3 letras para países, codificação de 3 letras (GBP, USD, EUR) para códigos de moeda. Curto, autoexplicativo e imutável (e existe um ISO para eles).

    Cada um de Idn, Code e Name será único, de modo que cada um é uma chave candidata e qualquer um pode ser escolhido como a chave primária. Portanto, para o exemplo fornecido, o Idn pode ser removido da definição da tabela e o código usado em seu lugar. Diferentes DBMS lidam com números inteiros e strings à sua própria maneira, portanto, pode haver considerações de desempenho. Pode ser útil ter Idn como FK em algumas tabelas e Code em outras.

    • 12
  2. Best Answer

    Por IDN, entendo que você quer dizer um IDENTITY, SEQUENCEou AUTO_INCREMENTcampo? Você deve dar uma olhada aqui e aqui .

    Observação, seção 5 (Uso incorreto de valores de dados como elementos de dados) da primeira referência, abaixo da figura 10

    É claro que você pode ter uma tabela separada para os vendedores e, em seguida, fazer referência a ela usando uma chave estrangeira, de preferência com uma chave substituta simples, como sales_person_id , mostrada acima.

    Portanto, esse especialista acha que você deve "deferir" as chaves substitutas. É realmente uma técnica de SQL bastante básica e não deve causar problemas no seu SQL do dia-a-dia. Parece que há um erro na figura 10 - o sales_person em SalesData deve ser uma chave substituta (ou seja, um número), não um texto. Estou inferindo isso da citação acima.

    O que você deve evitar a todo custo é a tentação (muito comum para programadores de banco de dados iniciantes) de cometer o erro descrito na seção (1) Tabelas de pesquisa comuns. Isso é comumente chamado de abordagem MUCK ( chave de código massivamente unificada ) (não por acaso :-) notavelmente por Joe Celko , também conhecido sarcasticamente como OTLT - One True Lookup Table ) e leva a todos os tipos de dificuldades. Os programadores novatos parecem sentir que um único código/pesquisa/qualquer tabela é "mais limpo" e será mais eficiente quando nada poderia estar mais longe da verdade.

    Da segunda referência acima:

    A normalização elimina dados redundantes, tornando assim a tarefa de reforçar a integridade dos dados muito mais simples, mas o processo de criação de um MUCK é algo totalmente diferente. Os MUCKs não eliminam dados redundantes, mas são uma eliminação do que são PERCEBIDAS como tabelas redundantes, mas como demonstrarei, menos tabelas não significa simplicidade.

    Você também pode querer dar uma olhada no paradigma EAV ( Entity Attribute Value ) relacionado com o qual trato aqui .

    • 11

  3. A resposta é, como sempre, depende , neste caso, principalmente se seus valores estão sujeitos a mudanças .

    Embora existam muitos princípios concorrentes, o princípio principal em jogo é que, em um banco de dados relacional, você deseja evitar a todo custo a atualização de valores de chave primária (e, portanto, estrangeira). Outros podem argumentar que alguns dos outros fatores que você mencionou, como a legibilidade, por exemplo, são os mais importantes. No entanto, eu diria que os dados que são quebrados semanticamente não ajudam você, sejam legíveis ou não.

    Com base em muitos anos de amarga experiência, uso a regra prática de que qualquer elemento de dados que possa mudar provavelmente acabará mudando . É por isso que sou a favor de chaves primárias sem sentido para quase todas as tabelas. Existem exceções. Por exemplo, se houver um corpo de padrões (por exemplo, ISO) que declarou um valor de código de pesquisa (por exemplo, USD para dólares dos Estados Unidos), eu confiaria que isso seria estável.

    Para o seu caso de uso específico do nome do partido político, apresento para sua consideração o principal partido conservador federal no Canadá, que foi conhecido por muitos anos como o "Partido Conservador Progressista", mas após uma fusão com (assumida por) a Reforma Partido, eles se tornaram, brevemente, o "Partido da Aliança Conservadora-Reformista" e então, depois que alguém se preocupou em pensar nas ramificações das abreviações, eles se estabeleceram no "Partido Conservador do Canadá". Então, aqui está um exemplo concreto de onde usar uma chave substituta para uma tabela de pesquisa permitiria que você mantivesse seus dados precisos e consistentes.

    Por outro lado (há sempre pelo menos um), se o valor da tabela de pesquisa não for tão difundido ou não tão central para o seu domínio de dados, pode valer a pena o risco de usar uma chave natural. Todo o design é um trade-off.

    • 2

relate perguntas