?
Solved

Design / Sql Query options

Posted on 2011-02-16
8
Medium Priority
?
352 Views
Last Modified: 2012-05-11
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,
0
Comment
Question by:freshgrill
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 34910149
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
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 34910206
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
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 34910222
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 2000 total points
ID: 34910256

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
 

Author Comment

by:freshgrill
ID: 34910263
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
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 34910284
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
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34910399

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


0
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 34910413
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question