SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Questions about Joining & Foreign Keys

    I'm just learning how to make MySQL tables, and I want to make sure I set them up so that I can join them. I thought I knew what I was doing, but I just checked the MySQL Manual and a tutorial again and discovered that I'm confused.

    First, let me introduce my tables:

    1. Continents & Oceans
    2. Nations
    3. States
    4. Counties
    5. EcoRegions

    Each table begins with a field/column named ID, which begins with the numeral 1 and is designated the Primary Key. This is followed by the fields Name and Type.

    Examples of names and types from all five tables:

    1. Continents & Oceans - Africa, Continent
    2. Nations - Guam, dependency
    3. States - Massachusetts, commonwealth
    4. Counties - North Slope, borough
    5. EcoRegions - Great Plains, temperate grassland

    Before I go any further, I put a screen shot online at http://www.geoworld.org/worlddatabase.gif (The image looks awfully small, but, as you probably know, you can enlarge it with Mozilla Firefox and perhaps with IE as well.)

    The Continents & Oceans table features an extra column with two values. The first identifies each entry as New World, Old World, Southern Continent or Oceanus. The second identifies it as Northern Hemisphere, Southern Hemisphere or Both Hemispheres. There are several columns of additional data in the Counties table.

    And that's about it, except for the identification codes. I thought primary keys were generally numerical and began with 1, and that I'd be using a FOREIGN KEY to join these tables.

    The continents table ends with a field named CCode (Continent Code). The value for North America is na.

    The nations table ends with two fields named NCode (Nation Code) and CCode. For the United States, the Nation Code is US, and the continent code is na, which is supposed to link it to the Continents table.

    The States table ends with two fields named SCode (State Code) and NCode. Alaska's SCode value is AK, while its NCode value is US. I didn't think it was necessary to link it to a continent, because it will be linked to the U.S., which is, in turn, linked to North America.

    The Counties table doesn't include codes for each county, but it ends with a SCode field, linking each county to its respective state.

    The EcoRegions table is more complex. For example, the Great Plains occupies portions of several states and provinces in both the U.S. and Canada. Tentatively, I'm giving each biome (e.g. Great Plains) a field named ECode. In my example, I made two rows for Great Plains, one linked to Alberta, the other South Dakota. I also included a field for the nation code, but I assume that isn't necessary - unless I make a presentation that mentions nations but not states.

    Anyway, I thought I was going to designated various code fields - Nation Code, State Code, etc. - foreign keys and link them to each other. But I just read that you have to join a foreign key to the master table's primary key.

    So let's say the master table is Continents & Oceans, and its primary key is numbered 1-18. On my Nations table, I have a foreign key with abbreviations, like us and mx for the U.S. and Mexico. How can I link this to a primary key that uses numerals?

    Or do I need to create a foreign key that also uses numeral? If the Continents table describes North America as 1 in the primary key, then would a foreign key in a Nations table also refer to North America as 1?

    And if that's the case, would I need to have a Primary Key in the second table?

    If yes, could I number it 1 through 246, then use these same values in a foreign key on the third table?

    I don't know if it helps any, but I also took a snapshot of my table layout, at
    http://www.geoworld.org/worlddatabase.gif

    Thanks!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the numeric primary keys that you have in each table are fine

    the foreign keys need to use the same values

    the codes that you are using to "relate" rows could also be used as primary/foreign keys

    your choice

    some people will argue vehemently that you should always use the numerics (called surrogate keys) instead of the codes (called natural keys), but the more vehemently they insist that you should only and always use surrogates, the less i trust their competence

    just remember that you can join tables on any columns you wish, regardless of whether those columns are primary/foreign keys
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    the numeric primary keys that you have in each table are fine

    the foreign keys need to use the same values

    the codes that you are using to "relate" rows could also be used as primary/foreign keys

    your choice

    some people will argue vehemently that you should always use the numerics (called surrogate keys) instead of the codes (called natural keys), but the more vehemently they insist that you should only and always use surrogates, the less i trust their competence

    just remember that you can join tables on any columns you wish, regardless of whether those columns are primary/foreign keys
    OK, I think I understand what you're saying. If the ID field and Primary Key on my Nations table is a string of numerals, 1-250, I can use the same field as the Foreign Key on my States table and join the two columns.

    OR, I can designate country codes (e.g. us, mx) as either a Primary Key or Foreign Key on my Nations table and use those same country codes as a Foreign Key on my States table to join them.

    Actually, I would greatly prefer to use codes/abbreviations. If I'm working on a states table, and I want to join an Alberta row to Canada on my nations table and link Florida to the U.S. on my nations table, it would be so much easier to remember "ca" and "us" than 3 and 4 - or was that 4 and 5? 5 and 6?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that's true, except you do not need to "remember" the numeric (surrogate) key values

    that's the whole point of surrogate keys, they have absolutely no meaning and should never be revealed to the system's users

    all your queries would be based on the codes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •