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)
- ingredientUnitType << kind of unit it holds (eg. gm, ml, etc)
Sampel database for that tables above:
| ProductID | ProductName | ProductPrice | 12 Bun 1.5 15 Ham Burger 5 13 Chicken Burger 7
| 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
| 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.