title: Simple and right way to link and add columns to database tables, database design
body: <p>I have following tables.</p>
  
  <p><strong>Product</strong> with following attributes:</p>
  
  <ul>
  <li>ProductID  &lt;&lt; ID of the product</li>
  <li>ProductName &lt;&lt; name of product</li>
  <li>ProductPrice &lt;&lt; Price of product</li>
  </ul>
  
  <p><strong>Recipe</strong> with following attributes:</p>
  
  <ul>
  <li>RecipeID &lt;&lt; index of Recipe</li>
  <li>ProductID &lt;&lt; multiple productID and are/ can be duplicate</li>
  <li>ingredientID &lt;&lt; multiple ingredientID but are unique for specific productID</li>
  <li>productAsIngredientID &lt;&lt; sometimes product itself can act as an ingredient. eg. Bun (it can be sold independently as a bun or can be added to Recipe of Burger)</li>
  <li>ingredientAmount&lt;&lt; Quantity (double/int)</li>
  </ul>
  
  <p><strong>Ingredients</strong> with following attribute:</p>
  
  <ul>
  <li>ingredientID &lt;&lt; it is the list of raw ingredients, eg (flour)</li>
  <li>ingredientName</li>
  <li>ingredientUnitType &lt;&lt; type of unit it holds (eg. gm, ml, etc)</li>
  </ul>
  
  <p>Sampel database for the tables above:</p>
  
  <p>PRODUCT:</p>
  
  <pre><code>|   ProductID    |    ProductName    |   ProductPrice   | 
  
         12             Bun                     1.5
         15             Ham Burger              5
         13             Chicken Burger          7
  </code></pre>
  
  <p>INGREDIENT:</p>
  
  <pre><code>|    ingredientID    |      ingredientName         |   ingredientUnitType   |          
  
           1                   Salt                           gm
           2                   Yeast                          gm
           3                   Refined Wheat Flour            gm
           4                   Milk                           ml
           5                   Chicken Meat                   gm
           6                   Onion                          gm
           7                   Tomatoes                       gm 
  </code></pre>
  
  <p>RECIPE:</p>
  
  <pre><code>| RecipeID | ProductID | ingredientID  |  productAsIngredientID  |  ingredientAmount  |
  
       1          13                               12                         1
       2          13           5                                             20
       3          13           6                                              7
       4          13           7                                             10
       5          12           1                                              5
       6          12           2                                              2
       7          12           3                                             10
       8          .            .
       9          .            .
  </code></pre>
  
  <p>in Recipe Table, ProductID can not be equal to productAsIngredientID in a same row. But in table Recipe, productID and productAsIngredientID both links to Product.ProductID.</p>
  
  <p>However there is a problem in linking these the above two tables with productID. If I linked them, none of them can have null values or any values that is not in ingredients or Product Table.</p>
  
  <p>I am using Microsoft Access (MDB) as a database 
  Please suggest me a right way to get this done. How can I organize the Product item itself as a type of ingredient itself.</p>