Design / Sql Query options

Using MSSQL 2008R2 , I am looking for suggestions on how to best handle this situtation.

Example of recipes and ingredients.

I have recipes table that list recipe_id, ingredient_id that links to a Ingredient table that has current price.

However, some recipes ingedients are really "sub-recipes" that are listed in teh same recipe table.

My 2 questions:

1) How to handle recipes that have multiple "sub-recipes"?
2) How to best handle previous costs, I want to compare costs of ingredients between today and 1 year ago? Currently, my ingredient table lists on last bought price.

Thanks,
freshgrillAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Ephraim WangoyaConnect With a Mentor Commented:

For sub recipes, you can add a new field to the table called ParentRecipe_id

recipe_id, ParentRecipe_id, ingredient_id

For main recipes, the parentid is zero, for sub recipes, the parent id is the recipe id of the main recipe
Make sure to index this field

To compare price changes, yo will need a new child table with the fields
Ingredient_id, Cost, LoggedDate

Whenever a price changes, add a new record to the table with the current cost. With this data, not only can you query the difference between last years cost but you have enough data to see the cost trend for a particular ingredient
0
 
Alex MatzingerDatabase AdministratorCommented:
To best handle last years cost, add a new column to the ingredients table named Last_Years_Cost.  

Alter table ingredients ADD Last_Years_Cost float
0
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
for sub recipes, you can do it a couple of ways, 1) self-join or by using a childtable with references to parent/child recipes

For you situation I would go with door #2.

create a table (subrecipe) with the following fields

key_id  field
parent_recipe_id
child_recipe_id

Using this, you can query this table joined to the recipe table and retrieve any child records you may have. something like the following, it may return more than 1 row, and then you would need to walk each row to extract the sub recipes.

SELECT recipe.*, s.*
    FROM recipe
    JOIN subrecipe x ON x.parent_recipe_id = recipe.recipe_id
    JOIN recipe s ON x.child_recipe_id = s.recipe_id
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Alex MatzingerDatabase AdministratorCommented:
For sub recipes i would suggest braking them off into completely separate recipes.  So lets say you have one main recipe that calls for a vanilla frosting recipe well that vanilla frosting recipe should be listed in your table as a main recipe as well.

Then you may think about adding a table to your system labled Sub_recipes which links a main recipe to a sub recipes.  Sub_recipes would simply be a table of 2 recipe_id's labeled main_id and sub_id.  

This would allow you to:
select *
from recipes 1, recipes 2,  sub_recipes 3, ingredients 4
where 1.id = 3.main_id AND 2.id = 3.sub_ID AND (4.ingredient_id = 1.ingredient_id OR 4.ingredient_id = 2.ingredient_id)

which should return all recipes and sub recipes with all ingredients
 
0
 
freshgrillAuthor Commented:
Clarifications:

1) Sub-recipes could go multiple layers deep, recipe -> subR ->subR.  i.e. a subR can have a subR itself.

2) I don't want to just last years cost, I want to run reports based on time frame dynamically. i.e What did the recipe cost in Jan. versus July.
0
 
Alex MatzingerDatabase AdministratorCommented:
for cost:
prob best to add a enw table labled ingredients_cost with fields ingredient_ID, date, cost.
Every time you buy the ingredient, you add the date and cost of that ingredient to the table.

0
 
Ephraim WangoyaCommented:

#34910256
The kind of structure I showed (recipe_id, ParentRecipe_id, ingredient_id) will support sub recipes up to any level you want


0
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
in response to sub-recipes could go mulitple layers deep.

The child recipe table is the way to go then.

You will just need to create a stored procedure that will return all the geneology of the parent recipe.
0
All Courses

From novice to tech pro — start learning today.