#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,687
    Rep Power
    171

    Is it better design to have 2 foreign keys when joining 3 tables?


    Hello;

    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.
    customers
    -----------
    id
    city_id

    cities
    -----------
    id
    country_id

    countries
    ----------
    id
    VS
    customers
    -----------
    id
    city_id
    country_id

    cities
    -----------
    id
    country_id

    countries
    ----------
    id
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    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".
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,687
    Rep Power
    171
    Originally Posted by Jacques1
    Hi,

    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".
    Hey 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.

IMN logo majestic logo threadwatch logo seochat tools logo