Início / dba / Perguntas / 9011
Accepted
Brad Turner
Asked: 2011-12-10 16:39:15 +0800 CST

Uso adequado de tabelas de pesquisa

  • 772

Estou tendo problemas para descobrir exatamente como colocar bons limites para quando e onde usar tabelas de pesquisa em um banco de dados. A maioria das fontes que consultei dizem que nunca é demais, mas, em algum momento, parece que o banco de dados seria dividido em tantos pedaços que, embora possa ser eficiente, não é mais gerenciável. Aqui está um exemplo combinado do que estou trabalhando:

Digamos que eu tenha uma tabela chamada Funcionários:

ID  LName   FName   Gender  Position
1   Doe     John    Male    Manager
2   Doe     Jane    Female  Sales
3   Smith   John    Male    Sales

Imagine por um momento que os dados são mais complexos e contêm centenas de linhas. A coisa mais óbvia que vejo que poderia ser movida para uma tabela de pesquisa seria Posição. Eu poderia criar uma tabela chamada Positions e colocar as chaves estrangeiras da tabela Positions na tabela Employees na coluna Position.

ID  Position
1   Manager
2   Sales

Mas até que ponto posso continuar a dividir as informações em tabelas de pesquisa menores antes que elas se tornem incontroláveis? Eu poderia criar uma tabela de gênero e ter 1 correspondendo a masculino e 2 correspondendo a feminino em uma tabela de pesquisa separada. Eu poderia até colocar LNames e FNames em tabelas. Todas as entradas "John" são substituídas por uma chave estrangeira de 1 que aponta para a tabela FName que diz que um ID de 1 corresponde a John. No entanto, se você for longe demais nessa toca do coelho, sua tabela Employees será reduzida a uma confusão de chaves estrangeiras:

ID  LName   FName   Gender  Position
1   1       1       1       1
2   1       2       2       2
3   2       1       1       2

Embora isso possa ou não ser mais eficiente para um servidor processar, certamente é ilegível para uma pessoa normal que pode estar tentando mantê-lo e torna mais difícil para um desenvolvedor de aplicativos tentar acessá-lo. Então, minha verdadeira pergunta é quão longe é longe demais? Existem "práticas recomendadas" para esse tipo de coisa ou um bom conjunto de diretrizes em algum lugar? Não consigo encontrar nenhuma informação on-line que realmente estabeleça um conjunto de diretrizes bom e utilizável para esse problema específico que estou tendo. O design de banco de dados é antigo para mim, mas o BOM design de banco de dados é muito novo, portanto, respostas excessivamente técnicas podem estar além da minha cabeça. Qualquer ajuda seria apreciada!

relational-theory

3 respostas

  1. Best Answer

    Mas até que ponto posso continuar a dividir as informações em tabelas de pesquisa menores antes que elas se tornem incontroláveis? Eu poderia criar uma tabela de gênero e ter 1 correspondendo a masculino e 2 correspondendo a feminino em uma tabela de pesquisa separada.

    Você está misturando dois problemas diferentes. Um problema é o uso de uma tabela de "pesquisa"; o outro é o uso de chaves substitutas (números de identificação).

    Comece com esta tabela.

    ID  LName   FName   Gender  Position
1   Doe     John    Male    Manager
2   Doe     Jane    Female  Sales
3   Smith   John    Male    Sales

    Você pode criar uma tabela de "pesquisa" para posições como esta.

    create table positions (
  pos_name varchar(10) primary key
);

insert into positions
select distinct position 
from employees;

alter table employees
add constraint emp_fk1
foreign key (position) 
  references positions (pos_name);

    Sua tabela original parece exatamente como antes de criar a tabela "lookup". E a tabela de funcionários não requer junções adicionais para obter dados úteis e legíveis por humanos.

    O uso de uma tabela de "pesquisa" se resume a isso: seu aplicativo precisa do controle sobre os valores de entrada fornecidos por uma referência de chave estrangeira? Nesse caso, você sempre pode usar uma tabela de "pesquisa". (Independentemente de usar uma chave substituta.)

    Em alguns casos, você poderá preencher completamente essa tabela em tempo de design. Em outros casos, os usuários precisam ser capazes de adicionar linhas a essa tabela em tempo de execução. (E você provavelmente precisará incluir alguns processos administrativos para revisar novos dados.) Gênero, que na verdade tem um padrão ISO , pode ser totalmente preenchido no momento do design. Nomes de ruas para pedidos internacionais de produtos on-line provavelmente precisam ser adicionados em tempo de execução.

    • 22

  2. Em sua tabela Funcionários, eu teria apenas uma pesquisa para "Posição" porque é um conjunto limitado de dados que pode ser expandido.

    • O gênero é autodescritivo (digamos Mou F), limitado a 2 valores e pode ser aplicado com uma restrição CHECK. Você não adicionará novos gêneros (ignorando as besteiras do politicamente correto)
    • O primeiro nome "John" não faz parte de um conjunto limitado e restrito de dados: o conjunto potencial de dados é enorme a ponto de efetivamente ilimitado, portanto não deve ser uma pesquisa

    Se você quiser adicionar uma nova posição, basta adicionar uma linha à tabela de pesquisa. Isso também remove anomalias de modificação de dados, que é um ponto de normalização

    Além disso, uma vez que você tenha um milhão de funcionários, é mais eficiente armazenar tinyint PositionID do que varchar.

    Vamos adicionar uma nova coluna "moeda do salário". Eu usaria uma tabela de pesquisa aqui com uma chave de CHF, GBP, EUR, USD etc: eu não usaria uma chave substituta. Isso pode ser restringido com uma restrição CHECK como Gender, mas é um conjunto de dados limitado, mas expansível, como Position. Eu dou este exemplo porque eu usaria a chave natural mesmo que ela apareça em um milhão de linhas de dados de funcionários, apesar de ser char (3) em vez de tinyint

    Então, para resumir, você usa tabelas de pesquisa

    1. onde você tem um conjunto de dados finito, mas expansível em uma coluna
    2. onde está não é auto-descritivo
    3. para evitar anomalias de modificação de dados
    • 8

  3. A resposta é um "depende". Não é muito satisfatório, mas há muitas influências empurrando e puxando o design. Se você tiver programadores de aplicativos projetando o banco de dados, uma estrutura como a que você descreve funciona para eles porque o ORM oculta a complexidade. Você vai arrancar os cabelos ao escrever relatórios e terá que entrar em dez mesas para obter um endereço.

    Design para o uso, uso pretendido e uso futuro provável. É aqui que entra o seu conhecimento do processo de negócios. Se você estiver projetando um banco de dados para uma empresa veterinária, há suposições razoáveis ​​sobre tamanho, uso e direções na funcionalidade que serão bem diferentes de uma start-up de alta tecnologia.

    Para reutilizar uma citação favorita

    "Um homem sábio uma vez me disse "normalize até doer, desnormalize até funcionar".

    Em algum lugar lá está o ponto ideal. Minha experiência tem sido que ter um id de chave em mais de uma tabela não é um crime tão sério quanto alguns pensam se você nunca mudar as chaves primárias.

    Veja este exemplo abreviado de tabelas altamente normalizadas de um sistema real

    CREATE TABLE PROPERTY
(ID                          NUMBER(9)           NOT NULL);

CREATE TABLE PROPERTY_TYPE
(ID                          NUMBER(9)           NOT NULL);

CREATE TABLE PROPERTY_LOCALE 
PROPERTY_ID                  NUMBER(9)           NOT NULL,
(LOCALE_ID                   NUMBER(9)           NOT NULL,  --language 
VALUE                        VARCHAR2(200)       NOT NULL);

CREATE TABLE PROPERTY_DEPENDENCY
(PROPERTY_ID                 NUMBER(9)           NOT NULL,
 PARENT_PROPERTY_ID          NUMBER(9)                   ,
 PROPERTY_TYPE_ID            NUMBER(9)           NOT NULL);

    Essas tabelas configuram uma lista vinculada de propriedades únicas e propriedades pai-filho e são usadas aqui 

      CREATE TABLE CASE_PROPERTY
  (ID                        NUMBER(9)           NOT NULL,
  PARENT_ID                  NUMBER(9),
  CASE_ID                    NUMBER(9)           NOT NULL,
  PROPERTY_ID                NUMBER(9),
  PROPERTY_TYPE_ID           NUMBER(9)           NOT NULL);

    Isso parece bom: obtenha todos os casos com um property_id em uma seleção

    Vamos pegar uma lista para escolher

     Select pl.value, pd.property_id
 from property_locale pl, property_dependency pd
 where pl.property_id = pd.property_id
 and pd.property_type_id = 2;  --example number

    Agora tente selecionar todas as propriedades de um caso se ele tiver property_types de 3 e 4 e 5, ou não...

    SELECT   cp2.case_id,
         (SELECT   pl.VALUE
            FROM   case_property cp, property_locale pl
           WHERE       cp.property_id = pl.property_id
                   AND CP.PROPERTY_TYPE_ID = 2
                   AND pl.locale_id = 2
                   AND cp.case_id = cp2.case_id)
            AS VALUE1,
         (SELECT   pl.VALUE
            FROM   case_property cp, property_locale pl
           WHERE       cp.property_id = pl.property_id
                   AND CP.PROPERTY_TYPE_ID = 34
                   AND pl.locale_id = 2
                   AND cp.case_id = cp2.case_id)
            AS VALUE2,
         (SELECT   pl.VALUE
            FROM   case_property cp, property_locale pl
           WHERE       cp.property_id = pl.property_id
                   AND CP.PROPERTY_TYPE_ID = 4
                   AND pl.locale_id = 2
                   AND cp.case_id = cp2.case_id)
            AS VALUE3
  FROM   case_property cp2
 WHERE   cp2.case_id = 10293

    Isso só dói... mesmo quando você usa formas mais elegantes de lidar com isso. No entanto, adicione um pouco de normalização quebrando as propriedades para as quais um caso terá apenas um property_id e isso pode ser muito melhor.

    Para descobrir quando você tem muitas tabelas ou não o suficiente, tente consultar o banco de dados com perguntas que o aplicativo usará, um relatório e uma análise ano a ano.

    • 5

relate perguntas