Sessão nostalgia: formas normais
Sessão nostalgia: formas normais
Na minha época e CEP, a "porta de entrada" para a profissión de TI era trabalhar com ERPs. Nem se chamavam assim na época; eram apenas "sistemas empresariais", geralmente desenvolvidos em uma linguagem de 4ª geração obscura vinculada a um banco de dados obscuro. Falo muito sobre isso neste texto.
Uma vez que bancos de dados relacionais eram a base de todo o nosso trabalho, os programas de treinamento das empresas e das ferramentas de desenvolvimento eram surpreendentemente bons ao abordar a questão das "formas normais" ou "normalização" de dados.
Era algo que, tipo, todo mundo e a mãe dele sabia. Era o nosso Evangelho. Era cobrado em entrevistas de emprego. Era motivo de piada, ou parafraseado como piada. É até estranho que hoje em dia seja algo desconhecido de tantos desenvolvedores.
Na verdade, as formas normais não têm nada de mais. Conforme resumiu um colega de trabalho mais velho, da minha época de programador mal-pago de ERP, elas são apenas bom senso. Um desenvolvedor ou DBA com bom senso produziria um banco de dados normalizado, pelo menos até o terceiro nível (3NF para os íntimos) mesmo que nunca tenha ouvido falar de formas normais.
Para ilustrar as formas normais, e mostrar essa maravilha à geração Z, vou usar o mesmo exemplo da época: clientes e pedidos.
| # Cliente | Nome Cliente | # Pedido | # Produto | Nome Produto |
|---|---|---|---|---|
| 1 | Merposa S/A | 123 | 54,98 | Detergente, Sabão |
| 77 | Óleo | |||
| 2 | Lavou Desfiou Confecções | 125 | 54 | Detergente |
| 2 | Lavou Desfiou Confecções | 125 | 69 | Shampoo |
| 3 | Meu Ovo Verdureira |
A tabela acima está na forma normal "nula", UNF ou 0NF. A organização dos dados tem diversos problemas óbvios. Os mais prementes são:
Um defeito que a tabela acima não tem é duplicação de dados. Se, por exemplo, um mesmo produto aparecesse duas vezes no mesmo pedido, ou houvesse linhas com todas as colunas vazias, os dados seriam imprestáveis e não haveria como normalizá-los.
A primeira forma normal prescreve que cada célula (linha x coluna) possui apenas um valor, e a tabela não depende de uma ordem em especial para fazer sentido.
O exemplo abaixo possui os mesmos dados que o anterior, porém está 1NF:
| # Cliente* | Nome Cliente* | # Pedido* | # Produto | Nome Produto |
|---|---|---|---|---|
| 1 | Merposa S/A | 123 | 54 | Detergente |
| 1 | Merposa S/A | 123 | 98 | Sabão |
| 1 | Merposa S/A | 123 | 77 | Óleo |
| 2 | Lavou Desfiou Confecções | 125 | 54 | Detergente |
| 2 | Lavou Desfiou Confecções | 125 | 69 | Shampoo |
| 3 | Meu Ovo Verdureira |
Aqui nós já temos um arremedo de organização. A tabela acima já se parece com uma "view", ou seja, o resultado de uma consulta SQL que junta três tabelas normalizadas (cliente, pedido e detalhes do pedido).
O que significa a última linha, com as colunas do cliente preenchidas, porém as colunas de pedido nulas? Neste exemplo, estamos presumindo que todos os dados do negócio estão nesta tabela, então um cliente sem pedidos teria de ser representado desse jeito.
Se quiséssemos representar um pedido vazio (sem produtos), teríamos de recorrer a um expediente semelhante, com cliente e número do pedido preenchidos, e produto nulo.
Podemos agora atribuir uma chave primária, ou seja, um valor ou conjunto de valores que identifica cada linha, e ocorre no máximo uma vez na tabela. Seria a tupla '# Cliente' + '# Pedido' + '# Produto', que marcamos com asteriscos no cabeçalho da tabela. (Estamos é claro presumindo que um produto não possa constar mais de uma vez num mesmo pedido.)
A propósito, uma chave primária composta por mais de uma coluna é chamada de superchave na literatura.
O problema mais óbvio agora é a repetição do nome do cliente em cada linha de detalhe de pedido. Se a razão social de um cliente tivesse de ser atualizada nesse banco de dados, a operação afetaria inúmeras linhas, em vez de apenas uma, como seria o razoável.
A segunda forma normal exige que cada coluna que não faça parte da chave primária deve depender da totalidade da chave primária.
Como dissemos antes, a repetição do nome do cliente não parece boa. Falando em termos mais precisos, a coluna 'Nome Cliente' depende apenas do código do cliente, não da chave primária inteira; portanto o lugar desta coluna é em outra tabela.
Sendo assim, teremos duas tabelas agora: Clientes e Pedidos.
| # Cliente* | Nome Cliente |
|---|---|
| 1 | Merposa S/A |
| 2 | Lavou Desfiou Confecções |
| 3 | Meu Ovo Verdureira |
| # Cliente* | # Pedido* | # Produto* | Nome Produto |
|---|---|---|---|
| 1 | 123 | 54 | Detergente |
| 1 | 123 | 98 | Sabão |
| 1 | 123 | 77 | Óleo |
| 2 | 125 | 54 | Detergente |
| 2 | 125 | 69 | Shampoo |
A normalização dos dados para 2NF também resolveu o problema de como representar clientes sem pedidos sem recorrer a linhas parcialmente nulas. E sempre podemos "desnormalizar" nossos dados de 2NF para 1NF recorrendo a uma view, algo como
select clientes.*, pedidos.* from clientes left outer join pedidos on clientes.codigo = pedidos.cliente
A query acima lista o cliente 3 com pedido nulo. Se quisermos uma view contendo apenas clientes com pedidos, basta trocar LEFT OUTER JOIN por INNER JOIN.
Ainda no contexto da 2NF, temos de considerar se o número do pedido depende ou não do código do cliente. Isso depende das regras de negócio.
Pode até ser que alguma empresa numere seus pedidos individualmente por cliente (caso em que pode haver inúmeros pedidos #123, um por cliente) porém o mais típico é que o número do pedido seja sequencial crescente (só pode haver um pedido #123 na empresa inteira).
Neste caso, a chave primária da tabela de pedidos não é mais a tupla '# Cliente' + '# Pedido' + '# Produto', mas apenas '# Pedido' + '# Produto'.
O código do cliente não faz mais parte da chave, e depende apenas de '# Pedido', não da totalidade da chave primária. Então também tem de ser deslocado para outra tabela:
| # Cliente* | Nome Cliente |
|---|---|
| 1 | Merposa S/A |
| 2 | Lavou Desfiou Confecções |
| 3 | Meu Ovo Verdureira |
| # Cliente | # Pedido* |
|---|---|
| 1 | 123 |
| 2 | 125 |
| # Pedido* | # Produto* | Nome Produto |
|---|---|---|
| 123 | 54 | Detergente |
| 123 | 98 | Sabão |
| 123 | 77 | Óleo |
| 125 | 54 | Detergente |
| 125 | 69 | Shampoo |
O esquema está começando a parecer civilizado.
A vantagem de haver uma tabela de pedidos, em separado dos detalhes do pedido, é que quaisquer dados auxiliares do pedido (data, endereço de entrega, condições de pagamento, etc.) que se aplicam ao pedido inteiro, podem ser alojados nessa tabela sem violar a 2NF.
Mas ainda existe um problema óbvio na tabela de detalhes do pedido: o nome do produto é repetido em diferentes linhas.
A terceira forma normal exige que colunas que não sejam chaves não possuam relação de dependência com outras colunas que também não sejam chaves.
Na minha opinião, 3NF e BCNF são a mesma coisa. Ou melhor: BCNF é o que a maioria das pessoas entende como 3NF.
Na tabela de detalhes do pedido, a chave primária é a tupla '# Pedido' + '# Produto'. O nome do produto depende apenas do código do produto, que não é (sozinho) uma chave primária. Para que os dados estejam 3NF/BCNF, precisamos resolver isto deslocando o nome do produto para outra tabela.
| # Cliente* | Nome Cliente |
|---|---|
| 1 | Merposa S/A |
| 2 | Lavou Desfiou Confecções |
| 3 | Meu Ovo Verdureira |
| # Cliente | # Pedido* |
|---|---|
| 1 | 123 |
| 2 | 125 |
| # Pedido* | # Produto* |
|---|---|
| 123 | 54 |
| 123 | 98 |
| 123 | 77 |
| 125 | 54 |
| 125 | 69 |
| # Produto* | Nome Produto |
|---|---|
| 54 | Detergente |
| 98 | Sabão |
| 77 | Óleo |
| 69 | Shampoo |
Note que a normalização acima baseou-se no fato que o código de produto não era, sozinho, uma chave primária. Aqui reside a diferença entre 3NF "baunilha" e a BCNF.
Vamos dar um passo atrás e revisitar a tabela de pedidos 2NF:
| # Pedido* | # Produto* | Nome Produto |
|---|---|---|
| 123 | 54 | Detergente |
Pela 3NF "baunilha", esta tabela já estaria normalizada, porque o nome do produto depende da chave primária composta '# Pedido' + '# Produto'. A 3NF "baunilha" não toma conhecimento que o nome do produto depende apenas de uma parte da chave.
Já a BCNF considera a coluna '# Produto' sob dois pontos de vista: como parte da chave primária, e como valor dependente da chave primária. Isto nos permite detectar o problema: o nome do produto tem dois senhores, porque ele depende da chave primária, mas também depende de outra coluna que não é chave primária.
O problema todo começa quando usamos colunas de informação como parte de chaves compostas. No exemplo acima, a diferença entre 3NF e BCNF emerge porque usamos uma chave composta, e só estamos usando '# Produto' como parte da chave composta porque presumimos que um pedido só pode listar cada produto uma única vez.
Suponha agora que a regra de negócio permita que um produto apareça múltiplas vezes no mesmo pedido. A tabela de pedidos 2NF ficaria assim:
| # Pedido* | # Sequencial* | # Produto | Nome Produto |
|---|---|---|---|
| 123 | 1 | 54 | Detergente |
| 123 | 2 | 54 | Detergente |
Introduzimos uma coluna adicional '# Sequencial' para identificar cada linha do pedido. Agora, o código do produto não faz mais parte da chave primária, ele é uma coluna dependente da chave composta '# Pedido' + '# Sequencial'.
Nesta configuração, a 3NF é suficiente para detectar o problema, pois agora o código do produto não é chave nem parte de uma chave, e a dependência entre nome de produto e código de produto viola tanto a BCNF quanto a 3NF.
A 3NF não trata apenas de dependências relacionais, mas também funcionais. Por exemplo, suponha as colunas X e Y, onde Y é função de X. Essa função pode ser uma operação matemática, uma manipulação de string, o que for. Pela terceira forma normal, Y é redundadnte e deve ser eliminada, e calculada sob demanda quando necessária.
Por questões de performance ou de rastreabilidade, nem sempre podemos seguir esta regra à risca.
Como dito antes, a segregação dos dados originais nestas quatro tabelas foi basicamente um exercício de senso comum. O que as formas normais fazem é codificar o senso comum, lhe dão uma base científica.
As formas normais são cumulativas. Por exemplo, para um banco de dados ser considerado 3NF, ele deve cumprir todas as condições da UNF, 1NF e 2NF.
As formas normais superiores à 3NF/BCNF são menos conhecidas, e seu uso é considerado restrito, seja pela relativa raridade das situações que elas tratam, seja pelo impacto que elas podem causar na performance do banco.
Esta forma normal visa eliminar "mini explosões combinatoriais". Nosso exemplo de pedidos de clientes já está na 4NF, então vamos ter de fabricar uma violação para explicá-la.
Imagine a seguinte tabela, que relaciona clientes a endereços de entrega e condições de pagamento.
| Cliente | Endereço de entrega | Condição de pagamento |
|---|
Peço perdão por não me dar ao trabalho de preenchê-la com alguns dados de exemplo.
A pergunta é a seguinte: existe alguma relação entre endereço de entrega e condição de pagamento? Na política da sua empresa, existe o caso de um cliente poder pagar em 30/60/90 apenas quando a entrega for feita no endereço XPTO?
Se sim, a tabela acima está ok. Se não, haverá uma pequena explosão combinatorial por cliente. Se o cliente possui 3 endereços de entrega e pode optar dentre 4 condições de pagamento, a tabela teria de ter 12 linhas por cliente para expressar todas as possibilidades.
Apesar de ser um problema óbvio de modelagem, ele não é detectado pelas formas normais até 3NF. A quarta forma normal veda "dependências multivaloradas independentes entre si".
Antes de prosseguir à quinta forma normal, preciso introduzir o conceito de relacionamentos muitos-para-muitos, para o caso de não ser familiar ao leitor.
A relação entre clientes e pedidos é uma relação um-para-muitos (1:n), porque um cliente pode estar relacionado a múltiplos pedidos, mas cada pedido é relacionado a apenas um cliente. Esta relação não precisa de nenhuma tabela além das citadas (clientes e pedidos), basta fazer um JOIN entre elas.
Já a relação entre pedidos e produtos é muitos-para-muitos (n:n) pois cada pedido pode conter muitos produtos, e cada produto pode constar em inúmeros pedidos. A relação n:n é potencialmente uma explosão combinatorial, mas geralmente não o é. Nem todo pedido lista todos os produtos possíveis, e é certo que nenhum produto consta em todos os pedidos.
O mapa das relações realmente existentes tem de ser armazenado em alguma tabela, que no nosso caso é a de detalhes do pedido. Não podemos fazer o JOIN diretamente entre pedidos e produtos; a junção entre elas tem de ser intermediada pela tabela de detalhes do pedido.
Esta forma normal procura detectar relacionamentos muitos-para-muitos (n:n) que poderiam ser decompostos em versões mais simples.
Considere a tabela abaixo, que armazena uma relação n:n:n entre clientes, vendedores e produtos:
| Cliente | Vendedor | Produto |
|---|
Esta tabela codifica relações de exclusividade entre vendedores, clientes e produtos. Por exemplo, apenas o vendedor João pode vender detergente para a empresa Merposa. A pergunta é a seguinte: esta relação tríplice poderia ser decomposta em três tabelas menores?
| Cliente | Vendedor |
|---|
| Cliente | Produto |
|---|
| Vendedor | Produto |
|---|
Nesta versão, temos três relações n:n. Se pudermos expressar as relações desta forma, isto significa que a tabela original tríplice não estava na quinta forma normal e deve ser decomposta.
Se a tabela tríplice pode ser decomposta ou não, vai depender das regras de negócio da empresa. Em geral, pode. Um cliente pode "pertencer" a um vendedor, ou um vendedor pode ter exclusividade na venda de um produto, mas dificilmente haverá uma regra de negócio que implique as três coisas ao mesmo tempo.
Como se pode ver, a 5NF já é bem mais obscura a difícil de exemplificar que as anteriores. Violações da 5NF são raras, mesmo quando o arquiteto do banco de dados não tenha atentado para esta forma normal.
A sexta forma normal é relativamente simples: cada tabela só pode possuir uma coluna além da chave primária.
Por exemplo, uma tabela de clientes típica tem dezenas de colunas: razão social, endereço, telefone, cidade, UF, etc. etc. além é claro da chave primária que pode ser um código numérico ou o CNPJ (má ideia, mas não é incomum).
Para adequar-se à 6NF, essa tabela teria de ser desmembrada em dezenas: uma com o código e a razão social, outra com o código e o endereço, outra com o código e o telefone...
Mas por que alguém faria isto?!
Uma possível utilidade é quando se usa versionamento de linhas. Neste caso, armazenamos todas as versões passadas de cada linha da tabela; a chave primária convencional é composta com um timestamp. Para obter a linha mais atual, buscamos pelo timestamp mais recente.
Numa tabela versionada que tenha muitas colunas, haverá muita repetição de informação entre versões, e não fica imediatamente claro qual coluna foi alterada entre uma versão e outra.
Se a tabela está na 6NF, essa repetição é evitada. No exemplo da tabela de clientes, se apenas o telefone foi alterado, apenas a tabela de telefones de clientes ganha uma linha nova. Além da economia de espaço, temos um registro histórico preciso do que foi alterado, e quando.
Desvantagens: consultar os dados do cliente para e.g. emitir uma nota fiscal implica em fazer JOIN com inúmeras tabelas. Além de inconveniente, pode causar problemas de performance. Por conta disso, esta forma normal é raramente usada.
Em vez de adotar a 6NF, podemos simplesmente usar um banco de dados que faça o versionamento automático de linhas.
As formas normais visam basicamente a) diminuir a redundância de dados, o que reduz o consumo de disco; e b) e otimizar operações de escrita, reduzindo o número de linhas afetadas quando a informação precisa ser atualizada.
As formas normais presumem que o banco de dados relacional é rápido na hora de fazer JOINs de tabelas, e que a velocidade de leitura é sempre a mesma, independente do nível de normalização do banco de dados. Nem sempre isto é verdadeiro.
Um modo muito comum de melhorar tanto a performance de leitura quanto a conveniência (ou seja, usando-se consultas SQL menos complicadas) é violar de propósito alguma forma normal. Isto é chamado de "denormalização". Alguns exemplos:
E é claro, NoSQL é uma realidade. Dados em bancos não-relacionais provavelmente não vão se encaixar nas formas normais, que foram pensadas para bancos relacionais.
Sou de uma época onde havia verdadeira obsessão por economia de disco. Era o recurso escasso da época. Hoje em dia, a pressão por economia de disco é muito menor. Bancos de dados empresariais são até comedidos em armazenamento, comparativamente a outras aplicações. Eu trabalhava numa malharia de 2.500 funcionários, o banco de dados possuia uns 600MB. Se continuasse a existir, teria uns 6GB hoje. Enquanto isso, as câmeras de segurança da minha casa geram 10GB por dia.
Citamos antes as chaves primárias. A chave primária identifica as linhas de uma tabela. Pense no que significa "identidade". Significa que um valor de chave primária ocorre no máximo uma vez na tabela, e todas as demais informações da linha dependem dela. Uma chave primária não pode ser nula.
Chaves primárias têm de ser bem escolhidas, porque elas são a espinha dorsal do banco de dados. Na dúvida, a chave primária deve ser um número sequencial crescente. Sempre existe a tentação de usar alguma informação do mundo real que parece única. Por exemplo, o CNPJ de cliente.
Por que o CNPJ não é uma boa chave primária? Porque nem todo cliente possui CNPJ. Por exemplo, clientes estrangeiros. Um truque de época era colocar o CNPJ do despachante aduaneiro. Funcionava até o mesmo despachante representar mais outro cliente do exterior. (Nem vou entrar no mérito de armazenar CNPJs como números inteiros, em vez de strings. Agora que o CNPJ virou alfanumérico, deve estar havendo muito choro e ranger de dentes no mundo ERP.)
A chave estrangeira é uma chave primária de outra tabela. Por exemplo, na tabela de pedidos, o código do cliente é uma chave estrangeira. Em geral, é através delas que as tabelas constroem relacionamentos.
O CNPJ de cliente é algo que eu chamo de "chave comum" ou "chave parcial". Não é uma chave primária, mas tem características de chave. Veja que nem todo cliente tem CNPJ, mas não pode haver dois registros de cliente com o mesmo CNPJ. O sistema tem de impedir que sejam cadastrados dois clientes com o mesmo CNPJ, o que significa que a busca de cliente por CNPJ tem de ser rápida.
Em nossa obsessão por performance em computadores fracos, como pouco disco e pouca memória, muitas vezes confundíamos chaves com índices.
Índices são estruturas de dados, classicamente baseadas em B-Trees, que aceleram a busca por um elemento. Ao buscar uma palavra num dicionário de 100.000 palavras, teremos de ler 100.000 palavras se procuramos sequencialmente. Ou, pelo fato do dicionário estar em ordem alfabética, podemos achá-la em apenas 17 leituras (logaritmo de 100 mil na base 2).
Toda chave primária tem índice, para que os JOINs sejam rápidos. Mas pode ser necessário indexar colunas que não são chaves. E não necessariamente todo índice é uma boa chave. O CNPJ de cliente deve ser um índice, pois numa empresa é comum consultar a base de clientes pelo CNPJ. Existem tipos de índices mais adequados a textos, e.g. o nome do cliente, que é interessante poder consultar por um fragmento. Nem por isso CNPJ ou nome de cliente fazem boas chaves primárias.
É claro, se o negócio atende exclusivamente empresas e o mercado interno, CNPJ é sim uma chave candidata. Na literatura, chave candidata é uma coluna que reúne as condições para ser uma chave primária. Mesmo assim não é recomendável, porque empresas crescem e mudam.
Seja como for, índices e chaves ocupavam espaços muitos próximos dentro das nossas cabeças. Como os índices também têm um custo de disco e processamento, procurávamos criar índices baseados em chaves compostas, tentando obter o melhor de dois mundos.
No mestrado, aprendi que, na ciência da computação, ninguém liga muito para índices. Na cabeça dos "pais fundadores" dos bancos relacionais, índices são detalhes de implementação; oportunidades de otimização que um banco de dados decente acharia sozinho.
Na ciência relacional, a preocupação é com a cardinalidade dos valores em cada coluna. Por exemplo, o CNPJ de cliente é um valor de alta cardinalidade, pois quando existe, é único por cliente.
O CEP do cliente tem média cardinalidade; tem alguma repetição, mas ainda particiona a tabela em diversos pedaços de tamanho pequeno e parecido. A cidade tem cardinalidade média-baixa, pois algumas partições ficam muito maiores que outras (e.g. clientes de São Paulo/SP x clientes de Iomerê/SC).
Já uma coluna como "Cliente Ativo" que admite apenas dois valores ("Sim" ou "Não"), ou o gênero (M/F) de um cliente pessoa física, possuem baixíssima cardinalidade. Uma coluna como "Suframa (S/N)" é pior ainda, pois além de ter baixa cardinalidade, tende a particionar a tabela em dois pedaços muito diferentes. Uma empresa geralmente terá muito mais clientes fora da Zona Franca de Manaus do que dentro.
Um bom banco de dados procura detectar a cardinalidade das colunas, pois numa consulta SQL o filtro sobre uma coluna de alta cardinalidade é muito mais eficiente. Uma coluna de alta cardinalidade, poucas linhas nulas e utilizada como filtro em consultas SQL é forte candidata a um índice.
No meu tempo, os bancos de dados não faziam isso, ou tentavam fazer e erravam. Então tínhamos de usar algum truque para "convencer" o banco de dados a sempre filtrar primeiro pela coluna de cardinalidade maior. Isso presumindo que o ser humaninho conhecesse bem a cardinalidade das diversas colunas. Em geral conhecíamos, mas também errávamos.
Colunas com alta cardinalidade porém com uns poucos valores muito repetidos são "encrenca" para índices. Exemplo: data de pagamento de uma conta. Tem cardinalidade boa para contas já pagas, mas todas as contas não-pagas possuem data de pagamento vazia. Filtrar contas já pagas pela data de pagamento é eficiente, mas filtrar contas não-pagas pela data de pagamento obviamente não tem alta cardinalidade.
Bancos mais simples, que usam o algoritmo B-Tree tal qual ele é explicado em livros didáticos, podem ficar muito lentos quando indexam uma coluna desse tipo. (Pergunte como eu sei...) Um truque que usávamos era criar um índice composto com uma segunda coluna de alta cardinalidade, que não era realmente usada nas buscas, era apenas para fazer o índice funcionar bem. Bancos mais modernos já não têm este problema.
E hoje em dia, a ênfase em índices vai sendo diluída pelos bancos NoSQL. Por exemplo, em bancos de dados como Apache Hive simplesmente não se fala de índices. A única otimização "visível", que temos de escolher explicitamente, é a partição do banco em arquivos, segundo o valor de uma ou duas colunas.
Cada arquivo ("parquet") possui estruturas internas que são menos precisas que índices convencionais, porém aceleram as consultas quase tão bem quanto. E a melhor parte é que está tudo lá, é detalhe de implementação, é só usar.