SQL tables for postal codes

Asked

Viewed 546 times

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

  • 1

    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

  • 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 table concelhos?

  • 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.

  • @Ricardopunctual I have this with me and with this file come 2 more that are the districts and the Counties

  • @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

Show 1 more comment
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.