Olá! Meu nome é Andrei Semenov, sou analista sênior na Sportmaster. Neste post, quero levantar a questão da desnormalização de bancos de dados de sistemas ERP. Vamos considerar as condições gerais, bem como um exemplo específico — digamos que será uma maravilhosa taverna monopolista para piratas e marinheiros. Onde é necessário atender piratas e marinheiros de maneiras diferentes, uma vez que suas concepções estéticas e padrões de consumo diferem significativamente.
Como fazer todos ficarem satisfeitos? Como não enlouquecer projetando e mantendo um sistema assim? O que fazer se na taverna começarem a chegar não apenas os habituais piratas e marinheiros?
Tudo em ordem. Mas vamos por partes.
1. Limitações e suposições
Tudo o que foi exposto refere-se apenas a bancos de dados relacionais. As consequências da desnormalização, bem como anomalias de modificação, exclusão e inserção, que estão bem documentadas, incluindo na Internet, não são abordadas. Ficam fora da publicação os casos em que a desnormalização é uma prática comum, com exemplos clássicos: série e número de passaporte, data e hora, entre outros.
O post utiliza definições claramente compreensíveis e aplicáveis das formas normais, sem referenciar termos matemáticos. Na forma como podem ser aplicadas à análise de processos de negócios reais e ao projeto de software industrial.
Há uma opinião de que o projeto de data warehouses, ferramentas para criação de relatórios e acordos de integração (que utilizam representação tabular da informação), difere do projeto de bancos de dados de sistemas ERP na medida em que a conveniência de uso e a aplicação de desnormalizações conscientes podem ter prioridade sobre a proteção da integridade dos dados. Eu compartilho essa opinião, e o que será descrito a seguir refere-se exclusivamente a modelos de dados mestres e dados transacionais de sistemas ERP.
A explicação das formas normais é apresentada com um exemplo compreensível para a maioria dos leitores. No entanto, como uma ilustração clara, nos itens 4-5 foi deliberadamente utilizada uma tarefa acentuadamente "inventada". Se isso não for feito e um exemplo clássico for utilizado, como o mesmo modelo de armazenamento de pedidos do item 2, pode-se chegar à situação em que a atenção do leitor é desviado da decomposição do processo em um modelo, para a experiência pessoal e percepção de como os processos e modelos de armazenamento de dados em sistemas de informação devem ser construídos. Em outras palavras, pegue dois analistas de TI qualificados, um que presta serviços a logísticos que transportam passageiros, e outro a logísticos que transportam máquinas para a produção de microchips. Peça a eles, sem discutir previamente os processos de negócios que podem ser automatizados, para criar um modelo de dados para armazenar informações sobre um trajeto ferroviário.
Há uma probabilidade não nula de que nos modelos propostos você encontre não apenas um conjunto de atributos visivelmente diferente, mas também conjuntos de entidades que não coincidem, porque cada analista se baseará nos processos e tarefas com os quais está acostumado. E, em tal situação, dizer qual modelo é "correto" é impossível, pois não há critério de avaliação.
2. Formas Normais
A primeira forma normal do BD exige a atomicidade de todos os atributos.
Em particular, se um objeto A possui atributos não-chave a e b, de modo que c=f(a,b) e na tabela que descreve o objeto A você armazena o valor do atributo c, então a primeira forma normal está violada no BD. Por exemplo, se na especificação do pedido é indicado a quantidade, cujas unidades de medida dependem do tipo de produto: em um caso podem ser peças, no outro litros, e em um terceiro embalagens compostas por peças (no modelo acima Good_count_WR), então a atomicidade dos atributos é violada no BD. Neste caso, para dizer como deve ser o conjunto de tabelas na especificação do pedido, é necessário uma descrição alvo do processo de trabalho no sistema de informação, e como os processos podem ser diferentes, também podem existir muitas versões "corretas".
A segunda forma normal do BD exige a conformidade com a primeira forma e uma tabela própria para cada entidade relacionada ao processo de trabalho em SI. Se em uma tabela existem dependências com=f1(a) e d=f2(b) e não há dependência com=f3(b), então a tabela viola a segunda forma normal. No exemplo acima, na tabela "Pedido", não há dependência entre o pedido e o endereço. Altere o nome da rua ou da cidade, e isso não terá nenhum impacto nos atributos essenciais do pedido.
Terceira forma normal do BD exige a conformidade com a segunda forma normal e a ausência de dependências funcionais entre atributos de diferentes entidades. Esta regra pode ser formulada assim: "tudo que pode ser calculado deve ser calculado". Em outras palavras, se existem dois objetos A e B. Na tabela que armazena os atributos do objeto A, é manifestado o atributo C, e o objeto B tem um atributo b tal que existe c=f4(b), então a terceira forma normal é violada. No exemplo abaixo, o atributo "Quantidade de itens" (Total_count_WR) no registro do pedido claramente tende a violar a terceira forma normal.
3. Minha abordagem para a aplicação da normalização
1. Apenas o processo de negócios automatizável direcionado pode fornecer ao analista critérios para identificar entidades e atributos ao criar o modelo de armazenamento de dados. A criação de um modelo de processo é uma condição indispensável para a criação de um modelo de dados normal.
2. Alcançar a terceira forma normal em sentido estrito pode não ser viável na prática real de criação de sistemas ERP, ao cumprir parte ou todas as seguintes condições:
- processos automatizados raramente estão sujeitos a mudanças,
- os prazos para pesquisa e desenvolvimento estão apertados,
- os requisitos de integridade dos dados são condicionalmente baixos (erros potenciais em software industrial não levam a perdas financeiras ou de clientes para o fornecedor de software)
- etc.
Nas condições descritas, os custos para identificar e descrever o ciclo de vida de alguns objetos e seus atributos podem não ser justificados do ponto de vista da eficiência econômica.
3. Quaisquer consequências da desnormalização do modelo de dados em um SI já criado podem ser contidas por meio de uma pesquisa cuidadosa do código e testes.
4. A desnormalização é uma maneira de transferir o esforço de pesquisa das fontes de dados e o projeto do processo de negócios para a fase de desenvolvimento, do período de implementação para o período de evolução do sistema.
5. É desejável buscar a terceira forma normal do BD se:
- A direção da mudança dos processos de negócios automatizados é difícil de prever
- Dentro da equipe de implementação e/ou desenvolvimento, existe uma divisão de trabalho pouco permeável
- Os sistemas que fazem parte do contorno de integração se desenvolvem segundo seus próprios planos
- A incoerência de dados pode levar à perda de clientes ou dinheiro pela empresa
6. O projeto do modelo de dados deve ser realizado pelo analista apenas em relação aos modelos do processo de negócios-alvo e do processo no SI. Se o desenvolvedor for responsável pelo projeto do modelo de dados, ele terá que se aprofundar na área temática a ponto de, em particular, entender a diferença entre os valores dos atributos — uma condição necessária para destacar atributos atômicos. Assim, ele assume funções não inerentes.
4 Tarefa para ilustração
Suponha que você tenha uma pequena taverna robotizada no porto. Seu segmento de mercado: marinheiros e piratas que chegam ao porto e precisam de descanso. Para os marinheiros, você vende chá de tomilho, e para os piratas, rum e pentes de osso para desembaraçar a barba. O serviço na taverna é realizado por um robô-hostess e um robô-barman. Devido à alta qualidade e preços baixos, você eliminou todos os concorrentes, de modo que cada um que desce do navio vem à sua taverna, que é a única no porto.
O complexo de sistemas de informação da taverna consiste no seguinte software:
- Sistema de alerta antecipado sobre o cliente, reconhecendo sua categoria por características distintivas
- Sistema de gerenciamento de robôs-hostess e robôs-barmans
- Sistema de gerenciamento de estoque e entrega ao ponto de venda
- Sistema de gerenciamento de relacionamentos com fornecedores (SGRS)
Processo:
O sistema de alerta antecipado reconhece as pessoas que descem do navio. Se a pessoa estiver bem barbeada, ela é classificada como marinheiro; se a pessoa tiver barba, é classificada como pirata.
Ao entrar na taverna, o hóspede ouve do robô-hostess as boas-vindas de acordo com sua categoria, por exemplo: "Ho-ho-ho, estimado pirata, dirija-se à mesa nº..."
O convidado passa pela mesa especificada, onde o robô-barman já preparou os itens de acordo com a categoria. O robô-barman envia informações para o sistema de estoque de que a próxima remessa de entrega deve ser aumentada, e o sistema de informação de estoque, com base nos estoques disponíveis, gera um pedido de compra no SUOP.
Suponha que o sistema de alerta antecipado tenha sido desenvolvido pela sua equipe interna de TI, e o programa de gerenciamento dos robôs de bar tenha sido criado por um contratante externo especificamente para o seu negócio. Já os sistemas de gerenciamento de estoque e relacionamento com fornecedores são soluções de prateleira personalizadas do mercado.
5. Exemplos de desnormalização e seu impacto no desenvolvimento de software
Ao projetar o processo de negócios, os especialistas consultados da área declararam unanimemente que em todo o mundo os piratas bebem rum e pentear suas barbas com pentes de osso, enquanto os marinheiros bebem chá com tomilho e estão sempre bem barbeados.
Surge um catálogo de tipos de clientes com dois valores: 1 - piratas, 2 - marinheiros, compartilhado por todo o contorno informacional da empresa.
O sistema de alerta sobre o cliente imediatamente salva o resultado do processamento da imagem como um identificador (ID) do cliente reconhecido e seu tipo: marinheiro ou pirata.
ID do objeto reconhecido
Categoria do cliente
100500
Pirata
100501
Pirata
100502
Marinheiro
Vamos destacar novamente que
1. Nossos marinheiros são, na verdade, pessoas barbeadas
2. Nossos piratas são, na verdade, pessoas barbadas
Quais problemas precisam ser resolvidos para que nossa estrutura busque a terceira forma normal:
- violação da atomicidade do atributo - Categoria do cliente
- mistura do fato analisado e da conclusão em uma única tabela
- dependência funcional registrada entre atributos de diferentes entidades.
Em forma normalizada, teríamos duas tabelas:
- resultado do reconhecimento na forma de um conjunto de características estabelecidas,
ID do objeto reconhecido
Pelo facial
100500
Sim
100501
Sim
100502
Não
- resultado da determinação do tipo de cliente como uma aplicação da lógica embutida no SI para a interpretação das características estabelecidas
ID do objeto reconhecido
ID de identificação
Categoria do cliente
100500
100001
Pirata
100501
100002
Pirata
100502
100003
Marinheiro
Como uma organização de armazenamento de dados normalizada pode facilitar o desenvolvimento de um complexo de sistemas de informação? Suponha que, de repente, você tenha novos clientes. Digamos que sejam piratas japoneses, que podem não ter barba, mas andam com um papagaio no ombro, e piratas ecológicos, que você fácil reconhecerá pelo perfil azulado de Greta no peito esquerdo.
Os piratas ecológicos, naturalmente, não podem usar pentes de osso e exigem um equivalente feito de plástico marinho reciclado.
Você precisará ajustar os algoritmos de funcionamento dos programas de acordo com as novas introduções. Se as regras de normalização tivessem sido seguidas, você teria apenas que adicionar entradas para algumas ramificações de processos em determinados sistemas e criar novas ramificações apenas para os casos e sistemas onde a presença de pelos faciais é relevante. Contudo, como as regras não foram seguidas, você terá que analisar todo o código, em todo o contorno, onde são utilizados os valores do diretório de tipos de clientes e estabelecer claramente que, em um caso, o algoritmo deve levar em conta a atividade profissional do cliente, enquanto, em outro, as características físicas.
Na forma que tende à normalização, teríamos duas tabelas com dados operacionais e dois diretórios:
- resultado do reconhecimento na forma de um conjunto de características estabelecidas,
ID do objeto reconhecido
Greta no peito esquerdo
Pássaro no ombro
Pelo facial
100510
1
1
1
100511
0
0
1
100512
1
0
- resultado da determinação do tipo de cliente (suponha que seja uma representação do usuário, na qual são mostradas as descrições dos diretórios)
Significa que a denormalização detectada impede que os sistemas sejam ajustados às novas condições? Claro que não. Se imaginarmos que todos os sistemas de informação foram criados por uma única equipe com zero rotatividade de pessoal, com desenvolvimentos bem documentados e informações sendo passadas na equipe sem perdas, as mudanças necessárias podem ser feitas com esforços desprezíveis. Mas se voltarmos às condições iniciais do problema, apenas a impressão dos protocolos de discussões conjuntas consumiria 1,5 teclados e mais 0,5 para a elaboração dos procedimentos de compra.
No exemplo acima, todas as três formas normais foram violadas; vamos tentar violá-las separadamente.
Violação da primeira forma normal:
Suponha que os produtos em seu armazém sejam entregues a partir dos armazéns dos fornecedores com o uso de uma van de 1,5 toneladas que pertence à sua taverna. O tamanho dos seus pedidos é tão pequeno em relação ao volume dos fornecedores que eles são sempre realizados exatamente um por um, sem esperar pela fabricação. Precisam de tabelas separadas neste cenário? Veículos, tipos de veículos, é necessário separar o planejado do realizado nos seus pedidos enviados aos fornecedores?
Imagine quantas conexões "desnecessárias" seus programadores teriam que escrever se utilizassem o modelo abaixo para desenvolver o programa.
Suponha que decidimos que a estrutura proposta é excessivamente complexa, para o nosso caso, separar o planejado do realizado no registro do pedido é uma informação redundante, e a especificação do pedido é regravada com base nos resultados da aceitação do produto recebido, raros erros de contagem e recebimento de produtos de qualidade inadequada são resolvidos fora do sistema de informação.
E um dia você vê que todo o salão da taverna está cheio de piratas irritados e desgrenhados. O que aconteceu?
Aconteceu que, com o crescimento do seu negócio, o consumo também aumentou. Algum tempo atrás, foi tomada uma decisão de gestão de que, se a van estivesse sobrecarregada em volume e/ou peso, o que acontecia muito raramente, o fornecedor priorizava a carga em favor das bebidas.
Os produtos não entregues entravam no próximo pedido e eram enviados em uma nova viagem, a presença de um estoque mínimo no armazém da taverna permitia ignorar ocorrências de falta.
No porto, o último concorrente fechou, e a ocorrência de sobrecarga da van, ignorada devido à priorização baseada na suposição de que o estoque mínimo era suficiente e na carga periódica insuficiente do veículo, tornou-se uma prática comum. O sistema criado funcionará perfeitamente de acordo com os algoritmos incorporados e não terá qualquer possibilidade de rastrear as falhas sistemáticas nos pedidos planejados. Apenas uma reputação arruinada e clientes insatisfeitos poderão detectar o problema.
Um leitor atento certamente notou que a quantidade solicitada na especificação do pedido (T_ORDER_SPEC) na Seção 2 e na Seção 5 pode ou não estar em conformidade com a primeira forma normal. Tudo depende de se as diferentes unidades de medida podem cair no mesmo campo dentro da variedade de produtos escolhida.
Violação da segunda forma normal:
À medida que suas necessidades crescem, você adquire mais alguns veículos de diferentes tamanhos. No contexto apresentado acima, a criação de um catálogo de veículos foi considerada redundante, resultando em todos os algoritmos de trabalho com dados que atendem às necessidades de entrega e estoque tratando o transporte de carga do fornecedor para o armazém como uma viagem exclusivamente de uma caminhonete de 1,5 tonelada. Portanto, junto com a compra de novos veículos, você acaba criando um catálogo de veículos; entretanto, você deverá analisar todo o código que se refere ao transporte de carga para descobrir se em cada ponto específico existem referências às características do veículo em que o negócio começou.
Violação da terceira forma normal:
Em algum momento, você começa a criar um programa de fidelidade e um registro de cliente habitual aparece. Por que, por exemplo, perder tempo criando representações físicas que armazenam dados agregados sobre vendas para um cliente específico, a serem usados em relatórios e transferidos para sistemas analíticos, se na fase inicial do programa de fidelidade, tudo o que interessa ao cliente pode ser registrado na própria ficha do cliente? E, de fato, à primeira vista, não parece haver sentido. Mas sempre que sua empresa conectar, por exemplo, novos canais de venda, deve haver alguém entre seus analistas que lembre que existe esse atributo de agregação.
Ao projetar cada novo processo, digamos, vendas pela internet, vendas através de distribuidores conectados ao sistema de fidelidade comum, alguém deve se lembrar de que todos os novos processos devem garantir a integridade dos dados em nível de código. Para um banco de dados industrial com mil tabelas, isso parece uma tarefa um tanto inviável.
Um desenvolvedor experiente, é claro, sabe como contornar todos os problemas mencionados acima, mas, na minha opinião, a tarefa de um analista experiente é evitar que cheguem a esse ponto.
Gostaria de expressar minha gratidão pelo valioso feedback ao preparar a publicação com o desenvolvedor líder, Evgeny Yaruhin.
Literatura
Connolly Thomas, Begg Caroline. Bancos de dados. Design, implementação e manutenção. Teoria e prática.
Fonte: habr.com
