Normalization question regarding address,city,country data

Question

I currently have 3 tables storing information about all the main cities in the world, each region/state that corresponds to those countries, and each city that is in those states/regions.

Now I have about 6 other tables in my database e.g. the Users or Organisations tables which need the exact same 5 columns: address, suburb, city, state/region, country. So I was wondering if it was 'good' normalization practice to perhaps use a 'Location' table which stores those 5 pieces of information, then the Users or Organisations table would have a location_id to reference back to.

Good idea or bad idea? I'm also considering using a 'Contacts' table in the same principle which would contain home_phone, business_phone, mobile_phone, email_address rather than have those same 5 columns in each of the 6 tables.

Any advice appreciated. Many thanks!

Answer 1

That's an unnecessary set of relationships - you can get the regions/country from the city instead.

Note that I've got regions and countries the wrong way round in the image below, but it illustrates what I mean!

Naturally, it would be useful to define a view combining cities, countries and regions for actually writing your queries with. I also think that normalizing to this level is a very good idea in practice. When you come to add a new field to your locations table you will be very glad you've split it out ;-)