Created
August 6, 2011 01:26
-
-
Save SnugglePilot/1128882 to your computer and use it in GitHub Desktop.
Sample Data for SQL Ingredient Selection
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment