April 24th, 2013, 06:28 AM
Is it better design to have 2 foreign keys when joining 3 tables?
This seems to come up quite often. Which one is a better design? I see it is not neccessary to have country_id column for customers table.
April 24th, 2013, 07:20 AM
storing addresses is not trivial.
If you assume that every city belongs to exactly one country and that this assignment never changes, making a country_id column in the customers table is actually wrong. This is redundant data with all the problems that come with it. For example, you could have conflicting information: Your customer entry says "I come from city 17, which is in Norway", but your countries table says "city 17 is in Denmark". Now what?
However: Sometimes the country a city belongs to does change. If, for whatever reason, you need to account for that, you would indeed store the country in the customers table -- as a kind of snapshot: "This city belonged to that country when the user registered".
Also note that some cities belong to different countries at the same time. So a city itself does not determine a certain country. The country would have to be stored separately. However, if you wanted to account for this special case, you'd need to restructure the "cities" table as well and put the assignments in a separate table like "city_to_country".
April 24th, 2013, 07:23 AM
Originally Posted by Jacques1
Usually in my cases they dont change. Package belongs to room and room belongs to hotel. Sydney is always in Australia. But I hear you thank you.