Normalization question regarding address,city,country data

I currently have 3 tables s开发者_如何学JAVAtoring 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!

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 ;-)

I think you are already overdoing it. Regions, Cities and Countries as seperate tables is great, as you have the ability to easily manage the entries without creating data inconsistence. But storing address data for each user / organisation in a seperate (locations) table is unnecessary. Add those fields to the user / organisation tables.

EDIT: RB illustrated it in much more detail :)