|
|
Posted by David Haynes on 04/17/06 22:31
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:
Recipe:
1, 'Chinese Chicken', 'A neat recipe that is easy to make', ...
Ingredient:
1, 'Garlic'
2, 'Chicken'
3, 'Salt'
4, 'Pepper'
Quantity:
1, 1, 1, 'clove'
1, 2, 1, 'lb white meat'
1, 3, 1, 'tsp'
1, 4, 1, 'tbsp'
Then, let's say you have another recipe that calls for salt.
Recipe:
2, 'Mashed Potatoes', 'Light and fluffy mashed potatoes'
Ingredient:
5, 'Potatoes'
6, 'Buttermilk'
Quantity:
2, 3, 1, 'tbsp'
2, 5, 2, 'lbs cubed'
2, 6, .25, 'cup'
2, 4, 0, 'to taste'
etc.
-david-
Navigation:
[Reply to this message]
|