0
Good,
I am creating a BD for a project I am doing and in this project are included Addresses (Customers, Suppliers, etc.), addresses that will contain Postal Code.
My question is this::
I have 3 tables - Districts; Municipalities; Postal Codes;
CREATE TABLE distritos(
cd VARCHAR(2), --Código Distrito
nome_dist VARCHAR(50) not null, --Nome Distrito
PRIMARY KEY (cd),
);
CREATE TABLE concelhos(
cd VARCHAR(2) not null, --Código Distrito
cc VARCHAR(2), --Código Concelho
nome_conc VARCHAR(50) not null, --Nome Concelho
PRIMARY KEY (cc),
FOREIGN KEY (cd) REFERENCES distritos(cd),
);
CREATE TABLE cp(
cp_id INT IDENTITY(1, 1),
cp_cc VARCHAR(2) not null, --Código Concelho
cp_localidade VARCHAR(50) not null, --Nome Localidade
cp_cp4 VARCHAR(4) not null, --CP4 Nº Código Postal
cp_cp3 VARCHAR(3) not null, --CP3 Extensão Nº Código Postal
cp_dp VARCHAR(50) not null, --Designação Postal
PRIMARY KEY (cp_id),
FOREIGN KEY (cp_cc) REFERENCES concelhos(cc),
);
What I intend to do is the relationship between these three tables, but I have a problem and I can’t think of how to solve it.
The list of the table Concelhos
with the table of Códigos Postais
is not possible since there are several councils with the same code and this is not unique.
I don’t know if I explained myself well but anything comment and I try to explain better.
The problem is that the codes repeat themselves, and even the postal code can repeat itself, what differentiates are other information missing in your cp table, such as the change, which can tell if they are even or odd sides and the address number ranges. For example, the CP 3750-061 may appear repeated 2x for Rua Nossa Senhora das Preces, for even and odd numbers. I suggest you visit this link that has a model with CP and you can import in Excel to better understand: https://raw.githubusercontent.com/centraldedados/codigos_postais/master/data/codigos_postais.csv
– Ricardo Pontual
I have a project in which I do this among other things, only a table with zip codes in which one of the foreign keys points to the county , this foreign key is an internal id that points to a single line in the table counties: https://www.open-data-en.migueldvl.com/en/postal codes/2765-350
– Miguel
If you say "since there are several councils with the same code" why you defined the field
cc
(County Code) as primary key of the tableconcelhos
?– anonimo
Ricardo, the address will be written manually the only information I want from the Postal Code is when I insert a new customer and fill the postal code appear the District, County and Locality. I don’t need the other information.
– Richie16
@Ricardopunctual I have this with me and with this file come 2 more that are the districts and the Counties
– Richie16
@Miguel I could not understand what you explained to me but this project you showed me on this link is not quite what I need because the address will be written manually and what I want is when I enter the Postal Code appears to me in front of the same the postal designation and below 3 fields separate the District, County and Locality. Example: 2625-254 VIALONGA Lisboa, Vila Franca de Xira, Vialonga
– Richie16