is the ZIP code a unique ID in Brazil?
I own the official base of CEPs of Brazil, the e-DNE, sold by the post office.
I need to create a new bank from it, and I would like to know if I can use the CEP value as my DB's unique identifier.
Since there are locations that use the same zip code for multiple addresses, I would like to know how to uniquely identify those locations.
2 answers
Let's go back a little to the theory of primary keys, we should do some analysis: Basic Rules for primary keys:
- there cannot be two occurrences of the same entity with the same content in primary key
- the primary key cannot be composed of an optional attribute , or that is, attribute that accepts null.
- the identifier attributes must be the minimum set that can identify each instance of an entity.
- should not be used external keys. (Attributes about which you it has no control. Ex: CPF, CNPJ)
- each key identifier attribute must have a reduced size It should not contain volatile information.
Let's start from the number 4, where CEP is a foreign key, that is, if a number is included or there is some modification, how would you act in cases like this? Remembering that recently in the city of São Paulo there was the change in the phone number. Changes like these in a PK (primary key) would cause a lot of problems. The idea of following the normalizations is to ensure the stability of your database with ACID(Atomicity, Consistency, Isolation and durability).
You could even facilitate the search by using the zip code number as an INDEX, this would help, however, in this case the best is to keep the PK with a self-increment ID even.
Coming 1 year later, but the intention here is to leave the link to the concept of"geographical thing designated by the CEP" ... This leads to 2 answers.
Only after having clarity of the object, of the thing that has been identified by the ZIP code, does it make sense to ask for example if the thing has changed, or, more importantly, if two ZIP codes would not be pointing to the same thing: if two ZIP codes point to the same thing it is not unique, considered a unique identifier .
The typical " thing "pointed out by CEPs until 1992 was a city or city sub-region, dubbed by The Post Office as a"Sub-sector divider". There was even a map map of the regions of CEP , which today still applies to map the generic CEPs, those in the form XXXXX-000 (suffix zero)... answer-1: generic CEPs are always unique, each designating its region on the map.
The ZIP code we are accustomed today, however, is one who designates a street, a square or even "the even side of the stretch of the street", etc. This type of ZIP code is called common ZIP code (it has a suffix in the range from 001 to 899). The thing he designates is a "polygon of square faces" (see Wikipedia ), which in the illustration below corresponds to a color,
(imagine for example that zip code 12345-678 designates the color blue)
So we arrive at another possible answer... Answer-2 : The common CEPs are always unique, each designating its "region of square faces" on the map.
From the topological point of view, however, has something strange: the areas of Response-2 are within that of the areas of response-1. From this point of view there is spatial overlap between different CEPs, that is, there are two CEPs designating the same thing... It can be argued that there is no uniqueness in CEPs as a whole, only when considering only one type of CEP.
There are also the "special CEPs", which can designate addresses already contemplated by the" common CEP", but again we have to understand that the" special CEP", when designating a geographical entity, is an entity within a"common CEP"... But finally: it can eventually be claimed that special CEPs duplicate some of the common ones.