You're right. I have already posted a 3 tables solution: recipes, ingredients and recipeingredients.
Adolfo On Sun, 2002-12-22 at 14:10, Jeff Snoxell wrote: > > > I'm designing a delivery program with php and mysql, and I'm > > > having troubles with some tables, my problem is with the > > > recipes, the recipes are divided into two tables one with a > > > recipe id and description the other detailing the > > > ingredients, of each particular recipe, that's where my > > > problem starts because each ingredient is not unique and even > > > it appears in many recipes, and another thing is every time > > > and item is ordered their correspondent recipe ingredients > > > are added , ie. pizza has a code 11, recipe has code 01 then > > > all the ingredients in the recipe table have different > > > ingredient code each, and are related by the recipe code 01, > > > then each separate item must be counted, and added to the > > > ingredients general daily totals, I mean pizza uses 2 > > > tomatoes then the general total will show two tomatoes and > > > each other ingredient. There are also some combined recipes : > > > Pizza with a special sauce, then this item has to query two > > > parts of the same table, say code 12 is Pizza with a combined > > > sauce of mushrooms and milk, and pineapples, so each > > > ingredient must be added to the general total. I need help > > > with the design because I don't want to have database anomalies. > > > > > -----Original Message----- > > > From: Ricardo Fitzgerald [mailto:[EMAIL PROTECTED]] > > > Sent: Sunday, December 22, 2002 11:22 AM > > > To: [EMAIL PROTECTED] > > > Subject: Help with database Design > > > > > > > >create table recipes( > >rid int not null primary key, > >rname ....) ; > > > >create table ingredient( > >iid int not null, > >rid int not null, # references recipe.rid > >ridorder int not null, # order of this ingredient in recipe > >PRIMARY KEY(iid,rid,ridorder), > >iname ...) ; > > Your solution means a repetition of ingredients in the "ingredients" table. > Then, if the unit cost of an ingredient changed you'd have to change every > occurance of the ingredient... and where would you store info like the > source manufacturer, tel and address etc etc. How about: > > table meals( > m_id primary, > m_name, etc etc > ) > > table ingredients( > i_id primary, > i_name, > i_price, etc etc > ) > > table recipes( > r_id primary, > m_id, #(the meal ident) referencess meals, there will be multiples of these > ) > > OR a really nasty way to do it with 2 tables...... > > table meals( > m_id primary, > m_name, > m_ingredients, # a comma separated list of ingredient ID's in a text field > ) > > table ingredients( > as above > ) > > so then you can read in a list of ingredient id's for each recipe, then > 'look up' the ingredients. > > > Jeff Snoxell > Aetherweb Ltd > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > -- Adolfo Bello <[EMAIL PROTECTED]> --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
