2

This question already has an answer here:

I've been gathering information about how addresses in different countries are written. While doing that I've also been working on a database design to hold this world wide information most probably used for making deliveries.

I started off with GeoNames.org data and came up with a first design (the higher table has the lower table as a child) :

Continent
  Country
    City
      Address

With Currency (as a FK) and Language (with a many-to-many table) linked to the Country table. Also a many-to-many Capital table linking a Country and a City.

Soon I realized the US for example uses the state's code in their address. So an extra Province table was inserted between Country and City:

Continent
  Country
    Province
      City
        Address

Now I stumbled upon United Nations Code for Trade and Transport Locations (UN/LOCODE). This organization provides a list with all the cities in each country with some detailed information. This data fits pretty good with my 2nd design. I only renamed the Province table to SubDivision which is in the US the state, in Belgium the province and in France the department. This is very similar to the ISO 3166-2 codes.

Continent
  Country
    SubDevision
      City
        Address

To me, this looks like a good detailed database structure that won't need to be altered in the future. Like I said in the beginning, Currency is a FK in the Country table easy editable or removable. Language has a many-to-many relationship with Country and is also easy editable or removable. My biggest concern is that ISO also has more than 1 subdivision level fear certain countries. For Belgium for example there they have the provinces (provided in the UNLOCODE code list), but ISO (ISO 3166-2:BE) also has 3 regions that are higher than the provinces in Belgium and are pretty much in-between the Country and the Province.

  1. Should additional (optional) tables be added? How this would be implemented is a question to me though.
  2. Another database design I can think off is all loose tables, no FK's, and 1 table linking all required tables together with following columns: AddressID, CityID, SubDiv1ID, SubDiv2ID, ... , CountryID, ContinentID. Not sure if this is a good approach.
  3. Add a different table per Country based on their amount of SubDivisions. This means Continent and Country and Address stay unchanged. City will be Country specific with the US for example having a USState (SubDiv 1) -> City and Belgium BERegion (SubDiv 1) -> BEProvince (SubDiv 2) -> City.
  4. I did a little more searching and found this answer.

Consider creating lookup tables for city, state and country entities. The city/state/country columns of the address table then consist of FKs pointing to these lookup tables. This allows you to guarantee consistent spellings across all addresses and gives you a place to store additional metadata (e.g., city population) if needed in the future.

marked as duplicate by Joel Brown, Philᵀᴹ, mustaccio, LowlyDBA, Michael Green Jul 7 '16 at 16:49

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Turkey spans two continents. Some countries have an official and a de facto currency - Zimbabwe comes to mind. Which city is the capital of more than one country that needs a many to many? – Michael Green Sep 28 '15 at 11:08
1

The answer will very much depend on the use you wish to make of the addresses. If you simply require a bunch of glyphs to print on a delivery docket then what you have looks good enough. The ADDRESS table will simply absorb the local variations in formatting, sequencing and line breaks. If you require to use this for, say, automated sorting and delivery route planning you will need a lot more detail than your five level hierarchy. If your purpose is to qualitative compare regions or subdivisions across countries then you must analyse that in detail and model at the level that suits that need.

Trying to chase down the One True Address model is a fool's errand. Leave that to each national postal organisation and concentrate on what adds unique value to your application.

Not the answer you're looking for? Browse other questions tagged or ask your own question.