RE: Help with database Design

[Jeff Snoxell]

> 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