recipes and weight conversions

[ricky_vegas]

I need to design a db to store recipes, ingredients, and cost for each recipe.
For example, I buy 1 lb of butter at $4.50 Then for my recipe #1 I use 1 cup of butter and for recipe #2 I use 1 tablespoon of butter. To complicate this project a little more, I have dry and liquid items. So, some of the ingredients need to be converted in fl.oz. other in lbs. What's the best way of doing it?

I started with the following tables but I soon got stuck:

tblIngredients (ID, IngredientName, ???)
tblRecipes (ID, RecipeName)
tblCategories (ID, CatName)
tblDishes (RecipeName-IngredientName-CatName,??? )

Anyone can give me a tip on how to proceed?

Thanks so much.

Rick

 

[David Eagar]

To do this properly is going to be quite a task. You will need a Conversion table and in your Ingredient table you will need a Purchase Unit and Purchase Price

In the Conversion table you will need all possible recipe units and conversion factor per ingredient, so as per your example, for Butter PU = lb and PP = $4.50

Cup = x lbs
Tablespoon = y lbs
Teaspoon = z lbs

etc etc

 

[GaryPanic]

you need to standise your weights -
a cup is 8oz?s

anyway
if you sitck to metric it will help out

250grames is a block of butter
etc

you really problem will be eggs
when you come to aggregate them - as these are still sold by the dozen - or by the tray/case depeding on how many you use

 

[David Eagar]

you really problem will be eggs
when you come to aggregate them - as these are still sold by the dozen - or by the tray/case depeding on how many you use

If the standard was Each, problem would be averted - this would apply to many other ingredients, vegetables, cuts of meat etc

 

[David Eagar]

Actually, now comes the heart of the matter - how much of this is for the purpose intended?

To be truly accurate, you will need day to day prices for your ingredients, what you pay today for a T Bone steak wont be what you pay tomorow

So, have have a seperate Ingredient Price table and store history and run queries to calculate averages over specified time period OR

Get back to actually cooking the recipes and not spend all your time in maintaining the data

If this is an Access exercise, its a good one to get to grips with all the permutations of what you set out to do and where that can lead to (and the structure rebuilds required to make it happen)

If this a practicle working model, maybe less navel gazing is required - If market prices change day to day, are menu prices also going to change on a daily basis? I think not

 

[GaryPanic]

from the looks of it -its an assignment(and not an easy one at that)

 

[ricky_vegas]

thanks everyone. I figured that it was not going to be an easy task. I'm gonna try to create the tables as suggested by David and see how that works.
Each recipe is going to cost between $1.50-4.00 and on the menu the items will be sold at $30-60 so I'm not very concerned if the market prices change every day.
I'm sure I'll have more questions so I count on you all.
Thanks
Rick