Re: [Cronometer-development] DB Schema extension

[Chris R. <of...@gm...>]

On 5/12/05, Aaron Davidson <ada...@po...> wrote:
> Funny, I was JUST about to post my thoughts for handling recipes.
>=20
> 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.
>=20
> 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).
>=20
> RECIPE_INGREDIENT_MAPPING (
>         INTEGER Food_ID FOREIGN KEY
>         INTEGER Weight_ID FOREIGN KEY
>         REAL Quantity
> )
>=20
> > 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.

The only problem with this is that, while it does help keep a recipe a
conceptual food, it also makes it harder to add things like
descriptions and preparation instructions -- which, while not strictly
in the purview of this app, would be nice.  Maybe make it a three-step
link -- keep the recipe table, but it'll lose the name -- that will go
into food, and it will instead have a foreign key into the food table
to link it there.  I say this because recipies, while sort of foodly,
are really not the same thing.

Also, where are we going to calculate these values?  I guess we can do
it on loading of the recipe -- but just imagine the number of DB calls
we'll have!  Also, what about the food editor for a recipe?  Suppose
you modify the sum of the caloric values?  What happens to the
sub-foods?

Realistically, the more I think about it, the more I believe that we
would be best off keeping a separate table of recipes, and then in the
daily list, programmatically merging them, but keeping them separate
for purposes of additions -- maybe make recipes a new data source?=20
Something like it, anyway.
=20
> 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.

I concur.

> 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.
>=20
> I'm not sure what we should do here....ideas?

I was thinking about this, too.

Perhaps we should add a "visible" tag to the foods table?

The more we add to this, the more certain I become that my original
idea of mixing the My Foods and USDA into one table will cause more
trouble than it's worth.  The personalized foods, while similar, have
so many more operations to support that it's not really worth it --
and then we can optimize the hell out of the USDA one with indices all
over the place...

Since the datasource abstracts that anyway, changes to the underlying
schema are not important to the UI -- but I think maybe we need to do
lunch next week and go over these.  What say you to Monday?  Tomorrow,
even, but I know you guys do builds on friday, right?

--=20
Chris R.
=3D=3D=3D=3D=3D=3D
Not to be taken literally, internally, or seriously.