Conceito de Surrogate Key – Chaves Substitutas

Uma Surrogate Key, em um Banco de dados, é uma chave de substituição. É um identificador único para cada entidade do mundo modelado ou um objeto no banco de dados.  A chave substituta não é derivada de dados do aplicativo. OBS. O assunto esta sendo abordado de uma forma geral, sem se ater a modelagem dimensional ou conceitos de DW/BI. Sempre que for conveniente, colocarei o conceito em um ambiente dimensional, que é a nossa seara…

Há pelo menos duas definições de um substituto:

Substituto (1)

Esta definição baseia-se no que é dado pelo Hall, Owlett e Todd (1976).  Aqui um substituto representa uma entidade no mundo exterior.  O substituto é gerado internamente pelo sistema, mas é, contudo, visível para o usuário ou aplicação.

Substituto (2)

Esta definição baseia-se no que é dado pelo Wieringa e De Jonge (1991).  Aqui um substituto representa um objeto no banco de dados em si.  O substituto é gerado internamente pelo sistema e é invisível para o usuário ou aplicação.

A definição (1) define substituto e seu uso no modelo de dados ao invés do modelo de armazenamento e é utilizada neste artigo.

Uma distinção importante entre um substituto e uma chave primária, depende da estruturação do uso do banco de dados: se é um banco de dados atual ou um banco de dados temporal.

Um banco de dados atual armazena apenas dados atuais válidos, portanto, há uma correspondência tipo um-para-um entre um substituto no mundo modelado e a chave primária de algum objeto no banco de dados. Neste caso, o substituto pode ser usado como uma chave primária, resultando no termo chave substituta(Surrogate Key).  No entanto, em um banco de dados temporal há um relacionamento de muitos para um entre as chaves primárias e os substitutos.  Como pode haver vários objetos no banco de dados correspondente a um único substituto, não podemos usar o substituto como uma chave primária, outro atributo é necessário, além do substituto, para identificar cada objeto.

Levando esse conceito para uma modelagem dimensional, aprendemos que chaves compostas em dimensões causam uma certa complexidade na estruturação de um SQL, além de afetar o tempo de execução desse SQL. Assim sendo, quando uma tabela origem de uma dimensão possui uma chave composta para a identificação única da linha na tabela, usamos uma SURROGATE KEY única para representar essa primary key composta. Mesmo que não tenhamos uma chave composta no sistema de origem, criamos uma Surrogate Key na dimensão (com a finalidade de Primary Key), para permitir um controle histórico dos dados. 

Temos assim, o que chamamos de Chave do Sistema Transacional, que seria a primary Key composta, e a Chave de Negócio da dimensão.

Apesar de Hall, Owlett e Todd (1976) não dizerem nada sobre isso, outras fontes argumentaram que um substituto deve obedecer as seguintes regras:

  • O valor é único para todo o sistema, portanto nunca reutilizadas;
  • O valor é gerado pelo sistema;
  • O valor não é manipulável pelo usuário ou aplicação;
  • O valor não contém nenhum significado semântico;
  • O valor não é visível para o usuário ou aplicação (no caso de modelagem relacional);
  • O valor não é composto de vários valores a partir de diferentes domínios.

Substitutos na prática

Em um banco de dados atual, a chave substituta pode ser a chave primária, gerada pelo sistema de gerenciamento de banco de dados e não derivado de qualquer dados do aplicativo no banco de dados.  O único significado da chave substituta é atuar como chave primária.  Também é possível que a chave substituta exista em adição ao identificador único gerado pelo banco de dados (por exemplo, um número de RH de cada trabalhador, além do identificador único de cada funcionário).

Uma surrogate key é freqüentemente um número seqüencial (por exemplo, Sybase e SQL Server “coluna de identidade”, um PostgreSQL serial , um Oracle SEQUENCE ou uma coluna definida com AUTO_INCREMENT no MySQL ), mas não tem que, necessariamente, ser assim.  Ter a chave independente de todas as outras colunas isola possíveis mudanças de valores nos relacionamentos do banco de dados ou projeto do banco de dados (tornando a base de dados mais ágil ) garantindo as singularidades.

Em um banco de dados temporais, é necessário distinguir entre a chave substituta e a chave primária.  Normalmente, cada linha teria tanto uma chave primária e uma chave substituta.  A chave primária identifica a linha único no banco de dados, a chave substituta identifica a entidade única no mundo modelado; essas duas chaves não são as mesmas.  Por exemplo, o quadro de pessoal pode conter duas linhas para “John Smith”, uma linha quando foi empregado entre 1990 e 1999, outra linha quando foi empregado entre 2001 e 2006.  Na modelagem dimensional, usando uma SK, teremos a chave do sistema de origem única para os dois casos (chave primária no sistema de origem), mas as SKs serão diferentes, uma para cada intervalo de tempo.

Alguns projetistas de banco de dados usam chaves substitutas sistemáticamente, independentemente da adequação de outras chaves candidatas, enquanto outros vão usar uma chave já presente nos dados, se houver.

A chave substituta também pode ser chamada de chave sintética, um identificador de entidade, uma chave gerada pelo sistema, um número de seqüência de base de dados, uma chave ‘factless’ (sem Fato), uma chave técnica, ou identificador exclusivo arbitrário.  

Alguns desses termos descrevem a forma de gerar novos valores substitutos em vez do conceito natural de substituto.

Aqui estão alguns possíveis candidatos para a geração de substitutos:

  • Identificadores Únicos Universais (Universally Unique Identifiers (UUIDs))
  • Identificadores Únicos Globais (Globally Unique Identifiers (GUIDs))
  • Identificadores de Objeto (Object Identifiers (OIDs))
  • Sybase ou SQL Server coluna de identidade
  • Oracle SEQUENCE
  • PostgreSQL serial 
  • MySQL AUTO_INCREMENT
  • Tipo de dados Auto_Increment no Microsoft Access
  • AS IDENTITY GENERATED BY DEFAULT no IBM DB2

Vantagens

Imutabilidade

As chaves substitutas não mudam enquanto a linha existe. Isso tem as seguintes vantagens:

  • As aplicações não perdem a referência à linha no Banco de Dados, uma vez que o identificador nunca muda.
  • Os dados da chave primária podem ser sempre modificados, mesmo com bases de dados que não suportam atualizações em cascata através de  chaves estrangeiras relacionadas.

Mudanças nos requisitos

Atributos que identificam uma entidade única podem mudar, o que poderia invalidar a adequação das chaves compostas naturais. Considere o seguinte exemplo:

O nome de um funcionário usuário de rede é escolhido como uma chave natural.  Após a fusão com outra empresa, os novos funcionários devem ser inseridos.  Alguns dos novos nomes de usuários da rede criam conflitos, porque seus nomes de usuário foram gerados de forma independente (quando as empresas foram separadas).

Nesses casos, geralmente um novo atributo deve ser adicionado à chave natural, por exemplo, uma coluna empresa original. Com uma chave substituta, apenas a tabela que define a chave substituta deve ser mudada.  Com chaves naturais, todas as tabelas que usam a chave natural terão que mudar.

Alguns problemas de domínios não identificam claramente uma chave apropriada natural.  As chaves substitutas evitam escolher uma chave natural, que pode estar incorreta.

Performance Desempenho

As chaves substitutas tendem a ser um tipo de dados compacto, como um inteiro de quatro bytes.  Isso permite que o banco de dados consulte a coluna de chave única mais rápida do que o faria com múltiplas colunas.  Mais:

  • A distribuição não-redundante de chaves faz com que o resultado do índice b-tree seja completamente equilibrado.
  • As chaves substitutas são menos onerosos para o uso em Join (menos colunas para comparar) do de chaves compostas.

Compatibilidade

Utilizando vários sistemas de banco de dados de desenvolvimento de aplicativos, drivers e mapeamento objeto-relacional de sistemas, tais como Ruby on Rails ou Hibernate (Java), é muito mais fácil usar uma chave substituta inteira ou GUID para cada tabela, em vez de chaves naturais, a fim de suportar as operações de banco de dados do sistema agnóstico e objetos de mapeamento de linha.

Uniformidade

Ao ter uma chave substituta uniforme em cada tabela, algumas tarefas podem ser facilmente automatizadas para todas as tabelas, bastando escrever o código de forma tabela-independente.

Validação

É possível projetar valores de chave tais como as que seguem um certo padrão conhecido ou estrutura que podem ser verificadas automaticamente por alguns meios.  Por exemplo, as chaves que são destinadas a serem utilizadas em algumas colunas de uma tabela, podem ser projetadas para ter um “olhar diferente” daquelas que se destinam a ser usados em outra coluna ou uma tabela, simplificando assim a detecção de erros de aplicativo no qual as chaves tenham sido extraviadas. Entretanto, esta característica das chaves substitutas nunca deve ser usada para conduzir qualquer lógica dos próprios aplicativos, pois isso pode violar os princípios da normalização de banco de dados.

Até a Próxima!

13 Respostas to “Conceito de Surrogate Key – Chaves Substitutas”

  1. Thiago Ornellas Says:

    Olha só… se não é o mesmo “artigo”, mas em inglês
    http://en.wikipedia.org/wiki/Surrogate_key

    • Carlos Alberto Lorenzi Lima Says:

      Salve Thiago, tudo bem?

      É sim! Costumo pesquisar vários sites, vários livros em inglês, e coloco os textos, alguns traduzidos, outros em inglês mesmo, para compartilhar com o pessoal.

      Também recebo dicas da Kimball University e coloco. A idéia e divulgar e compartilhar vários textos sobre DW e BI, inclusive de algumas pessoas que me mandam.

      Obrigado por sua visita.

      Até mais.

      Lito

  2. Alberto Costa Says:

    Existe alguma teoria que explica o uso de SK com mais um campo como chave de uma dimensão, por exemplo? Isto é muito usado, pois a SK garante a unicidade da informação com os seus diversos históricos. Obrigado.

    • Carlos Alberto Lorenzi Lima Says:

      Alberto, tudo bem?

      A função da Surrogate Key é a de permitir que você tenha uma chave única. Não conheço nenhuma teoria ou escrito sobre o uso de uma SK composta, mas isso tiraria uma das finalidade da chamada chave substituta, que é a de substituir as chaves compostas dos sistemas de origem.

      Mas, isso não é uma regra fixa… Eu diria que tudo depende do negócio…..

      Abração.

      Lito

  3. Carlos Eduardo Pereira Araujo Says:

    Olá Carlos, a empresa que trabalho decidiu adotar Surrogate Key .
    Eu particularmente curto e sempre trabalhei desse jeito (exceto para tabelas de dominio), porém surgiu uma dúvida. É correto usar surrogate key para tudo ?Inclusive para tabelas de dominio como: Status, Estado Civil, UF?
    Ou seja, ao invés de ter uma tabela com #SG_UF, DS_UF para representar SP, São Paulo ou então #CD_ESTADOCIVIL, DS_ESTADOCIVIL para representar C=Casado, S=Solteiro teríamos que utilizar ID?
    Não ficaria estranho utilizar #ID_UF, CD_UF, DS_UF para representar 1, SP, São Paulo ?
    Como fazer com os dados de domínio ou sistema?

    • Carlos Alberto Lorenzi Lima Says:

      Carlão, tudo bem????
      Desculpe a demora, mas eu estava viajando.
      Meu caro, não existe uma regra rígida para o uso de SK… O uso da SK, a meu ver, deve ser ‘obrigatório’ sempre que desejarmos manter um histórico de determinada característica – A chamada SCD (Slowly Change Dimension), e sempre que quisermos separar as chaves do OLTP com as chaves do OLAP.

      Isso vai de cada arquiteto… Eu, por exemplo, não costumo criar dimensões que possuam uma chave e um descritivo, ou seja, Estado Civil, Sexo, UF, etc…. Coloco essas informações como uma característica de dimensões como Clientes, Região, etc…, mas isso não significa que seja errado. Precisamos apenas, tomar o cuidado de não criar FATOS com muitas chaves estrangeiras ( a chamada CENTIPEDE) pois dificultará o acesso e tempo de resposta.

      Uma outra possibilidade, seria a criação de uma JUNK DIMENSION, onde colocaríamos em apenas uma SK, várias características de um Fato (Tipo de Pagamento, meio de Pagamento, Canal de Vendas, etc…).

      Dê uma lida sobre JUNK DIMENSIONS…. No meu blog tem algo sobre isso.

      Grande abraço.

      Lito

  4. Sandra Ferreira Says:

    Boa noite, tenho uma dúvida qto ao uso de SK como PK: a possibilidade de duplicidade das linhas(exceto a coluna SK) não seria considerada?

    • Carlos Alberto Lorenzi Lima Says:

      Sandra, bom dia!!!

      O uso de surrogate key serve, exatamente, para que você possa ter linhas duplicadas! Linhas referentes, por exemplo, a um mesmo cliente… Eu posso ter o mesmo cliente, com SKs diferentes, porém, com conteúdo de colunas diferentes: teríamos, mais um exemplo, uma linha com a coluna de ESTADO CIVIL igual a ‘Solteiro’ e outra linha, com outra SK, para o mesmo cliente, com a coluna ESTADO CIVIL igual a ‘Casado’, assim poderíamos controlar todas as compras que ele fez quando era solteiro, e todas as compras quando casado. Observe que, nesse caso, precisamos ter colunas de controle e, as mais usuais, são as colunas DATA INICIO e DATA FIM, para controlar o período de validade de cada uma dessas linhas.

      Caberá a nós, em nosso projeto de ETL, controlar as cargas dessas linhas, de forma a evitar a duplicidade total das mesmas, sem updates…

      Certo?

      Abração!!!!!

      Lito

  5. João Coimbra Says:

    Só não podemos deixar de identificar bem essa SK de que tabela etc ela saiu, senão fica um mar de SKs e qualquer um que vá ler o código fica perdido

  6. Herbert Dos Santos Says:

    Thiago,

    Tenho um grande problema na minha empresa que se resume em:

    -Quando a empresa era pequena, foi criada a tabela CLIENTE com o nome fantasia como uma Pk.
    Este problema se tornou gigantesco devido a hoje (empresa com milhares de clientes), quando o usuário salva um cadastro de cliente com o nome fantasia errado, ele não consegue mais altera-la pois é uma PK.
    É claro que via comando update podemos alterar a Pk, porém mesmo que um job que colocarmos para por exemplo armazenar as alterações e em um horario noturno rodamos para dar update, corremos o risco de existir uma FK sendo utilizada pela aplicação e assim ocasionar o travamento do banco.
    Estou buscando alguma solução para que seja possível que o usuário possa alterar a Pk na aplicação (sei que é impossível)
    Tem alguma luz? Kk

    Obrigado

    • Carlos Alberto Lorenzi Lima Says:

      Herbert, por favor, me desculpe a demora na resposta, mas por algum problema técnico, seu comentário não estava aparecendo no Blog…. Só o vi agora!!!
      Mas, como você disse, mudar uma PK requer a mudança de toda a estrutura da Tabela no Banco de dados.
      Você pode tentar criar uma tabela auxiliar como ponte entre a PK antiga e a PK atual (Uma espécie de DE/PARA), ou usar o conceito de Surrogate Key em uma nova Tabela/Dimensão de Cliente.
      Mas, pela tempo entre a sua pergunta e essa minha resposta, você já deve ter encontrado a solução…
      Grande abraço.
      Lito


Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: