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.
- Should additional (optional) tables be added? How this would be implemented is a question to me though.
- 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.
- 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.
- 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.