Are my relationships set up right?
I am trying to build a portion of a solution that will compile multiple tables of ingredients into a combined ingredient list pulling in the individual ingredient from one of the ingredient tables and matching it with a supplier from a supplier table. The reason that I am doing it this way is because there are instances where more than one supplier may supply a particular type of ingredient and many ingredients may be supplied by different suppliers -- i.e the classic many to many relationship. However, the different types of ingredients (i.e. grains, hops, etc) need to be separated into different tables because the fields for each type of ingredient types are different. Therefore, I have multiple ingredient tables each with its own primary key that are all relating to a single foreign key in the join table. Do I have the relationships set up right? Or should I have separate foreign keys in the join table for each ingredient table? A screen shot of my relationship table is attached.
In any event, how do I build interface to pull one of the ingredients from one of the ingredient tables into the supplier_ingredient table and match it with a supplier from the supplier table? I want the result to be a list that shows the ingredient, the supplier, the units and the unit cost.
Thanks for the help!