Solved

Design / Sql Query options

Posted on 2011-02-16
8
348 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Email Header Detail 12 55
Help Required 3 97
Need help how to find where my error is in UFD 6 30
why sql server only update some statistics in the database ? 3 22
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

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