Nesta primeira parte, vamos propor uma estrutura de tabelas relacionais para a manipulação de CEPs e cadastramento de endereços com um mínimo de intervenção do usuário, e que também seja simples e direta para produzir relatórios com foco comercial ou estatístico. Diferente das bases fornecidas pelos correios, onde o foco é a consulta por endereço e/ou por cep, esta está otimizada para consulta através do CEP e não do endereço, e para a extração de dados de maneira macro, como UF ou CIDADE.
A ideia deste projeto é permitir que você implemente um serviço de autopreenchimento, modularizando sua base de acordo com a abrangência física do projeto. Não adianta termos uma base gigantesca como a dos correios, disponível para nosso cliente, se o negócio dele se limita por exemplo a um cadastro dos clientes de uma loja de bairro, então, nesta série de artigos, vamos propor uma estrutura de dados, onde você pode manter um cadastro local dos dados, e atualiza-lo com base em informações vindas de serviços públicos de consulta gratuitos ou não.
Na nossa base de países, incluímos não só o nome em português, como também em Inglês, para manter a compatibilidade com o padrão ISO e as abreviações definidas na ISO com 2 letras, 3 letras (atual) e o código numérico ISO e BACEN (usado pelo IBGE e pelo BANCO CENTRAL) para referências dos países. Veja abaixo a estrutura proposta:
CREATE TABLE `paises` ( `pais_id` smallint(5) UNSIGNED ZEROFILL NOT NULL COMMENT 'ID do Pais', `pais_nome` varchar(200) DEFAULT NULL COMMENT 'Nome em Português', `pais_nome_en` varchar(200) DEFAULT NULL COMMENT 'Nome Internacional (inglês)', `pais_bacen` char(4) DEFAULT NULL COMMENT 'ID pelo sistema BACEN (banco central brasileiro)', `pais_sigla` char(3) NOT NULL COMMENT 'Sigla com 3 Letras, Padrão ISO', `pais_sigla_iso` char(2) DEFAULT NULL COMMENT 'Sigla', `pais_iso` char(4) NOT NULL COMMENT 'Código Único Padrão ISO' ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Países e Nações';
Caso alguém precise ou queira utilizar o cadastro já com os DADOS, segue um link disponibilizando uma cópia da tabela: TABELA_PAISES_MYSQL.SQL para download a partir do Google Drive. Neste cadastro, usamos os 251 códigos disponíveis e em uso pelo comitê ISO, quem quiser maiores detalhes pode acessar as diretrizes da norma diretamente no site da entidade (EM INGLÊS): https://www.iso.org/iso-3166-country-codes.html
Seguindo a mesma linha de tabela, segue a estrutura para uma tabela de UNIDADES FEDERADAS, ou os ESTADOS, a estrutura permite uso de estados com 3 caracteres, no caso dos estados brasileiros, somente são usadas UFs com 2 CARACTERES na sua SIGLA. Mantendo a compatibilidade com os dados do IBGE, inserimos o campo IBGE contendo o ID (identificador) único na entidade, que é usada em todo o território nacional. Veja a estrutura:
CREATE TABLE `ufs` ( `uf_id` char(2) NOT NULL COMMENT 'ID padrão IBGE', `uf_nome` varchar(75) DEFAULT NULL COMMENT 'Nome por Extenso', `uf_sigla` varchar(2) DEFAULT NULL COMMENT 'Sigla (UF)', `uf_ddd` varchar(50) DEFAULT NULL COMMENT 'DDDs Validos Para os Telefones separados por virgula', `pais_id` smallint(5) UNSIGNED ZEROFILL DEFAULT NULL COMMENT 'ID Do Pais de Origem ou 0 para Exterior)' ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Unidades Federativas (estados) ';
A atenção vai para 2 campos, uf_id que contém o código único atribuído pelo IBGE para cada estado, perceba que ele não é sequencial, pois o primeiro digito equivale a uma das 5 macro regiões brasileiras, quem trabalha com estatística, pode usar esse grupo na geração e extração de dados e ele é também a CHAVE PRIMÁRIA da tabela. Outro campo é uma listagem de DDD válido para aquele estado, que serve para auxiliar na validação ou no preenchimento de outros dados de cadastro.
Ah, os código de pais_id, foi mantidos com 5 dígitos, pois dependendo do objetivo, pode ser necessário além dos cadastros referentes aos estados do Brasil, o uso de estados e territórios de outros países e nações, que podem ser compostos, para facilitar a extração de relatórios e consulta aos dados. Para manter a compatibilidade com as normas ISO e IBGE os países onde não existe a divisão de UNIDADES FEDERADAS, mantemos o código 00000 que equivale a um pais EXTERIOR, essa regra é definida também pela SEFAZ para a manutenção de campos das NF-e (Notas Fiscais Eletrônicas). Quem quiser uma listagem contendo os cadastros dos estados e territórios brasileiros, pode baixar de: TABELA_UFS_MYSQL.SQL pelo Google Drive. Neste cadastro, para endereços de outros países, o código de UF pelo IBGE é 99.
As Siglas dos estados brasileiros, acompanham a definição da norma ISO 3166-3 que identifica UNIDADES FEDERADAS e TERRITÓRIOS de cada pais, e é determinada pelo código de pais de 3 digitos + 2 ou 3 digitos referentes a UNIDADE FEDERADA. Por Exemplo São Paulo (SP) pelo identificador ISO é definido como BRA_SP.
Para o cadastro de CIDADES, a estrutura segue também os padrões do IBGE, onde existe a seguinte estrutura de campos:
CREATE TABLE `cidades` ( `cidade_id` char(7) NOT NULL COMMENT 'ID da CIDADE ou MUNICIPIO conforme o IBGE', `cidade_nome` varchar(200) NOT NULL COMMENT 'Nome da Cidade', `uf_id` char(2) NOT NULL COMMENT 'ID do UF IBGE' ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Cidades ';
Aqui é uma tabela simples, criada de acordo com as definições de cidades brasileiras pelo IBGE, mantemos os padrões já estabelecidos e usamos como chave primária a definição padrão usada pelo IBGE que é também composta facilitando as consultas e extrações de relatórios. Segundo o IBGE em 2019 temos 5570 cidades. Caso alguém necessite, pode baixar o dump da base de cidades em: TABELA_CIDADES_MYSQL.SQL no Google Drive.
Para a Tabela de BAIRROS, mantivemos a mesma estrutura da tabela cidades, como segue:
CREATE TABLE `bairros` ( `bairro_id` char(11) COLLATE utf8_unicode_ci NOT NULL, `bairro_nome` varchar(200) CHARACTER SET latin1 DEFAULT NULL, `cidade_id` char(7) CHARACTER SET latin1 NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
No caso dos bairros, seguimos o padrão dos nomes usados pela especificação do IBGE, acrescentando um sufixo de 4 casas ao número da cidade, que identificam o bairro sequencialmente dentro da sua cidade. Como podem ocorrer mudanças de bairros, como acréscimos e cisões, esta numeração foi gerada com base própria (sem uso de sistemas ou padrões de terceiros), já que a definição de bairro é muito confusa nos padrões brasileiros. Os correios identificam somente o literal, como um sufixo do endereço, que não considera o processamento da informação. O IBGE define somente DISTRITOS e SUBDISTRITOS sendo os distritos como sendo demarcações territoriais, e os subdistritos como sendo regiões administrativas.
Considerando-se que a maioria dos negócios e cadastros seja para endereçamento postal, ou para distribuição e áreas de entrega, consideramos a informação dos correios, porém, utilizamos uma ferramenta de normalização (em breve vamos preparar um artigo sobre isso) para facilitar a operacionalização, relatórios, extração e manipulação de dados, separando os bairros em uma subdivisão da área das cidades. Quem quiser utilizar essa mesma classificação, ou os dados, atualmente conto com uma base de 26.787 bairros em todo o brasil. Considerando-se o agrupamento por cidade (cabe uma discussão aqui, principalmente no que diz respeito a normalização de bases de dados, e gostaria de opiniões pelos comentários, se possível). Baixe o dump da TABELA_BAIRROS_MYSQL.SQL diretamente do Google Drive.
Para a tabela dos LOGRADOUROS (Ruas, avenidas, praças e etc) utilizamos o CEP como chave primária, já que cada CEP identifica somente 1 resultado, e a especificação dos correios determina que desde 2018 o DNE de todo o território nacional é feito através dele. Vale ressaltar que em raras situações, o endereçamento lógico do cep, pode ser diferente do endereçamento físico, principalmente nos lugares onde os correios não atuam, já que a abrangência das entregas e correspondências não é de 100% do território. Portanto, o cadastro de logradouros é uma sugestão, e o endereço físico tem que ser editado pelo próprio usuário. VEJA MAIS NAS CONSIDERAÇÕES FINAIS DO ARTIGO. Veja abaixo a proposta da estrutura da tabela LOGRADOUROS:
CREATE TABLE `logradouros` ( `logradouro_cep` varchar(9) NOT NULL, `logradouro_tipo` varchar(20) DEFAULT NULL, `logradouro_nome` varchar(70) DEFAULT NULL, `bairro_id` char(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8-general-ci;
Caso queira baixar o dump, temos cadastrados 633.469 registros de CEPs do Brasil, com todas as informações de CEP por Rua, onde os correios atuam com total cobertura das cidades. Baixe o dump da tabela pelo link: TABELA_LOGRADOUROS_MYSQL.SQL diretamente do Google Drive.
Considerações Finais do Artigo
Esta estrutura proposta, conta com todo o necessário para a criação de um autopreenchimento de endereços pela digitação do CEP ou mesmo a criação de uma API ou WEBSERVICE de consulta que requer muito poucos recursos do servidor, e abrange todo o território nacional.
Quando criamos este tipo de serviço, os CORREIOS indicam não criar relacionamento direto de outras partes do banco de dados com estas tabela, ou seja, se na mesma base de dados, tivemos um cadastro de ENDEREÇO, podemos usar a tabela de LOGRADOURO como uma forma de consulta e autopreenchimento, mais não podemos ter por exemplo a definição de CEP como a chave estrangeira da tabela ENDEREÇO, o mesmo ocorre com o Bairro. A justificativa é que o CEP e o DNE são sistemas ainda em evolução, e que podem ser drasticamente alterados sem aviso prévio, não mantendo nenhum relacionamento do endereço lógico (CEP) com o endereço físico (DNE). Em outras palavras, o CEP visa ser igual ao DNE no momento em que TODAS AS ÁREAS do BRASIL forem totalmente mapeadas pelo CEP, e este pode sofrer alterações bruscas para atender a demanda.
Quanto ao quesito da NORMATIZAÇÃO, nem todas as tabelas atendem a 3FN propositalmente, e num artigo futuro, vamos explicar e determinar o motivo, mais isso é assunto para discussão, se alguém quiser ir esquentando pode deixar o comentário ai abaixo, e também alguma dúvida, estou a disposição.
Na Próxima semana, vamos dar continuidade, ao desenvolvimento deste WEBSERVICE ou API de consulta e atualização de CEP incluíndo os detalhes de CEPS ÚNICOS PARA CIDADES (usado pelos correios, onde o sistema CEP não é igual ao DNE) e também as faixas de RESERVA OPERACIONAL DE CEPS, como as faixas de CEP por estado e o porque do SUFIXO (3 DIGITOS) ser realmente mantido em separado.
CONHEÇA NOSSA API PÚBLICA DE CONSULTA DE CEP
