Re: [Cronometer-development] DB Schema extension

[Aaron D. <ada...@po...>]

Funny, I was JUST about to post my thoughts for handling recipes.

Basically, I was thinking that a Recipe, a collection of food measures, 
generates a new Food Item. The only thing that is unique about a recipe 
food is that there are extra tables for that food item, detailing the 
recipe. When the recipe is edited, the Food's nutrient values are 
recomputed from the ingredients. I think this fits in nicely with what 
you outline below. In fact, maybe you already were thinking exactly 
this, but It wasn't clear from your post.

I think I would just get rid of the RECIPE table -- instead we have a 
FOOD entry. Instructions can be stored as a food comment (which we 
haven't implemented yet of course).

RECIPE_INGREDIENT_MAPPING (
	INTEGER Food_ID FOREIGN KEY
	INTEGER Weight_ID FOREIGN KEY
	REAL Quantity
)

> And a modification to the Weight table giving it a unique Weight_ID
> primary key for reference purposes, maintaining the foreign key
> relation into Food.  This relation means that a particular unit of
> weight is sufficient to identify a Food in the recipe, as well as make
> for more readable recipes on the client side.

We should make this change to the CONSUMED table as well. Right now it
just stores the gram amount, but it might be friendlier to also store
the Measure as well (but it's safest to keep grams in case the Measure
gets deleted, it can still function). Also we should not forget to 
change
the table names to MEASURE and SERVING, to minimize confusion down the 
road.

Another use-case I was thinking about that makes me slightly 
uncomfortable. Right now, I might click on USDA, search and add a 
banana. Then a few weeks from now, imagine I accidentally add a banana 
again from USDA, instead of from MyFoods. This adds banana again to 
MyFoods, so now if I search in MyFoods, I'll see two banana entries 
show up.

A typical user will select one of the two and delete one. Now what do 
we do then? We don't want the information for their Servings to be 
lost. Actually, this is an issue for any deletion of a Food. If they've 
consumed that food in the past, deleting it from MyFoods will throw off 
all their past records.

I'm not sure what we should do here....ideas?