Solved

Design / Sql Query options

Posted on 2011-02-16
8
349 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
  • 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 32

Accepted Solution

by:
ewangoya earned 500 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:ewangoya
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

809 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