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)