Foreign Key Coming from 2 Primary Keys from Distinct Tables

I have a doubt about how to establish a relationship one-to-many between 2 tables when the table from the “many” side has a foreign key field that receives data from 2 primary keys from distinct tables.

the problem is that it is not possible to establish a relationship one-to-many between tblInput (one) and tblFabricatedProductComponents (many) tables. the same happens between tblFabricatedInput (one) and tblFabricatedProductComponents (many) tables.

please, have a look in the attached schema. I think you guys will understand much better. this field is highlighted in red.

thanks in advance.

p.s.: my idea is that I’d have to split tblFabricatedProductComponents into:

tblFabProdComponentsFromInput

and

tblFabProdComponentsFromFabricatedInput

Answer

If I unterstand you correctly, you have two types of inputs: simple ingredients like sugar, coconut, etc, and fabricated inputs like coconut cream, which consists of multiple simple ingredients. If so, I would combine the tblInputs and tblFabricatedInputs and introduce a new field input_type like this:

Important: tblFabrInputIngred hasn’t any relation with tblFabrProdComp. It is just a detail table to tblInputs. tblFabrProdComp has relation only to tblInputs and tblFabrProducts.

And if you want to calculate, how many sugar you need for a coconut cream cake, then you can do that with a simple join.