Simple and right way to link and add columns to database tables, database design

Question

I have following tables.

Product with following attributes:

• ProductID << ID of the product
• ProductName << name of product
• ProductPrice << Price of product

Recipe with following attributes:

• RecipeID << index of Recipe
• ProductID << multiple productID and are/ can be duplicate
• ingredientID << multiple ingredientID but are unique for specific productID
• productAsIngredientID << 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)
• ingredientAmount<< Quantity (double/int)

Ingredients with following attribute:

• ingredientID << it is the list of raw ingredients, eg (flour)
• ingredientName
• ingredientUnitType << type of unit it holds (eg. gm, ml, etc)

Sampel database for the tables above:

PRODUCT:

|   ProductID    |    ProductName    |   ProductPrice   | 

       12             Bun                     1.5
       15             Ham Burger              5
       13             Chicken Burger          7

INGREDIENT:

|    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 can not be equal to productAsIngredientID in a same row. But in table Recipe, productID and productAsIngredientID both links to Product.ProductID.

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.

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.

Answer 1

What you are asking for is how to create many-to-many relationships between tables.

This is done by a separate "link table" which contains the Id from two other tables (and in your case we will add another column to the ingredient link table, the amount.

So the schema would look like this (I prefer attributes without the tablename as prefix):

Product

• Id
• ProductName
• Price
• Unit

Ingredient

• Id
• IngredientName
• Unit

Recipe

• Id
• RecipeName

RecipeProducts

• RecipeId (foreign key: Recipe.Id)
• ProductId (foreign key: Product.Id)
• Amount

RecipeIngredients

• RecipeId (foreing key: Recipe.Id)
• IngredientId (foreign key: Ingredient.Id)
• Amount

That's how I would normally layout the schema, but I am not that familiar with Access.

This is what I get from the access query wizard to get all Ingredients for a recipe

SELECT Ingredient.IngredientName, RecipeIngredients.Amount, Ingredient.Unit
FROM Ingredient 
INNER JOIN RecipeIngredients 
ON Ingredient.[ID] = RecipeIngredients.[IngredientId] 
WHERE RecipeIngredients.RecipeId = 1;

Not the best SQL and I suppose someone can provide a smarter way to get both products and ingredients for a recipe.

the productAsIngredientId should not be necessary, since when a product is a part of a recipe, it will be an ingredient, right? otherwise you need to change your table names to something more logical.