Recipes with ingredients with suppliers

Ask for Help
1

I run a snack business, where I make snacks and sauces and spice mixes which I sell. I want to make an app where a recipe has N ingredients (each with an amount: X gr of mayonnaise, Y ml of vinegar). Ingredients are in a separate table, to which the lines must link. In the ingredients table, the stock is given as well as a minimum amount. Each ingredient has a price, which defines the cost of production. Each ingredient has a supplier. Suppliers are in a separate table, and each supplier may supply multiple ingredients (rolled oats, raisins, &c). I’ve tried different strategies with the AI, as ‘recipes with ingredients’ doesn’t float (so I’ve tried clubs with members), but each time what I get is not relational, but just tables (set of clubs with N members; and people who have no apparent relationship with any clubs). What am I doing wrong?

2

Not sure what you mean here, do you mean you’re trying to ask AI what structure should you get?

Here’s my suggestion.

1. Ingredients Table

ingredient_id name stock_quantity minimum_stock cost_per_unit supplier_id
I001 Mayonnaise 5000 2000 0.5 S001
I002 Vinegar 2000 500 0.2 S002
I003 Chili Powder 1000 200 1.0 S001

2. Suppliers Table

supplier_id name contact_info
S001 Supplier A +1-234-567-8901
S002 Supplier B +1-987-654-3210

3. Recipes Table

recipe_id name
R001 Spicy Sauce
R002 Salad Dressing

4. Recipe_Ingredients Table

recipe_ingredient_id recipe_id ingredient_id quantity
RI001 R001 I001 200
RI002 R001 I002 50
RI003 R001 I003 10
RI004 R002 I001 100
RI005 R002 I002 30

How Tables Relate

  1. supplier_id in Suppliers Table links to supplier_id in Ingredients Table.
  2. ingredient_id in Ingredients Table links to ingredient_id in Recipe_Ingredients Table.
  3. recipe_id in Recipes Table links to recipe_id in Recipe_Ingredients Table.

Does that answer your question?

2 Likes