foreign key question

[IBazz]

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?

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

[ScallioXTX]

So, basically, is it properly done, if a column is used for > 1 foreign key?

What makes you think that won't be proper?

[r937]

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

[IBazz]

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.

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?

[r937]

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?

[r937]

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

[IBazz]

thanks rudy.

my pencils are crying right now!