diet: tables

table: foodItem

This is the generic type, like "chicken", which always has a single food group and generally doesn't change with brand.

DROP TABLE IF EXISTS foodItem; CREATE TABLE foodItem ( id int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(50) NOT NULL, description varchar(255) NULL, servingSizeAmount float(5,3) NULL, foodServingSizeUnitId int(10) NULL, calories float(5,1) NULL, carbohydrates float(5,1) NULL, fatSaturated float(5,1) NULL, fatUnsaturated float(5,1) NULL, fiber float(5,1) NULL, protein float(5,1) NULL, cholesterol float(5,1) NULL, sodium float(5,1) NULL, calcium float(5,1) NULL, iron float(5,1) NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

table: foodItemTag

DROP TABLE IF EXISTS foodItemTag; CREATE TABLE foodItemTag ( id int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(50) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; // meat INSERT INTO foodItemTag (name) VALUES ('beef'); INSERT INTO foodItemTag (name) VALUES ('chicken'); INSERT INTO foodItemTag (name) VALUES ('fish'); INSERT INTO foodItemTag (name) VALUES ('lamb'); INSERT INTO foodItemTag (name) VALUES ('pork'); // carbs INSERT INTO foodItemTag (name) VALUES ('pasta'); INSERT INTO foodItemTag (name) VALUES ('potato'); INSERT INTO foodItemTag (name) VALUES ('rice'); // vegetables INSERT INTO foodItemTag (name) VALUES ('asparagus'); INSERT INTO foodItemTag (name) VALUES ('bellPepper'); INSERT INTO foodItemTag (name) VALUES ('carrot'); INSERT INTO foodItemTag (name) VALUES ('celery'); INSERT INTO foodItemTag (name) VALUES ('chickpea'); INSERT INTO foodItemTag (name) VALUES ('corn'); INSERT INTO foodItemTag (name) VALUES ('cucumber'); INSERT INTO foodItemTag (name) VALUES ('eggplant'); INSERT INTO foodItemTag (name) VALUES ('garlic'); INSERT INTO foodItemTag (name) VALUES ('greenBeans'); INSERT INTO foodItemTag (name) VALUES ('lentil'); INSERT INTO foodItemTag (name) VALUES ('onion'); INSERT INTO foodItemTag (name) VALUES ('pea'); INSERT INTO foodItemTag (name) VALUES ('peanut'); INSERT INTO foodItemTag (name) VALUES ('spinach'); INSERT INTO foodItemTag (name) VALUES ('tomato'); INSERT INTO foodItemTag (name) VALUES ('zucchini');

table: foodSearchItem

DROP TABLE IF EXISTS foodSearchItem; CREATE TABLE foodSearchItem ( id int(10) unsigned NOT NULL AUTO_INCREMENT, foodItemTagId int(10) NOT NULL, foodItemId int(10) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

table: foodPackage

A food-package is a branded version of a food-item. Strictly speaking, this is the list of food that we'll be using. The food-item table is just used as a way of genericizing this when all the brands are really the same, nutritionally. If any of the nutritional values (including serving size) are null, then the value defaults to the associated food-item's value, if there is one.

DROP TABLE IF EXISTS foodPackage; CREATE TABLE foodPackage ( id int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(50) NOT NULL, foodItemId int(10) NULL, foodBrandId int(10) NULL, servingSizeAmount float(5,3) NOT NULL, foodServingSizeUnitId int(10) NOT NULL, calories float(5,1) NULL, carbohydrates float(5,1) NULL, fatSaturated float(5,1) NULL, fatUnsaturated float(5,1) NULL, fiber float(5,1) NULL, protein float(5,1) NULL, cholesterol float(5,1) NULL, sodium float(5,1) NULL, calcium float(5,1) NULL, iron float(5,1) NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

table: foodStore

Specific Stores.

DROP TABLE IF EXISTS foodStore; CREATE TABLE foodStore ( id int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(50) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

table: foodPrice

Food-price is tied to a branded item. They might come in different sizes, so servingCount is specified here.

DROP TABLE IF EXISTS foodPrice; CREATE TABLE foodPrice ( id int(10) unsigned NOT NULL AUTO_INCREMENT, foodStoreId int(10) NOT NULL, foodPackageId int(10) NOT NULL, servingCount float(4,1) NOT NULL, perOunce float(5,2) NULL, cost float(5,2) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; \\ http://shop.mywebgrocer.com/shop.aspx?strid=C217228

table: foodBrand

This is the food's brand. "Generic" is considered a brand. These might eventually tie back to a company, for coupon purposes, but there's currently no plans to do so.

DROP TABLE IF EXISTS foodBrand; CREATE TABLE foodBrand ( id int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(45) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

table: foodServingSizeUnit

This is a list of all serving size units.

DROP TABLE IF EXISTS foodServingSizeUnit; CREATE TABLE foodServingSizeUnit ( id int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(45) NOT NULL, longName varchar(45) NOT NULL, longNamePlural varchar(45) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO foodServingSizeUnit (name,longName,longNamePlural) VALUES ('cup','cup','cups'); INSERT INTO foodServingSizeUnit (name,longName,longNamePlural) VALUES ('fl oz','fluid ounce','fluid ounces'); INSERT INTO foodServingSizeUnit (name,longName,longNamePlural) VALUES ('gal','gallon','gallons'); INSERT INTO foodServingSizeUnit (name,longName,longNamePlural) VALUES ('L','liter','liters'); INSERT INTO foodServingSizeUnit (name,longName,longNamePlural) VALUES ('mL','milliliter','milliliters'); INSERT INTO foodServingSizeUnit (name,longName,longNamePlural) VALUES ('pt','pint','pints'); INSERT INTO foodServingSizeUnit (name,longName,longNamePlural) VALUES ('qt','quart','quarts'); INSERT INTO foodServingSizeUnit (name,longName,longNamePlural) VALUES ('tbsp','tablespoon','tablespoons'); INSERT INTO foodServingSizeUnit (name,longName,longNamePlural) VALUES ('tsp','teaspoon','teaspoons'); INSERT INTO foodServingSizeUnit (name,longName,longNamePlural) VALUES ('g','gram','grams'); INSERT INTO foodServingSizeUnit (name,longName,longNamePlural) VALUES ('kg','kilogram','kilograms'); INSERT INTO foodServingSizeUnit (name,longName,longNamePlural) VALUES ('mg','milligram','milligrams'); INSERT INTO foodServingSizeUnit (name,longName,longNamePlural) VALUES ('oz','ounce','ounces'); INSERT INTO foodServingSizeUnit (name,longName,longNamePlural) VALUES ('lb','pound','pounds'); INSERT INTO foodServingSizeUnit (name,longName,longNamePlural) VALUES ('count','count','count'); INSERT INTO foodServingSizeUnit (name,longName,longNamePlural) VALUES ('pinch','pinch','pinch');

table: foodServingSizeUnitConversion

Sometimes a recipe will call for something that's not in the product's serving size unit.

DROP TABLE IF EXISTS foodServingSizeUnitConversion; CREATE TABLE foodServingSizeUnitConversion ( id int(10) unsigned NOT NULL AUTO_INCREMENT, foodServingSizeUnitId int(10) NOT NULL, targetFoodServingSizeUnitId int(10) NOT NULL, conversionMultiple float(8,4) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; // http://www.infoplease.com/ipa/A0001723.html

table: foodRecipe

DROP TABLE IF EXISTS foodRecipe; CREATE TABLE foodRecipe ( id int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(45) NOT NULL, found varchar(100) NULL, directions varchar(3000) NOT NULL, servings int(10) NOT NULL, weightWatchersPoints int(10) NULL, totalTime int(10) NULL, prepTime int(10) NULL, cookTime int(10) NULL, calories float(5,1) NULL, carbohydrates float(5,1) NULL, fatSaturated float(5,1) NULL, fatUnsaturated float(5,1) NULL, fiber float(5,1) NULL, protein float(5,1) NULL, cholesterol float(5,1) NULL, sodium float(5,1) NULL, calcium float(5,1) NULL, iron float(5,1) NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; // http://www.healthyweightforum.org/eng/articles/weight_watchers_points/

table: foodIngredient

DROP TABLE IF EXISTS foodIngredient; CREATE TABLE foodIngredient ( id int(10) unsigned NOT NULL AUTO_INCREMENT, foodRecipeId varchar(45) NOT NULL, foodItemId int(10) NULL, foodPackageId int(10)NULL, foodServingSizeUnitId int(10) NOT NULL, servingAmount float(8,4) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;