Most DBAs are familiar with the classic bill of materials (BOM) problem: Given quantities of x, y, and z, how many units can you build? This SQL Server tip is a variation on the BOM problem; it tells you what recipes you can make with the ingredients you have on hand. The technique applies to a wide variety of applications, all of which are described by the BOM formula.
Here's the scenario: Imagine a SQL Server database that consists of the following tables:
- Recipes: a list of recipes with descriptions, plus various flags identifying spicy, vegetarian, and so on.
- Ingredients: a list of ingredients recognized by the system.
- RecipeIngredients: an associative table between Recipes and Ingredients, such that each ingredient required by a recipe is represented by one row in this table.
- Pantry: a table that represents the sum of what's in your pantry and refrigerator.
Assume there's some magical connection between the grocery stores you frequent and the database so that purchasing more ingredients somehow updates the Pantry table, either with increased quantities or new rows. (In the age of RFID, this may be possible in the future; for now at least, you still have to update the table yourself.)
Now for the problem: Identify which recipes you can make given what's in the pantry. The following is required to solve this problem:
- A recipe consists of n recipe ingredients.
- The pantry consists of p ingredients.
- Include any recipe with ingredients that can be found in the pantry.
You have to compare the list of recipe ingredients with the pantry and exclude the recipe if even one ingredient is missing. (For simplicity's sake, if you have at least one unit of measure of any required ingredient, you should consider that enough for this example.)
You can solve the problem by performing two counts. The first simply counts the ingredients required for each recipe; the second joins RecipeIngredients to Ingredients and counts. Any required ingredient that is missing from the pantry will lower that count, and by comparing the counts, you can exclude the recipes you cannot make and include those you can. I offer an example in Listing A. (The database in Listing A is a little more complex than the discussion implies, with a few lookup tables and a few unmentioned columns.)
Now that I have both counts (Required and Available), I simply have to compare them. I generally take the approach of atomic vs. molecular queries, so I saved each statement above as a view (IngredientsAvailable_By_Recipe_vue and IngredientsRequired_By_Recipe_vue). Then I create a third view that joins these on RecipeID, adding the criterion that Available must equal Required. To make the list more attractive, I joined to Recipes as well, so I could obtain the name of the recipe. View Listing B. You can flip the logic and show the recipes you cannot make simply by changing the WHERE clause to test for Required > Available.
As stated earlier, I'm assuming that the presence of any quantity in the pantry means that I have enough of that ingredient. I really need a Quantity column in the RecipeIngredients table and a Quantity column in the Pantry table. (This can get even more complicated; for example, I buy salt by the pound or kilo, and recipes typically call for tablespoons or pinches.)
If you have the Quantity columns, a new challenge arises: Assume that your menu for a dinner party for friends is guacamole, Mexican salad, huachinango (baked red snapper), and cerveza. The task is to compare what is required with what is in the pantry and create a shopping list of what you need to buy. I'll leave this challenge to the interested reader.
Note: You can also download a .NET executable that creates the database and populates it with the objects of interest, including the data in the tables. You will need to have .NET and SQL Server 2005 installed in order to execute the code. It's a winRAR file as attached, but inside that is the .NET executable. The code was created by Red Gate Software's SQL Packager, but you do not need Red Gate to run the executable.
Miss a tip?
Check out the SQL Server archive, and catch up on the most recent editions of Arthur Fuller's column.