[mythtv] mythrecipe

Christopher Flynn flynnguy at gmail.com
Tue Nov 9 16:28:50 UTC 2004


Ok, I'm working on trying to get the tables setup and I am still
struggling on the ingredients. Well here is what I have so far:

-- Recipe table
-- all the misc info for the recipe
-- picture_url would be an option picture for the recipe
create table recipe (
recipe_id INT unsigned primary key auto_increment, 
name varchar(255), 
user_id INT unsigned, 
source varchar(255), 
description TEXT, 
servings smallint unsigned, 
serving_unit varchar(255), 
-- I left out the serving_extra unless you can figure out what it is
prep_time smallint unsigned, 
cook_time smallint unsigned, 
created TIMESTAMP, 
modified TIMESTAMP,
picture_url varchar(60),
directions TEXT
);

-- Users table
-- user_id and login are probably redundant but we'll leave for now
-- use a md5 encryption and store as varchar(32)
-- can do something like SELECT MD5('pass')
-- I'm assuming admin is just a flag
create table users (
user_id INT unsigned primary key auto_increment,
login varchar(20),
passwd varchar(32),
admin bool,
name varchar(255),
email varchar(255),

UNIQUE(login),
UNIQUE(email)
);

-- Rating matches a recipe and a user to a rating. 
-- One rating per user per recipe. 
-- Should be easy to match on recipe_id && user_id
-- Comments would allow individual users to add their own comments.
create table rating (
recipe_id INT unsigned,
user_id INT unsigned,
rating INT(5),
rating_comments TEXT
);

-- Parent category allows for a linked list type of entry
create table categories (
category_id INT unsigned primary key auto_increment,
parent_category_id INT unsigned,
description varchar(255)
);

-- category_lookup would link a recipe to a category. There can be
multiple entries per recipe.
create table category_lookup (
recipe_id INT unsigned,
category_id INT unsigned
);

-- Ingredient information
-- season describes when the ingredient is typically ripe/available
-- I would like to be able to search by this field to help with seasonal cooking
-- selecting would provide tips on selecting a good one.
-- preparation would outline the different ways the ingredient is prepared
-- substitutions would list possible substitues
create table ingredients (
ingredient_id INT unsigned primary key auto_increment,
name varchar(255),
season varchar(255),
selecting text,
preparation text,
substitutions text,
unique(name)
);

Ok, the above is what I was thinking of to store for the ingredients
similar to recipezaar. We could even add a picture_url field to have
pictures of the ingredients. Now on to the troublesome bit...

create table recipe_ingredients (
recipe_ingredient_id INT unsigned primary key auto_increment,
recipe_id INT unsigned,
qty float(10,4) unsigned,
unit varchar(255),
totaste float(10,4),
name varchar(255),
INDEX(name),
INDEX(recipe_id)
);

Ok, this is basically what Chris already has. Now are you just
matching on the name field and matching that to the ingredient table?
So if name='lemon or lime' you would search through that field for
possible ingredients and then see we have a lemon and a lime and then
we could put tags around them to link them to an ingredient
information page? Also Chris, how do you feel about the totaste
multiplier?

Also later I will start to look at the nutritional info and see how we
could incorporate that into the table. My feeling is that we should be
able to put it in the ingredient table but it may be easier to just
put it in it's own table and then join the new table with the
ingredient table.
-Chris
-- 
You must be the change you wish to see in the world. - Gandhi


More information about the mythtv-dev mailing list