Posted by news@celticbear.com on 04/17/06 22:52

David Haynes wrote:
> news@celticbear.com wrote:
> > Sjoerd wrote:
> >> news@celticbear.com wrote:
> >>> need to have DB fields in mySQL that
> >>> will have lists of values
> >> Typically, one would use escape characters. For example, | would be
> >> used to seperate two fields. A literal | would be escaped: \|. This
> >> makes it possible to use the seperation character in the data.
> >>
> >> Another, probably better option, would be to make a seperate table.
> >> Instead of storing the ingredients seperated by a character like this:
> >> 1, Chinese Chicken, chicken|salt|pepper|mushrooms
> >> Think about making a ingredients table and storing it there:
> >>
> >> [Recipes]
> >> 1, Chinese Chicken
> >> 2, Meatloaf
> >>
> >> [Ingredients]
> >> 1, chicken
> >> 1, salt
> >> 1, pepper
> >> 1, mushrooms
> >> 2, meat
> >> 2, loaf
> >>
> >> This makes it easy to search and it is more logical from a DB point of
> >> view.
> >
> > Well, since you brought it up, speaking of the DB, that's kind of what
> > I had in mind, but, differemt.
> > The tbl_ingredients would have two columns, ID and NAME like:
> > 1, garlic
> > 2, chicken
> > 3, pepper
> > 4, salt
> > etc...
> >
> > Then the tbl_recipes would have colums like:
> > ID, NAME, INGREDIENTS, MEASUREMENTS, NOTES
> > with data like:
> > 1, Chinese Chicken, 1|2|3|4, 1 clove|1 lbs. breast| 1 tsp.| 1 T., more
> > stuff here....
> >
> > Where the ingredients would be the ingredient's ID in a CSV, (but
> > obviously using something other than a comma,).
> > That's the best I could come up with to be able to have a list of
> > unknown and constantly changing number of ingredients that would save
> > space in the table.
> >
> > The problem I'm seeing with your suggestion is that the [ingredients]
> > table would have a lot of redundant info.
> > Every recipe that required pepper would have a row with a recipie ID
> > and a value of "garlic," which would be repeated in the database n
> > number of times.
> >
> > I suppose of course, if the database remains small, that amount of
> > redundant data isn't going to hurt performance at all.... and would
> > possibly be easier to manage....
> >
> > Thanks for the reply and the feedback!!
> > -Liam
> >
>
> How about this instead?
>
> Three tables: Recipe, Quantity, Ingredient
>
> Recipe
> ID, NAME, DESCRIPTION, AUTHOR, ...
>
> Ingredient
> ID, DESCRIPTION
>
> Quantity
> RECIPE_ID, INGREDIENT_ID, AMOUNT, UNIT
>
> That way you could have:
>
> [..]
> -david-

Huh, OK. I hadn't thought of it that way. Intuitively it wouldn't seem
like you'd want to have the unique data that makes up the components of
the recipe in a "quantity" table as opposed to the table that contains
the recipe name and details, etc.
That erroneous thinking is what led me to the silliness of trying to
shove multiple data values into one field.
This solves (makes irrelevant) my array/CSV problem.
Thanks for the clue-by-four!
-Liam

[Back to original message]