[mythtv] mythrecipe

[Christopher Flynn]

On Tue, 09 Nov 2004 01:09:46 -0800, Chris Petersen
<lists at forevermore.net> wrote:
> >>1 cup plus 2 tbsp flour
> > for this scenario I would have either split this up into 2 ingredients or done:
> > qty|amt|ingredient
> > 1 | cup | plus 2 tbsp flour
> 
> No.  it would be stored internally as 1.125 cups and parsed into a
> human-readable size upon display (there are 16 tbsp in a cup).  My
> example was more to show the kind of things you would need to parse (for
> input) and display.
Ok, I see what you are doing. How would you handle something like 1-2
cups flour? I was describing how I would have set it up. I did set up
a very basic recipe program that did things like above but it was more
to just store and display the information than anything else.

 
> >>1 squeeze of lemon or lime
> > for this scenario I would have squeeze be the amount and then lemon or
> > lime as the ingredient.
> 
> squeeze is a unit, yes.  and there would be two separate ingredients.
> You wouldn't want to create an ingredient called "lemon or lime" --
> you'd want to link both to "lemon" and to "lime".
Ok, so you have the following for an ingredient:
CREATE TABLE ingredients (
    id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    parent_id   INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(255),
    summary     TEXT,
    text        TEXT,
    UNIQUE (name)
);
Ok, id is just a unique id for this item but what is parent_id? Is
this so we can link lemon and lime together? I'm also guessing name is
just the name of the item (ie. lemon) summary would be a description
of said item (ie. Yellow fruit) and then text would be what? At first
I thought it would be something like squeezed but would we want to
have a seperate ingredient for each way to prepare it? Like would we
have an onion entry for sliced, thickly sliced, diced, chopped,
minced, cubed, etc... ? Also have you looked at the nutritional value
aspect of it? I really like that feature in recipezaar but I can only
imagine how complicated it would be. I did a quick search and found
the USDA Nutrient Database for Standard Reference here:
http://www.nal.usda.gov/fnic/foodcomp/Data/SR17/sr17.html This is how
recipezaar calculates there information:
http://www.recipezaar.com/help/nutrition.zsp However like they say,
this is still just an approximation and there are some things that
will trip it up but I like the idea of implementing something like
this.


> > Of course I would be open to other suggestions because if we have some
> > sort of scale function it would probably get messed up. Of course I
> > don't know if there would be a way to make something that would scale
> > in all cases.
> 
> I have all of this done in php already.  It's borked at the moment
> because I made some semantic changes to make it easier to expand (better
> objects), but wouldn't be too hard to fix.
Ok, I think the first priority should be to get your web based version
setup and then once that is up and running we can start to look into
interfacing it with mythtv.

> > For the category but, I guess have a main category and then a
> > subcategory field. Sound good?
> 
> Basically, each category is a node.  There is one parent node, and when
> you create a new node, you tell it where its parent is.  Easy to do, and
> you can do some magic to make multi-homed categories, it's pretty cool.
Ok, so like a linked list?
 
> >> - table of recipes, linked with recipe-ingredients
> > So like a lookup table that has a recipeID and an ingredient?
> 
> one table for recipes, one for ingredients.  one record per recipe, one
> per ingredient.  ingredients link to recipe_id.
So in your recipe table you want to use the ingredient field to store
ingredients in a list? (Kindof like how mythmusic stores music ids in
it's playlists) I don't like storing information this way because if
you want to delete one ingredient, you have to read them all, parse
them and figure out which one to delete and then write them back. I
think it would be better to just have say one table called ingredient
lookup that looks something like:

create table recipe_lookup (
     recipe_id INT UNSIGNED
     ingredient_id INT UNSIGNED
)
this way if we have 2 ingredients with id's of 1 & 2 in a recipe who's
recipe id is 1 we would have two entried:
(1,1)
(1,2)
then to delete the second ingredient, we would just do something like:
delete from recipe_lookup where recipe_id=$CURRENT_RECIPE$ AND
ingredient_id=$INGREDIENT_TO_DELETE$.

Also maybe if I have time I will look into mysql's innoDB type and see
how to set it up because it seems they are working in fun features
like "on delete cascade" and other transactional stuff.
 
> >> - a category tree not directly linked to recipes (trust me, it's easier
> >>     to manage this way), but assigned keywords.  Recipes matching those
> >>     keywords will show up under that category.
> > This may be easier in terms of data entry but invites a whole lot of
> > other complications. First the queries would take longer than if we
> > have to search through keywords rather than just pull out the
> > information.
> 
> It's negligbly slower.  All keywords are in a related table, so you're
> comparing numerical entries.  It might get slow when you start getting
> in to the tens/hundreds of thousands of records per keyword, but with
> anything reasonable, especially with query caching, it shouldn't be a
> problem (I've written systems far more complex than this and there's no
> issue -- heck, the program listing query in mythweb is worse)
So how exactly do you want to do this? Search on keywords and then map
certain keywords to a category? If we do the linked list idea for
categores we could use another lookup table that would just store the
parent node and the recipe. That would allow for an unlimited ammount
of categories per recipe. It would also allow you to setup your
categories however you want.

What I'm envisioning is something like:
create table categories (
     category_id INT unsigned primary key autoincrement,
     parent_node INT unsigned,
     label varchar(60)
) 
parent_node would either be NULL or link to another category_id and
the label would be something like "Main Dish" or "Region." Then the
category lookup table would just link a recipe_id to a category_id.

> > I understand you are busy moving into your new house, I too will be
> > closing on my first house soon so this may be a little slow going.
> 
> Slow is good for me -- nuvexport and mythweb need attention before I can
> put much effort into this, too.
Understandable, thanks for your help in all of this, you've figured
out some things that were a stumbling block for me before. I also
noticed in your sql directory you have brew.sql that looks
suspiciously like homebrewing stuff ;-) You homebrew I assume then?
Perhaps we could eventuall tack on a brewing recipe/calculator addon
to the web page. I myself am saving and eventually planning on jumping
into an all grain setup. However that will be after the house. ;-)
-Chris

-- 
You must be the change you wish to see in the world. - Gandhi