I am creating a database for a factory that contains three tables: one with a list of Lines that are currently running, one of Support Departments that are running and one for Product Types that are made on each line. The problem that I found is linking the Product Type table back to the line/support department tables. I have a Primary Key LineId in the Line table and SupportDeptId in the Support Department table. I would like to only have one foreign key in the ProductSize table, but the only way I can find to do that is to create a middle table with lines and/or support department ids, and to link through that table. Does anyone have any suggestions?
Why do you only want one foreign key in the table?
Usually a foreign key is just implemented as an index. You pay a little performance penalty on inserts/updates, but not as much as creating a new table.