I've following tables.

Product with following characteristics:

  • ProductID << ID from the product
  • ProductName << title of product
  • ProductPrice << Cost of product

Recipe with following characteristics:

  • RecipeID << index of Recipe
  • ProductID << multiple productID and therefore areOr could be duplicate
  • ingredientID << multiple ingredientID but they are unique for specific productID
  • productAsIngredientID << sometimes product itself can behave as an component. eg. Bun (it may be offered individually like a bun or can be included to Recipe of Hamburger)
  • ingredientAmount<< Quantity (double/int)

Elements with following attribute:

  • ingredientID << it's the listing of raw elements, eg (flour)
  • ingredientName
  • ingredientUnitType << kind of unit it holds (eg. gm, ml, etc)

Sampel database for that tables above:

PRODUCT:

|   ProductID    |    ProductName    |   ProductPrice   | 

       12             Bun                     1.5
       15             Ham Burger              5
       13             Chicken Burger          7

Component:

|    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 

RECIPE:

| 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          .            .

in Recipe Table, ProductID cannot be comparable to productAsIngredientID inside a same row. However in table Recipe, productID and productAsIngredientID both links to Product.ProductID.

However there's an issue in connecting these the above mentioned two tables with productID. Basically linked them, not one of them might have null values or any values that's not in elements or Product Table.

I'm using Microsoft Access (MDB) like a database Please suggest us a proper way to accomplish this. How do i organize the merchandise item itself as a kind of component itself.