Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Design / Sql Query options

Posted on 2011-02-16
8
Medium Priority
?
353 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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: 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.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

597 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