Categoria: Banco de Dados

IDs x Chaves Primárias Compostas

Objetivo

O objetivo deste artigo é discutir um pouco sobre técnicas de modelagem de bancos de dados relacionais. Mesmo com o uso constante de frameworks de mapeamento objeto/relacional (NHibernate, Entity Framework, iBatis e outros), uma boa modelagem de banco de dados continua sendo muito necessária.

Mesmo com o crescimento de abordagens de modelagem de aplicações totalmente orientadas a objetos, o uso de BD’s relacionais parece não cair em desuso. Por isso acabei investindo nesta idéia.

Existem muitas armadilhas quando definindo um modelo entidade/relacionamento e impactos relativamente fáceis de serem previstos e contornados, caso conhecidos em tempo de modelagem (depois que a aplicação está em produção, qualquer mudança é bem complexa).

Por isso essa discussão de chaves compostas x ID’s

Definindo a chave primária (Primary Key)

O conceito de chave primária é uma forma de identificar unicamente um registro numa relação (os acadêmicos gostam de chamar tabelas de relações. Daqui pra frente vou usar tabela, porque é como é comumente conhecido). Chamamos de chave primária justamente pq é a “principal”. Ela será usada em todo e qualquer relacionamento com outras entidades (chaves estrangeiras, ou foreign keys).

Existem algumas discussões sobre a melhor forma de definí-las, que basicamente encontram-se nos dois pontos:

Usar ou não usar campos da própria entidade?

Chave primária com campos da própria entidade

Geralmente tem-se a idéia de utilizar um dado do próprio modelo de negócio para identificar unicamente o registro. Um exemplo disso, seria definir uma entidade “Cliente”, como a seguir:

Nesse caso estamos definindo um campo CPFCNPJ, do tipo varchar (string) como chave primária. Ou seja, o próprio CPF ou CNPJ do cliente será definido como chave.

Geralmente essa idéia vem da pergunta. Se eu já tenho um campo que identifica unicamente o registro, pra que criar outro?

Chave primária com identificador automático (popularmente conhecido como Id)

Invés de usarmos o próprio CPF/CNPJ como chave, vamos criar um campo “burro” (Id) para servir como chave primária. Nesse caso, nada impede que definimos uma chave alternativa (alternate key ou unique index) para impedir que sejam cadastrados dois clientes com o mesmo CPF/CNPJ. No modelo, ficaríamos com a seguinte idéia:

Usar chaves compostas?

A idéia de chave composta é ter mais de um campo na chave primária. Em meios acadêmicos essa é a forma comumente apresentada nos livros “clássicos”. Eu vou expor as duas formas de resolver esse problema, e posteriormente discutiremos os benefícios e problemas gerados por cada uma delas.

O exemplo usado será um modelo simples, com as entidades cliente, pedido, item de pedido e produto, com as características:

  • Cada pedido possui um cliente. Cada cliente pode estar em um ou mais pedidos.
  • Cada pedido possui “n” itens. Aqui ocorre um relacionamento master/detail ou pai/filho entre essas tabelas.
  • Cada item de pedido possui um produto. Cada produto pode estar em um ou mais itens de pedido.

Modelo 1: Exemplo de relacionamento master/detail com chave composta

No exemplo acima, a tabela Cliente segue o conceito de usar um campo da própria entidade para identificá-la unicamente. No caso o CPF/CNPJ é ideal.

O relacionamento usado entre Cliente e Pedido aparece em muitas ferramentas como relacionamento não-identificado. É quando ele não é exportado para a chave primária da tabela destino. Nesse caso, apenas é criada uma foreign key na tabela destino para garantir que não pode entrar registro na tabela Pedido, caso o CPFCNPJ informado não exista na tabela pedido. Não faz sentido colocar o campo CPFCNPJ na tabela de pedido já que por regra de negócio eu não posso ter pedidos com código duplicado e clientes diferentes.

Já a chave primária da tabela Pedido é CodPedido. Vamos pensar no código de pedido como um número sequencial conhecido pelo usuário já que não existe outro atributo melhor na entidade pedido para fazer essa função.

Já o relacionamento entre Pedido e ItemPedido é conhecido como relacionamento identificado. É quando ele exporta para a tabela destino sua chave primária. A tabela item de pedido “ganha” o código de pedido em sua chave primária. Nesse caso faz sentido, pq eu posso ter código do item de pedido duplicado entre pedidos diferentes.

O relacionamento entre produto e item de pedido também é não-dentificado (não exporta para a chave primária). Nesse caso, propositalmente coloquei o código do produto como varchar, para identificar que é um código que o usuário conhece.

Modelo 2: Exemplo de relacionamento master/detail com Id no lugar da chave composta

O modelo acima é a mesma idéia do anterior, utilizando a idéia de Id’s e sem chaves compostas. Vamos explicar cada uma das entidades.

Na tabela cliente, usamos o campo ClienteID (identity) como um identificador único na chave primária. A idéia aqui é criar um Id interno, invisível para o usuário final, somente para administrar os relacionamentos entre as entidades. Para o usuário final, criamos uma chave alternativa (alternate key ou unique index) no campo CPFCNPJ, para impedir que sejam inseridos 2 registros com mesmo CPF ou CNPJ.

Na tabela Pedido, usamos o campo PedidoID (identity) como chave primária. É a mesma idéia da tabela cliente. O código visível para o usuário final, CodPedido fica em outro atributo, fora da chave. Deve ser criada uma chave alternativa para evitar dois pedidos com o mesmo código de usuário.

O relacionamento entre pedido e cliente é não-identificado, ou seja, não exporta para a chave primária.

A tabela ItemPedido continua sendo filha da tabela Pedido, com a diferença que ela não recebe mais o código do pedido na chave primária. Este passa a ser um relacionamento não identificado, mas não impede que se localize para qual pedido este item pertence.

A tabela de Produto segue a mesma idéia da de Cliente e Pedido. Ganha o Id e o código visível para o usuário.

Análise das duas abordagens

Para conseguirmos ter uma idéia de qual das duas abordagens gera mais benefícios ou problemas, vamos analisar alguns critérios isoladamente.

Manutenção de Códigos

Quando descrevendo o campo CPFCNPJ no modelo 1, usei a pergunta: Se eu já tenho um campo que identifica unicamente o registro, pra que criar outro? A resposta é: Para simplificar a manutenção da aplicação em produção.

Sempre que trabalhamos com códigos da própria entidade na chave primária caímos num problema de manutenção. Olhando para o modelo 1, percebemos que a tabela de Pedido ganha o CPFCNPJ do cliente. A tabela ItemPedido o código do produto e assim sucessivamente.

As vezes acontecem em 3, 4, 5 e níveis jamais imaginados quando a aplicação vai crescendo. Exemplo: se surgir uma tabela “ProgramacaoEntrega” por item de pedido, ele vai ganhar o código do pedido, do item e da programação.

Como esses códigos são “vistos” pelo usuário, eventualmente precisamos de alguma manutenção. Quando precisamos fazer esse tipo de update, vamos cair em violações de foreign key. Aí temos que dar um insert num novo registro e ir dando update nas filhas, que podem gerar impactos parecidos. Em bases com grandes volumes de dados, essas operações são bastante desgastantes.

Acredito que esse seja um ponto importante, já que hoje em dia as equipes são muito reduzidas e não existem DBA’s para ficar fazendo esse tipo de manutenção.

Existe a opção de cascade constraints (cascade updates, cascade deletes), que também eventualmente geram outros problemas como transações muito grandes (Sql Server) gerando locks enormes na base de dados ou mesmo estouro de rollback segment no Oracle.

Tipos de Dados

As vezes caímos em algumas armadilhas por causa do tipo de dados que jogamos na chave primária. No modelo 1, vamos pensar na modelagem da tabela de ProgramacaoEntrega citada acima.

Pensemos numa entidade que para cada item de pedido, permita que sejam informadas “n” datas de entrega com a respectiva quantidade naquela data.

Nesse caso, a solução “ideal” seria jogar a própria informação da entidade como chave. No caso a data da programação de entrega.

Data é um tipo complicado. Se existe um milisegundo de diferença entre duas datas, as datas são diferentes, consequentemente o modelo aceitaria duas datas iguais com hora diferente (o que não atende a regra de negócio).

No Modelo 1 proposto a solução nesse caso seria colocar um código para a programação de entrega, somente para evitar o problema com a data na chave primária.

O mesmo problema ocorre para números float na chave primária e outros.

Aumento do número de campos nas tabelas

Nesse caso, as duas abordagens tem seus problemas. É muito esquisito ver uma tabela com PedidoID e CodPedido, parece uma redundância, mas se pensarmos num como identificador de relacionamento e outro como código visto pelo usuário, a idéia de redundância desaparece.

Olhando o exemplo acima, parece que o modelo 2 aumenta o número de campos, mas em outras situações como na tabela ItemPedido (geralmente tabelas filhas, netas, bisnetas), seguindo o modelo 1 aumenta-se muito o número de campos da entidade e na chave primária.

Performance em Joins

Em alguns casos, no exemplo da tabela fictícia ProgramacaoEntrega, ter o código do pedido na chave economizaria um join em ItemPedido para pegar informações do Pedido, se pensarmos no Modelo 1.

No modelo 2, teríamos que obrigatóriamente fazer join para ItemPedido e posteriormente para Pedido.

Particularmente nunca observei problemas de performance graves usando o modelo 2. Visto que é um join direto, por chave primária o custo dessa operação não é muito grande, principalmente para as máquinas com os recursos de hoje. Geralmente as chaves primárias são clusterizadas e tem um custo baixo para esse busca.

São raros outros casos em que o modelo 1 é mais econômico que o 2 em “economia de joins”.

Desenvolvimento das aplicações – Passagem de valores

Por último, vamos sair um pouco do banco de dados e pensar nas aplicações.

A maioria dos componentes visuais em qualquer linguagem e ambiente são orientados à seleção de um único valor. Exemplo: ListBox tem um único Selected Value, um combobox tem um único selected value.

Por causa das chaves compostas (modelo 1) as vezes para conseguir usar um combo box para selecionar determinado valor, precisa-se fazer operações de “concatenar” valores e depois fazer um parse deles novamente, devido a essa “restrição”. Esse problema é inexistente no modelo 2. Para ilustrar melhor esse ponto, é só pensar num list box que seleciona um item de pedido baseado numa lista de valores que possuem pedido + item (nesse exemplo não faz sentido, mas em muitos outros faz).

Um outro ponto relacionado ao desenvolvimento das aplicações está na passagem de valores. Quando se tem chaves com valores “string”, “data” e precisa-se passar esses valores em aplicações Web, utiliza-se de QueryString ou passagem de parâmetros via Post. Nesse caso, envolve um esforço de parse para enviar e recuperar a informação, que podem gerar outros problemas como encoding (ANSI/UTF8) para strings, ou mesmo UrlEncode/UrlDecode de caracteres especiais.

Conclusão

A pergunta que não quer calar… qual é a melhor abordagem?

Como quase tudo em TI, depende. Depende da aplicação, dos padrões impostos por clientes, da equipe e de uma série de fatores.

Eu particularmente prefiro o modelo 2. Trabalho a muitos anos já modelando dessa forma e por várias vezes já fui salvo por ela, principalmente no critério “manutenção”.

Não existe certo e errado para essas questões de modelagem. Ambas abordagens permitem que as regras de normalização, eliminação de redundância e outras sejam aplicadas.

Espero que com esse artigo, mais informações possam ser colocadas nas apimentadas discussões que esses assuntos acabam gerando e os critérios expostos possam ser melhor analisados.

Conceitos de Bancos de Dados Relacionais

Objetivo

O objetivo deste artigo é explicar de forma prática os principais conceitos dos bancos de dados relacionais. Tem muitas outras coisas e detalhes sobre os conceitos expostos aqui, mas esse é apenas um ponto de partida.

Se houverem dúvidas ou sugestões, enviem seus comentários (rodapé da página).

Bancos de Dados Relacionais x Bancos de Dados Orientados a Objeto

Nunca trabalhei com um banco de dados orientado a objeto. O único que “ouvi falar” no mercado foi o tal “Caché” da InterSystems, que nunca tive oportunidade de ver operando na prática e com grande volume de transações.

Quando começaram a evoluir as técnicas de projeto orientado a objeto, principalmente por causa da adoção da UML e começaram as discussões sobre persistência de objetos (Hibernate, por exemplo), começou-se a questionar muito se os bancos de dados relacionais (que já estavam vivos a mais ou menos umas 3 décadas) estariam prontos pra essa nova onda.

O fato é que estamos em 2009 e os bancos relacionais são mais usados do que nunca. Os sistemas legados que já começaram sem padrões, processos, metodologias estão todos escritos em BD’s relacionais e a chance de alguém querer reescrever sistemas com 10, 20 anos de vida é muito baixa.

Parece que a prática de mercado ficou em usar bancos de dados relacionais e mapeamento objeto/relacional para projetos com arquiteturas mais definidas.

Essa é a principal razão de eu ainda estar motivado para escrever isso.

Exemplos de Bancos de Dados Relacionais

  • Sybase, SQL Server 2000 e 2005: São praticamente a mesma coisa. O SQL Server é quase uma “evolução” do Sybase, que teve uma parceria com a Microsoft para que surgisse o SQL Server. São muito similares. Geralmente usados por empresas que fazem questão em manter “tudo” da Microsoft, adotados por gente que evolui de bancos de dados locais (como Access). Muitas pessoas optam também pelo SQL Server pela facilidade de administração.
  • Oracle: Considerado mais “enterprise” pela maioria. Possui melhores opções de gerenciamento de crescimento dos data files que o SQL Server, mas um pouco mais complexo de administrar que o primeiro.
  • Firebird/Interbase: Praticamente a mesma coisa também. O Firebird foi originado pela abertura do código do Interbase pela Borland, o que deu uma forte popularizada entre desenvolvedores Delphi. Tem a vantagem de ser open-source e suportar uma carga quase tão grande (senão algumas vezes maior) que o próprio SQL Server. Pessoas que evitam a adoção do Firebird geralmente são pessoas que fogem de ferramentas Open Source.
  • PostgreSQL: Open source também, disponivel praticamente desde sempre junto com algumas distros de Linux. Já tive oportunidade de mexer um pouco com esse banco e gostei muito. Parece-me até um pouco mais parrudo que o Firebird, porém roda muito melhor em linux do que Windows e tem uma grande dificuldade em drivers de qualidade para outras plataformas, pelo menos na época em que tive mais contato com ele (por volta de 2004). Não sei como está hoje.
  • MySQL: Muito popular entre usuários PHP, é muito simples, muito rápido e muito pequeno. Nas primeiras versões ele tinha uma grande performance principalmente porque não fazia muita coisa (não tinha nem integridade referencial) hoje ele implementa esse e outros conceitos, porém não sei se é tão performático. Muito usado em pequenas aplicações. Também gratuíto. Não sei se a licensa dele é open-source.

Principais Conceitos de Bancos de Dados Relacionais

Tabelas ou Relações

Nos livros “acadêmicos” é comum chamar tabelas de “Relações”. Vou chamar de tabelas mesmo que é como é popularmente conhecido (já que esse artigo está muito, mas muito longe de ser acadêmico).

As tabelas são onde os dados são armazenados, dá pra comparar diretamente com uma planilha Excel, que tem colunas e linhas. Cada coluna possui o seu tipo de dados (serão detalhados posteriormente).

Geralmente para nomear tabelas usa-se palavras no singular. Exemplo: Cliente, Endereco, Pedido, ItemPedido. Não é muito bom nomear tabelas no plural, pois principalmente em nomes compostos, gera-se uma confusão enorme. Ex.: ItensPedido ou ItensPedidos?

Chave Primária (Primary Key)

É a principal forma (não a única) de se localizar dados numa tabela. Vamos pensar numa tabela de “Cliente” que possua as colunas Nome, Telefone, RazaoSocial. Podemos identificar o cliente unicamente através de um campo Codigo, ClienteID ou mesmo CPF/CPNJ (apesar de eu não gostar).

Não vou discutir aqui técnicas sobre como modelar uma tabela/chave primária. Isso fica mais pra frente.

Chave alternativa (Alternate Key, Unique Index)

É uma forma secundária de se localizar unicamente um registro. Ex.: Nossa tabela Cliente tem como chave primária um campo ClienteID, poderíamos definir uma chave alternativa como CPF/CNPJ, além de não podermos duplicar CPF e CNPJ, é uma outra forma de localizar um cliente.

A maioria dos bancos de dados relacionais implementa esse conceito através de “UNIQUE” indexes (create unique index), porém, é muito comum ver nas ferramentas de modelagem (ERWin, Embarcadero ERStudio) o termo “alternate key”.

Chave estrangeira (Foreign Key) / Integridade Referencial

Integridade referencial é um conceito que determina que para que um determinado dado exista numa tabela, deve haver um correspondente em outra. Ex.: Para que possa ser inserido um ClienteID na tabela Pedido, é necessário que exista um registro com o ClienteID informado na tabela Cliente.

Esse conceito é implementado através de chaves estrangeiras (foreign keys). As chaves estrangeiras nada mais são do que essas definições.

É muito importante definir as chaves estrangeiras no modelo de dados, é uma forma de tornar o banco íntegro e evitar uma série de falhas nas aplicações que acessam esse banco. É por aqui que algumas consistências simples entram no modelo, por exemplo:

  • Faz sentido um pedido para um cliente que não existe? Aqui cabe uma FK.
  • Faz sentido um registro na movimentação de conta corrente para uma conta que não existe? Aqui cabe outra FK.
  • Faz sentido um endereço para um cliente que não existe?
  • E assim sucessivamente.

Check Constraint

Alguns bancos implementam esse conceito. É uma forma de determinar quais são as informações possíveis numa coluna da tabela. Ex.: Tem um cliente com um campo Status, char(1). Nesse campo somente podem entrar os valores ‘A’ (ativo) e ‘I’ (inativo). É pela check constraint que impede-se de qualquer outra informação entrar nessa coluna.

Trigger

A maioria dos bancos de dados relacionais hoje implementam uma linguagem de programação no banco de dados. No caso do Oracle, o PL/SQL, do SQL server, o Transact SQL e outras.

A Trigger é uma forma de se escrever um código no banco de dados que execute de acordo com um evento. Ex.: Sempre que inserir um registro na tabela cliente, executa uma trigger. Sempre que excluir um registro de uma tabela, executa outra trigger.

Como usar uma trigger é uma discussão à parte que merece um artigo só pra ela. Não é muito difícil cair em armadilhas ao se criar triggers. Futuramente pretendo escrever um artigo só sobre isso.

Stored Procedure (ou somente Procedure)

É um pedaço de código, um programa que executa do lado do banco de dados (consumindo os recursos de processamento e memória do banco de dados).

Há defensores das procedures e gente que não quer elas de jeito nenhum em seus projetos. Essa é uma discussão muito mais complexa.

Geralmente, usa-se procedures por questões de performance, ou seja, invés de trazer todos os dados para a aplicação e processar lá, faz-se tudo no banco. Nem sempre isso é performatico. Existe uma série de poréns. O principal é saber qual é o conceito neste momento.

User Defined Functions (UDF’s) ou somente Functions

São funções, escritas na linguagem de programação dos bancos de dados, que geralmente permitem que as mesmas sejam usadas direto de statements SQL.

Ex.: Cria-se uma função HoraUtil, que recebe um datetime como parâmetro e de dentro da query, pode-se fazer chamadas a elas, exemplo:

select
  Data,
  HoraUtil(DataHora)
from
  MinhaTabela

Em alguns casos, UDF’s economizam muito, mas muito código redundante em queries, porém, também podem causar vários problemas de performance.

View

View é um recurso que permite que determinada expressão SQL seja armazenada no banco de dados e novas consultas podem ser feitas sobre ela.

É uma forma principalmente de “restringir” ou criar visões mais complexas de tabelas (inclusive joins) e permitir consultas futuras. Ex.: Imagine uma tabela cliente. Pode-se derivar dessa tabela uma view “ClientesAtivos”, de forma que a view liste somente os clientes ativos.

Geralmente é bom usar Views por questões de administração de banco. Pela aplicação geralmente é uma perda desnecessária de performance (pois pode gerar planos de execução de queries bem complexos).

Auto-Relacionamentos em Bancos de Dados Relacionais

A idéia dos relacionamentos está em criar referências entre duas ou mais tabelas. Quando uma tabela tem uma foreign key (FK) para outra, existe um relacionamento entre as tabelas. Ex.: Relacionamento entre a tabela Pedido e ItemPedido, de forma a criar o conceito de integridade referencial entre elas.

Algumas vezes as pessoas se confundem quando aparece um relacionamento para a própria tabela (auto-relacionamento), mas segue o mesmo conceito de relacionamento entre duas tabelas. Por exemplo:

  • Tabela Empresa com os campos
    • EmpresaID (int, not null)
    • RazaoSocial (varchar(100) not null)
    • NomeFantasia (varchar(50) not null)
    • EmpresaPaiID (int, null)
  • FK Definida de EmpresaPaiID para EmpresaID.

Sempre que eu vou inserir um registro numa tabela que tem uma FK, a FK só é validada caso todos os elementos que compõem a FK (no caso dela ser composta) tenham valores diferentes de nulo. No exemplo acima, se eu inserir um registro nessa tabela sem EmpresaPaiID, a FK não vai ser validada.

Caso exista valor, o auto-relacionamento indica que o ID informado em EmpresaPaiID deve existir na tabela Empresa, campo EmpresaID (conforme definido na FK). Na prática, implementa a regra “só podem ser cadastradas empresas filhas caso a pai esteja cadastrada).

Mas no final das contas, pra que serve um auto-relacionamento? A resposta mais simples é, principalmente para criar estruturas hierárquicas na base de dados. Usando o mesmo exemplo acima, para representar uma estrutura:

  • EmpresaID1, “Teste”, “Teste”
    • EmpresaID2, “Teste2”, “Teste2”
      • EmpresaID3, “Teste3”, “Teste3”

A forma mais elegante de representar essa estrutura, empresa 3 filha da empresa 2 que é filha da 1, seria com auto-relacionamento na tabela empresa e os dados representados da seguinte forma:

EmpresaID RazaoSocial NomeFantasia EmpresaPaiID
1 Teste Teste null
2 Teste2 Teste2 1
3 Teste3 Teste3 2