Results 1 to 4 of 4

Thread: The "set" field

  1. #1
    Join Date
    Jul 2010
    Posts
    2

    Unanswered: The "set" field

    Bear with me as I'm very new with databases. This may be a somewhat simple question.

    I'm building a website of items that can be found in multiple places around the globe.

    I've broken down the various levels into individual tables, with foreign keys: table1=continents, table2=subcontinents, table3=countries and table4=districts/states/etc.

    Where I run into trouble is with the Item table. Some items will be in several dozen countries on several different continents. I don't want a hundred fields just so I can show multiple locations. One foreign key isn't going to do it, if I understand how it works. There could be dozens of combinations/permutations per item.

    Is this something I can do with the Set field? If so, can I get a layman's explanation of it? I've looked it up online, and I get that it might be my solution, but then my brain starts spinning in its description.

    thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by turpentyne View Post
    I've broken down the various levels into individual tables, with foreign keys: table1=continents, table2=subcontinents, table3=countries and table4=districts/states/etc.
    this sounds like a location hierarchy

    for instance, a country belongs to one and only one continent, yeah?

    (if you conveniently want to ignore turkey)

    so if an item belongs to a country, you automatically can conclude that the item also belongs to the continent that the country belongs to

    so all you have to do is link your items to table4

    and of course this has to be a many-to-many relationship, so you need an items_to_table4 table

    the mysql SET datatype is ~not~ what you're looking for
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2010
    Posts
    2

    maybe I'm starting to get it

    Ok... I think what I haven't been understanding is that there needs to be a table between the two I'm concerned about.

    so for example if I have a plants table, and a locations table:

    I need to build a table that is plant_locations. with something like a key id, then a foreign key for the plant and a foreign key for the locations. So there would be individual entries for each possible location?

    so when I build the form for somebody to enter plant locations, they're actually putting info into this linking table?

    (just to clarify, there's a separate form & table of plants that has other characteristics unrelated to location)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by turpentyne View Post
    I need to build a table that is plant_locations. with something like a key id, then a foreign key for the plant and a foreign key for the locations.
    yes, except without the key id

    CREATE TABLE plant_locations
    ( plant_id INTEGER NOT NULL
    , location_id INTEGER NOT NULL
    , PRIMARY KEY ( plant_id, location_id )
    , FOREIGN KEY ( plant_id ) REFERENCES plants ( id )
    , FOREIGN KEY ( location_id ) REFERENCES locations ( id )
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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