Sample Data for SQL Ingredient Selection

getMissingRecipeIngredients.sql

-- ----------------------------
-- Fancy query to get the recipe ingredients the user is close to having. (not destructive)
-- In all versions of this, replace ? with User.name
-- ----------------------------
SELECT RI.recipe, RI.ingredient
	FROM RecipeIngredient AS RI
	LEFT OUTER JOIN UserIngredient AS UI ON (UI.ingredient = RI.ingredient AND UI.user = ? )
WHERE UI.user IS NULL

-- ----------------------------
-- SQLite: Same as above, but destructive (group_concat, count, and GROUP BY destroy the relationship to the ingredient)
-- ----------------------------
SELECT RI.recipe, COUNT( RI.ingredient ) as MissingIngredients, GROUP_CONCAT( RI.ingredient, ", " )
	FROM RecipeIngredient AS RI
	LEFT OUTER JOIN UserIngredient AS UI ON (UI.ingredient = RI.ingredient AND UI.user = ? )
WHERE UI.user IS NULL
GROUP BY recipe
ORDER BY MissingIngredients ASC

-- ----------------------------
-- MySQL: Same as SQLite version above, but tweaked for syntax difference
-- For more details see: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
-- ----------------------------
SELECT RI.recipe, COUNT( RI.ingredient ) as MissingIngredients, GROUP_CONCAT( RI.ingredient SEPARATOR  ", " )
	FROM RecipeIngredient AS RI
	LEFT OUTER JOIN UserIngredient AS UI ON (UI.ingredient = RI.ingredient AND UI.user = ? )
WHERE UI.user IS NULL
GROUP BY recipe
ORDER BY MissingIngredients ASC

gistfile1.txt

GOAL:

To create a database of liquor in your liquor cabinet, and a database of possible drinks to make. 

Output: 
(1) a list of drinks you can make with your existing liquors, (hard!) and
(2) a list of drinks you could make if you just bought a few more things

===

Views that currently exist on the DB: https://gist.github.com/1128805
(the "view_recipes_in_cabinet_using_all_ingredients" isn't quite right - it only matches a recipe that has YOUR ENTIRE cabinet in it, not a non-exclusive subset)

Data setup:

table users -- The user database
users.identity = unique ID

table liquors -- A generic list of alcohol
liquors.id = unique ID
liquors.name = plaintext name

table liquorCabinet -- This is "your stockpile of liquor" as a user
liquorCabinet.userID = users.identity
liquorCabinet.liquorID = liquors.id

table recipes -- A list of possible recipe (drink) names
recipes.id = unique ID
recipes.name = plaintext name

table recipeIngredients -- The list of recipes
recipeIngredients.recipeID = recipes.id
recipeIngredients.liquorID = liquors.id

====

Some sample data:

A single user (users.identity = 1) has two things in his liquor cabinet:
liquorCabinet.userID = 1 liquorCabinet.liquorID = 1
liquorCabinet.userID = 1 liquorCabinet.liquorID = 2

that corresponds to
liquors.id = 1 liquors.name = Vodka
liquors.id = 2 liquors.name = Tequila

There are two recipes in the system:
recipes.id = 1 recipes.name = Shot o' Vodka
recipes.id = 2 recipes.name = Gross Mix

Which correspond to:
recipeIngredients.recipeID = 1 recipeIngredients.liquorID = 1
recipeIngredients.recipeID = 2 recipeIngredients.liquorID = 1
recipeIngredients.recipeID = 2 recipeIngredients.liquorID = 2

As it stands, this user can build both recipes as he has all of the necessary required liquors.

Schema.sql

-- ----------------------------
-- Table structures
-- If using MySQL, update each table definition to use INNODB or foreign keys will be pointless
-- Example:
-- CREATE TABLE "Ingredient" (
-- "name"  TEXT NOT NULL,
-- PRIMARY KEY ("name" ASC)
-- ) type INNODB;
-- ----------------------------
CREATE TABLE "Ingredient" (
"name"  TEXT NOT NULL,
PRIMARY KEY ("name" ASC)
);

CREATE TABLE "Recipe" (
"name"  TEXT NOT NULL,
PRIMARY KEY ("name")
);

CREATE TABLE "RecipeIngredient" (
"recipe"  TEXT NOT NULL,
"ingredient"  TEXT NOT NULL,
PRIMARY KEY ("recipe", "ingredient"),
CONSTRAINT "fkRecipeIngredientRecipe" FOREIGN KEY ("recipe") REFERENCES "Recipe" ("name") ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT "fkRecipeIngredientIngredient" FOREIGN KEY ("ingredient") REFERENCES "Ingredient" ("name") ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE "User" (
"name"  TEXT NOT NULL,
PRIMARY KEY ("name")
);

CREATE TABLE "UserIngredient" (
"user"  TEXT NOT NULL,
"ingredient"  TEXT NOT NULL,
PRIMARY KEY ("user", "ingredient"),
CONSTRAINT "fkUserCabinetUser" FOREIGN KEY ("user") REFERENCES "User" ("name") ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT "fkUserCabinetIngredient" FOREIGN KEY ("ingredient") REFERENCES "Ingredient" ("name") ON DELETE CASCADE ON UPDATE CASCADE
);

-- ----------------------------
-- Get an ingredient with a recipe
-- ----------------------------
CREATE VIEW "view_recipe_by_ingredient" AS 
	SELECT R.name AS recipe, RI.ingredient AS ingredient
		FROM Recipe AS R
	INNER JOIN RecipeIngredient AS RI ON R.name = RI.recipe;

-- ----------------------------
-- Get the total ingredients the recipe has
-- ----------------------------
CREATE VIEW "view_recipe_ingredient_total" AS 
	SELECT RbI.recipe, Count( RbI.ingredient ) as Total
		FROM view_recipe_by_ingredient AS RbI
	GROUP BY RbI.recipe;

-- ----------------------------
-- Get the total ingredients I have
-- ----------------------------
CREATE VIEW "view_cabinet_total" AS 
	SELECT UI.user, Count( UI.ingredient ) AS Total
		FROM UserIngredient AS UI
	GROUP BY UI.user;

-- ----------------------------
-- Use me to get recipes that I can make with what is in my cabinet
-- ----------------------------
CREATE VIEW "view_recipes_in_cabinet" AS 
	SELECT U.user, RbI.recipe, Count( RbI.ingredient ) AS TotalMatchedIngredients, RIT.Total as TotalRecipeIngredients
		FROM UserIngredient AS U
		INNER JOIN view_recipe_by_ingredient AS RbI ON( RbI.ingredient = U.ingredient )
		INNER JOIN view_recipe_ingredient_total AS RIT ON( RbI.recipe = RIT.recipe)
	GROUP BY U.user, RbI.recipe
	HAVING TotalRecipeIngredients = TotalMatchedIngredients;

TestData.sql

-- ----------------------------
-- Test Data
-- ----------------------------
INSERT INTO "Ingredient" VALUES ('Vodka');
INSERT INTO "Ingredient" VALUES ('Rum');
INSERT INTO "Ingredient" VALUES ('Sherry');
INSERT INTO "Ingredient" VALUES ('Whiskey');
INSERT INTO "Ingredient" VALUES ('Cherry');
INSERT INTO "Ingredient" VALUES ('Orange');
INSERT INTO "Ingredient" VALUES ('Olive');
INSERT INTO "Ingredient" VALUES ('Gin');
INSERT INTO "Ingredient" VALUES ('Lemon Juice');
INSERT INTO "Ingredient" VALUES ('Orange Gin');
INSERT INTO "Ingredient" VALUES ('Sweet Vermouth');
INSERT INTO "Ingredient" VALUES ('7-Up');
INSERT INTO "Ingredient" VALUES ('Cranberry Juice');
INSERT INTO "Ingredient" VALUES ('Dekuyper Razzmatazz');
INSERT INTO "Ingredient" VALUES ('Orange Juice');
INSERT INTO "Ingredient" VALUES ('Sour Mix');
INSERT INTO "Ingredient" VALUES ('Dry Cider');
INSERT INTO "Ingredient" VALUES ('Lager');
INSERT INTO "Ingredient" VALUES ('Sloe Gin');
INSERT INTO "Ingredient" VALUES ('Southern Comfort');
INSERT INTO "Ingredient" VALUES ('Triple Sec');
INSERT INTO "Ingredient" VALUES ('Blue Curacao');
INSERT INTO "Ingredient" VALUES ('Lime Juice');
INSERT INTO "Ingredient" VALUES ('Red Curacao');
INSERT INTO "Ingredient" VALUES ('Tequila');
INSERT INTO "Recipe" VALUES ('Leap Year Cocktail');
INSERT INTO "Recipe" VALUES ('Purple Gecko');
INSERT INTO "Recipe" VALUES ('Slow Comfortable Screw In Between the Sheets');
INSERT INTO "Recipe" VALUES ('Snakebite (UK)');
INSERT INTO "Recipe" VALUES ('Stoner Delight');
INSERT INTO "Recipe" VALUES ('Vodka');
INSERT INTO "Recipe" VALUES ('Tequila');
INSERT INTO "Recipe" VALUES ('Whiskey');
INSERT INTO "RecipeIngredient" VALUES ('Leap Year Cocktail', 'Lemon Juice');
INSERT INTO "RecipeIngredient" VALUES ('Leap Year Cocktail', 'Gin');
INSERT INTO "RecipeIngredient" VALUES ('Leap Year Cocktail', 'Orange Gin');
INSERT INTO "RecipeIngredient" VALUES ('Leap Year Cocktail', 'Sweet Vermouth');
INSERT INTO "RecipeIngredient" VALUES ('Purple Gecko', 'Blue Curacao');
INSERT INTO "RecipeIngredient" VALUES ('Purple Gecko', 'Cranberry Juice');
INSERT INTO "RecipeIngredient" VALUES ('Purple Gecko', 'Lime Juice');
INSERT INTO "RecipeIngredient" VALUES ('Purple Gecko', 'Red Curacao');
INSERT INTO "RecipeIngredient" VALUES ('Purple Gecko', 'Sour Mix');
INSERT INTO "RecipeIngredient" VALUES ('Purple Gecko', 'Tequila');
INSERT INTO "RecipeIngredient" VALUES ('Slow Comfortable Screw In Between the Sheets', 'Orange Juice');
INSERT INTO "RecipeIngredient" VALUES ('Slow Comfortable Screw In Between the Sheets', 'Sloe Gin');
INSERT INTO "RecipeIngredient" VALUES ('Slow Comfortable Screw In Between the Sheets', 'Southern Comfort');
INSERT INTO "RecipeIngredient" VALUES ('Slow Comfortable Screw In Between the Sheets', 'Triple Sec');
INSERT INTO "RecipeIngredient" VALUES ('Slow Comfortable Screw In Between the Sheets', 'Vodka');
INSERT INTO "RecipeIngredient" VALUES ('Snakebite (UK)', 'Dry Cider');
INSERT INTO "RecipeIngredient" VALUES ('Snakebite (UK)', 'Lager');
INSERT INTO "RecipeIngredient" VALUES ('Stoner Delight', '7-Up');
INSERT INTO "RecipeIngredient" VALUES ('Stoner Delight', 'Cranberry Juice');
INSERT INTO "RecipeIngredient" VALUES ('Stoner Delight', 'Dekuyper Razzmatazz');
INSERT INTO "RecipeIngredient" VALUES ('Stoner Delight', 'Orange Juice');
INSERT INTO "RecipeIngredient" VALUES ('Stoner Delight', 'Sour Mix');
INSERT INTO "RecipeIngredient" VALUES ('Stoner Delight', 'Vodka');
INSERT INTO "RecipeIngredient" VALUES ('Vodka', 'Vodka');
INSERT INTO "RecipeIngredient" VALUES ('Tequila', 'Tequila');
INSERT INTO "RecipeIngredient" VALUES ('Whiskey', 'Whiskey');
INSERT INTO "User" VALUES ('Kevin');
INSERT INTO "User" VALUES ('Andy');
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Orange Juice');
INSERT INTO "UserIngredient" VALUES ('Andy', 'Rum');
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Blue Curacao');
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Sour Mix');
INSERT INTO "UserIngredient" VALUES ('Kevin', '7-Up');
INSERT INTO "UserIngredient" VALUES ('Andy', 'Lemon Juice');
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Cherry');
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Dry Cider');
INSERT INTO "UserIngredient" VALUES ('Andy', 'Sweet Vermouth');
INSERT INTO "UserIngredient" VALUES ('Andy', 'Blue Curacao');
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Sherry');
INSERT INTO "UserIngredient" VALUES ('Andy', 'Tequila');
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Red Curacao');
INSERT INTO "UserIngredient" VALUES ('Andy', 'Sherry');
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Sweet Vermouth');
INSERT INTO "UserIngredient" VALUES ('Andy', 'Olive');
INSERT INTO "UserIngredient" VALUES ('Andy', 'Dekuyper Razzmatazz');
INSERT INTO "UserIngredient" VALUES ('Andy', 'Lime Juice');
INSERT INTO "UserIngredient" VALUES ('Andy', 'Lager');
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Sloe Gin');
INSERT INTO "UserIngredient" VALUES ('Andy', 'Southern Comfort');
INSERT INTO "UserIngredient" VALUES ('Andy', 'Red Curacao');
INSERT INTO "UserIngredient" VALUES ('Andy', 'Orange Gin');
INSERT INTO "UserIngredient" VALUES ('Andy', 'Orange Juice');
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Rum');
INSERT INTO "UserIngredient" VALUES ('Andy', 'Orange');
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Lemon Juice');
INSERT INTO "UserIngredient" VALUES ('Andy', 'Whiskey');
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Lime Juice');
INSERT INTO "UserIngredient" VALUES ('Kevin', 'Vodka');

UserIngredientPopulator.sql

-- ----------------------------
-- Empty the UserIngredient table and put random values in it
-- Change the LIMIT to something interesting, like Count( User.name ) * 10
-- MySQL: Change random() to RAND()
-- ----------------------------
DELETE FROM UserIngredient;
INSERT INTO UserIngredient
	SELECT U.name, I.name
		FROM User U, Ingredient I
	ORDER BY random()
	LIMIT 30;

view_recipes_not_in_cabinet

CREATE VIEW "view_recipes_not_in_cabinet" AS 
	SELECT *
		FROM RecipeIngredient
	WHERE ingredient NOT IN(
		SELECT ingredient FROM UserIngredient
	);

-- ----------------------------
-- Destructive: SQLite edition
-- ----------------------------
CREATE VIEW "view_recipes_not_in_cabinet_by_ingredients_missing" AS 
	SELECT recipe, COUNT( RI.ingredient ) as MissingIngredients, GROUP_CONCAT( ingredient, ", " )
		FROM RecipeIngredient
	WHERE ingredient NOT IN(
		SELECT ingredient FROM UserIngredient
	)
	GROUP BY recipe
	ORDER BY MissingIngredients ASC;

-- ----------------------------
-- Destructive: MySQL edition
-- ----------------------------
CREATE VIEW "view_recipes_not_in_cabinet_by_ingredients_missing" AS 
	SELECT recipe, COUNT( RI.ingredient ) as MissingIngredients, GROUP_CONCAT( ingredient SEPARATOR ", " )
		FROM RecipeIngredient
	WHERE ingredient NOT IN(
		SELECT ingredient FROM UserIngredient
	)
	GROUP BY recipe
	ORDER BY MissingIngredients ASC;

Notes:

• I used natural keys everywhere to simplify the concept. Surrogate keys could be substituted werever necessary.
• If this database is going on a mobile phone, locally, then the concept of User is moot and the UserIngredient table could be altered appropriately (like renaming it to LiquorCabinet). At the same time, all queies related to User need to be updated as well
  ** If the above is true, then getMissingRecipeIngredients query can actually become a view. See the file named view_recipies_not_in_cabinet.sql
• My naming convention isn't consistent, I should go an update the view names to match table naming :P