Posted by David Haynes on 04/17/06 23:15

news@celticbear.com wrote:
> 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
>
Glad to help.

The 'Quantity' table is better known as a 'many-to-many' join table and
is used quite a lot in database schema design. It is called
'many-to-many' since a recipe may use many ingredients and an ingredient
may be used in many recipes.

-david-

[Back to original message]