Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite
2

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?

share|improve this question
2  
Is this homework? – Phil Aug 23 '12 at 11:59
4  
Many countries do not have "states". Perhaps more than the ones that have states. Some have counties as a sub-region to country, some as a sub-region to state. Some have "divisions" or "perfectures". Some cities belong to more than one country. And then you have countries that are united or divided, so the structure is not stable in time. You have to first decide which aspects of this complex problem you want to deal with and which to leave out. – ypercube Aug 23 '12 at 12:39
is their any free database which carry these records – css3schools.comSahil Popli Aug 24 '12 at 5:03
Pondering how Singapore fits into this design...... – Chris Travers Sep 21 '12 at 8:21
BTW, street_address should be a fully free-form text field. We like to try to reduce to more atomic units here but addresses have so many global exceptions that's really not a good idea. – Chris Travers Sep 21 '12 at 8:24

2 Answers

up vote 1 down vote accepted

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?

share|improve this answer
There may be an issue here since there is no relationship between sub_region, region and City. For example, you can't list all sub_regions of region 'USA'. Similarly, you can't show all countries where 'Alexandria' is a city. – Emmad Kareem Aug 24 '12 at 9:17
It just depends on your needs. And you could you would just have to go through the Address table. The address is the relationship that unites country, region, sub_region, and city. If you think of it that way, it makes sense. It is kind of almost like a fact table in that regard, but that isn't the point. I also borrowed this idea from IAA (Insurance Application Architecture) where they tend to relate an address this way. But again, I think it goes back to answering what do you need. – Chris Aldrich Aug 24 '12 at 16:00
Most scenarios, until you get to Nicaragua, Ecuador, or plenty of other places. Ecuador typically has cross-streets required in addresses, and Nicaraguan addresses are.... unique in their own system. – Chris Travers Sep 21 '12 at 8:22
up vote 0 down vote

I have the following remarks:

1-According to your question's text, the fk for city and country are repeated unnecessarily in the Address table. given the city id alone, you can find the country and state by a join. Repeating them represents redundant relationships.

This desing may speed search by country or by state slightly though. You'd have to include 3 FKs in the Address table if the PK of State is composed of StateID+CountryID and the PK for city is CityID+StateID+CountryID. If you do this, the the FK in the address table becomes the compound key of CityID+StateID+CountryID.

2-Table names should be singular, e.g. City not Cities.

3-The Code column is not clear to me, is it unique? If it is, why use another ID?

4-The 'Address' table is probably meant to represent the address of the resort. However, this may not be quite practical. Some resorts have more than 1 distinct (sub-resorts) that may require different (or maybe the same) address. Also, there could be a management or contact address that is different from the resort address. You need to take care of this if your business rules demands it.

Edit - Attempting to show that only 1 FK is a must in the Address table and the rest are redundant from modeling perspective. FK2 and FK3 could be obtained by joinng the Address table with Country, State and City tables when only FK1 is present in the Address table. enter image description here

share|improve this answer
disagree cites can be in more than one state or even country, you need all three pks. – HLGEM Aug 23 '12 at 17:39
@HGLEM, I believe that my answer is correct as stated, I added a picture that may explain my point. I understand that there are situations (as indicated in the answer) and in OLAP schemas where all 3 FKs must exist. – Emmad Kareem Aug 23 '12 at 22:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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