Re: Help with database Design
Ricardo, you're asking how to normalise your database. There are links to some good intro tutorials at http://www.artfulsoftware.com/dbresources.html PB - Hi, I'm having problems trying to figure which is the best way to solve a design : 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. Thank you all, Merry Xmas Rick - 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
Re: Help with database Design
Ricardo, you're asking how to normalise your database. There are links to some good intro tutorials at http://www.artfulsoftware.com/dbresources.html PB - [filter fodder: sql, mysql] Hi, I'm having problems trying to figure which is the best way to solve a design : 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. Thank you all, Merry Xmas Rick - 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
RE: Help with database Design
-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 ...) ; HTH, Adolfo Hi, I'm having problems trying to figure which is the best way to solve a design : 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. Thank you all, Merry Xmas Rick __ Omni ICQ#: 37031810 Current ICQ status: + More ways to contact me __ - 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 - 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
RE: Help with database Design
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
RE: Help with database Design
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
