Database design experienced view

best practicesdatabase-designdatabase-recommendation

I want to make a database that stores countries, their respective states, and cities. I am confused how to normalize these. Can anyone help?

My ideas: 

Table 1  Countries (id pk, name , code)
Table 2  State (id pk, name, country_id fk, code)
table 3  Cities (id pk, name, state_id fk, pin_code)

I want to store hotels along with their address(es):

table 4 address (id pk, street_address, city_id fk, state_id fk, country_id fk)
table 4 Hotel (id pk, name, address_id)

Are there any changes for future proofing I should make, or any other mistakes I've made?

Best Answer

Well, if you want/need to break it out that way, I would go:

Country(id,name,code)
Region(id,name)
Sub_Region(id,name)
City(id,name)
Address(id,country_id,region_id,sub_region_id,city_id,street_address,postal_box,appt_suite_number,floor_number,postal_code)

That should cover most of your scenarios US and world-wide. (ie, region could cover states, provinces, etc. Sub region can cover counties, sub-provinces, etc. Postal code instead of zip code so it can work world wide.) And I suppose if you really wanted to get fancy you could break out the street "type" (ie, street, avenue, boulevard, circle, etc.). But I don't know if you need that level of granularity or not?

Related Question