1 Reply Latest reply on Apr 9, 2014 11:05 AM by philmodjunk

    Are my relationships set up right?



      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!


        • 1. Re: Are my relationships set up right?

               But is this really "many to many"?

               Won't a given record in Grains be linked to one and only one supplier?

               If another supplier provides you with, say "Barley", won't it have a different record in order to record different data for "color", "cost unit", etc?

               Seems like you need a primary field in Supplier Ingredient that links to foreign key fields in the Grains and Hops table, producing strictly one to many relationships from supplier to supplier_ingredient to Ingredients.