SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    foreign key question

    In the table below, I have a foreign key which relates back to the pk of the parent table.

    However, I have another table languages_in_business, which is a many to many table. It stores the business_id and language_abbr to show what languages the business supports.

    Should my last foreign key in the table below, relate to the languages table or can it relate to languages_in_business? I ask because the business_id col is already FK'd to this table's parent table.

    I don't just want to store the language value. I want to store the language values (ENG,FRE,CAN,GER), which are permitted for/supported by, this business.

    So, basically, is it properly done, if a column is used for > 1 foreign key?
    Code MySQL:
    create table site_navigation_buttons
    ( business_id int not null
    , nav_menu_name varchar(99)
    , sub_name varchar(99)
    , live_dev varchar(12)
    , language_abbr char(3)
    , nav_button_label varchar(24)
    , primary key ( business_id, nav_menu_name, sub_name, live_dev)
    , constraint siteNavigationButtons_siteNavigationMenuStructure_fk
      foreign key (business_id, nav_menu_name, sub_name, live_dev)
        references site_navigation_menu_structure(business_id, nav_menu_name, sub_name, live_dev) on update cascade
    , constraint siteNavigationMenuButtons_languagesAbbr_fk
        foreign key (business_id, language_abbr)
          references languages_in_business(business_id, language_abbr)
    )

    bazz

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,080
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by IBazz View Post
    So, basically, is it properly done, if a column is used for > 1 foreign key?
    What makes you think that won't be proper?
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,267
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    bazz, your FK in this table which references the site_navigation_menu_structure table also happens to be the same as this table's PK

    which means this table is in a one-to-one relationship with the site_navigation_menu_structure table

    why would you do that? why not toss the language_abbr and nav_button_label columns in with the other columns for the site_navigation_menu_structure table?

    was it your intention that each { business_id, nav_menu_name, sub_name, live_dev } be available in only one language?

    if not, you gots to rethink the PK for this table

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    groan! I wonder why I am finding this set of tables so difficult.

    Thank you both.

    @ScallioXTX - I thought/wondered if there would be a conflict because the FK was being controlled from more than one place.

    @rudy,
    I have rethunk my table PK. The FK is now (I think), as it needs to be because there will be one nav_menu_button_label per language, for each business.

    Code MySQL:
    CREATE TABLE site_navigation_buttons
    ( business_id INT NOT NULL
    , nav_menu_name VARCHAR(99)
    , sub_name VARCHAR(99)
    , live_dev VARCHAR(12)
    , language_abbr CHAR(3)
    , nav_button_label VARCHAR(24)
    , PRIMARY KEY ( business_id, nav_menu_name, language_abbr, nav_button_label)
    , CONSTRAINT siteNavigationButtons_siteNavigationMenuStructure_fk
      FOREIGN key (business_id, nav_menu_name, sub_name, live_dev)
        REFERENCES site_navigation_menu_structure(business_id, nav_menu_name, sub_name, live_dev) on UPDATE CASCADE
    , CONSTRAINT siteNavigationMenuButtons_languagesAbbr_fk
        FOREIGN key (business_id, language_abbr)
          REFERENCES languages_in_business(business_id, language_abbr)
    )

    dat better?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,267
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    dat better?
    no idea

    i mean, sorry, but i don't know your data, so i can't say if that'll suit your requirements

    why did you drop sub_name and live_dev from the PK? when the FK includes them?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,267
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    oh, and i just thought i would add this comment...

    if you're thinking any of this would be easier with surrogate keys, you'd be in danger of making the same error that most people who use surrogate keys make, namely ~not~ doing what you're doing with referencing your FKs to unique/PK keys using natural columns

    mind you, i hardly ever get as far along in the development process as creating tables until well after i have nailed my keys during the logical design process

    and i do my logical design using paper and pencil, drawing little boxes and connecting them with arrows

    a lot easier than juggling/redesigning multiple-column keys in CREATE TABLE statements and FOREIGN KEY references
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks rudy.

    my pencils are crying right now!


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
  •