Designing a recipe database that needs to include ingredients as well as sub-recipes

Question

I am designing a recipe database that needs to be very flexible as it is going to be communicating directly with our back-of-house inventory system. This is what I have so far in regards to the tables:

• Recipe: this table will contain the recipe date: the name, steps needed to cook, etc.
• Ingredients/Inventory: this is our back of house inventory, so this will have the information about each product that will be used in our recipes.
• Recipe Line Item: This is the tricky table, I want to be able link to the ingredients here as well as the quantity needed for the recipe, but I also need to be able to directly include recipes from the recipe table (such as marinara sauce that we make in-house), and that is why I am having trouble figuring out the best way to design this table.

Basically, the recipe line item table needs to be able to link to either the ingredients table or the recipe table depending on which line item is needed and I want to know what would be the most effective way to handle that.

Thank you so much in advance!

Answer 1

Looks like you need a database model similar to this:

This model has the following properties:

• Essentially, each recipe is a series of steps.
• Each step has its order relative to other steps of the same recipe (STEP_NO), a unit (mass, volume, count...), a quantity in that unit etc.
• A particular step is connected either to an ingredient (when INGREDIENT_ID is non-NULL) or to another recipe (when SUBRECIPE_ID is non-NULL).¹
• Other than that, the STEP is a fairly standard junction table implementing many-to-many relationship, which means the same ingredient can be used in multiple recipes (or even multiple steps of the same recipe) and also a recipe can be a "sub-recipe" of multiple other recipes.
• This is essentially a directed graph. The data model itself will not prevent cycles - they should be avoided at the client code level and possibly detected by triggers.

---

¹ If MySQL supported CHECK constraints (which it doesn't), you could ensure that one (but not both) of them is non-NULL like this:

CHECK (
    (INGREDIENT_ID IS NULL AND SUBRECIPE_ID IS NOT NULL)
    OR (INGREDIENT_ID IS NOT NULL AND SUBRECIPE_ID IS NULL)
)

As it stands, you'll need a trigger for that.

Answer 2

Ingredients and Recipes are both possible RecipeItems:

CREATE TABLE RecipeItems (
  ItemID       SERIAL,
  Type         ENUM('Ingredient', 'Recipe'),
  Name         VARCHAR(255) NOT NULL,
  Quantity     FLOAT NOT NULL,
  INDEX (ItemID, Type)
);

CREATE TABLE Ingredients (
  IngredientID BIGINT UNSIGNED NOT NULL,
  Type         ENUM('Ingredient'),
  CostPrice    DECIMAL(6,2),
  PRIMARY KEY (IngredientID),
  FOREIGN KEY (IngredientID, Type) REFERENCES RecipeItems (ItemID, Type)
);

CREATE TABLE Recipes (
  RecipeID     BIGINT UNSIGNED NOT NULL,
  Type         ENUM('Recipe'),
  SellPrice    DECIMAL(6,2),
  Date         DATE,
  Instructions TEXT,
  PRIMARY KEY (RecipeID),
  FOREIGN KEY (RecipeID, Type) REFERENCES RecipeItems (ItemID, Type)
);

Then RecipeLineItems:

CREATE TABLE RecipeLineItems (
  RecipeID     BIGINT UNSIGNED NOT NULL,
  ItemID       BIGINT UNSIGNED NOT NULL,
  Quantity     FLOAT NOT NULL,
  PRIMARY KEY (RecipeID, ItemID),
  FOREIGN KEY (RecipeID) REFERENCES Recipes     (RecipeID),
  FOREIGN KEY (ItemID)   REFERENCES RecipeItems (ItemID)
);

With this approach, I recommend that you enable strict SQL mode (or else invalid values will be accepted in the ENUM typed columns with the empty string '' as a special error value): this could break the intended referential integrity of the above model. An alternative (but slightly more tedious) approach would be to enforce referential integrity manually using triggers.

If only MySQL supported CHECK constraints, huh?

Answer 3

This script will create you a database which let you manage recipes, ingredients, the recip composition (ingredients_recipes) and also unities for you inventory and the composition. It also let you manage your inventory history.

Here is the query to get your current recipe, the ingredients needed, the quantity needed and the stock you currently have :

SELECT recipes.id, recipes.name AS recipeName, ingredients.name AS ingredientNeeded, CONCAT(ingredients_recipes.Qty,' ',neededUnities.name) AS neededQuantity, CONCAT(inventories.qty,' ',inventoryUnities.name) AS availableQuantity FROM recipes 

LEFT JOIN ingredients_recipes ON recipes.id=ingredients_recipes.recipe_id 
LEFT JOIN ingredients ON ingredients_recipes.ingredient_id = ingredients.id 
LEFT JOIN inventories ON ingredients.id=inventories.ingredient_id 
LEFT JOIN unities AS inventoryUnities ON inventories.unity_id=inventoryUnities.id
LEFT JOIN unities AS neededUnities ON ingredients_recipes.unity_id=neededUnities.id

WHERE inventories.`update` = (SELECT MAX(`update`) FROM inventories AS inv WHERE inv.ingredient_id = inventories.ingredient_id);

the database :

-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               5.5.16 - MySQL Community Server (GPL)
-- Server OS:                    Win32
-- HeidiSQL version:             7.0.0.4053
-- Date/time:                    2012-12-14 16:33:22
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET FOREIGN_KEY_CHECKS=0 */;

-- Dumping database structure for database
DROP DATABASE IF EXISTS `database`;
CREATE DATABASE IF NOT EXISTS `database` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `database`;


-- Dumping structure for table database.ingredients
DROP TABLE IF EXISTS `ingredients`;
CREATE TABLE IF NOT EXISTS `ingredients` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `unity_id` int(11) NOT NULL COMMENT 'for the default unity',
  `Created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `Unity_id` (`unity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table database.ingredients: ~0 rows (approximately)
DELETE FROM `ingredients`;
/*!40000 ALTER TABLE `ingredients` DISABLE KEYS */;
/*!40000 ALTER TABLE `ingredients` ENABLE KEYS */;


-- Dumping structure for table database.ingredients_recipes
DROP TABLE IF EXISTS `ingredients_recipes`;
CREATE TABLE IF NOT EXISTS `ingredients_recipes` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `ingredient_id` int(10) NOT NULL,
  `recipe_id` int(10) NOT NULL,
  `Qty` float NOT NULL,
  `Unity_id` int(10) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ingredient_id_recipe_id` (`ingredient_id`,`recipe_id`),
  KEY `Unity_id` (`Unity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table database.ingredients_recipes: ~0 rows (approximately)
DELETE FROM `ingredients_recipes`;
/*!40000 ALTER TABLE `ingredients_recipes` DISABLE KEYS */;
/*!40000 ALTER TABLE `ingredients_recipes` ENABLE KEYS */;


-- Dumping structure for table database.inventories
DROP TABLE IF EXISTS `inventories`;
CREATE TABLE IF NOT EXISTS `inventories` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `ingredient_id` int(10) NOT NULL COMMENT 'ingredient',
  `qty` int(10) NOT NULL COMMENT 'quantity',
  `unity_id` int(11) NOT NULL COMMENT 'unity for the ingredient',
  `update` datetime NOT NULL COMMENT 'date of the inventory update',
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table database.inventories: ~0 rows (approximately)
DELETE FROM `inventories`;
/*!40000 ALTER TABLE `inventories` DISABLE KEYS */;
/*!40000 ALTER TABLE `inventories` ENABLE KEYS */;


-- Dumping structure for table database.recipes
DROP TABLE IF EXISTS `recipes`;
CREATE TABLE IF NOT EXISTS `recipes` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `cooking` longtext NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table database.recipes: ~0 rows (approximately)
DELETE FROM `recipes`;
/*!40000 ALTER TABLE `recipes` DISABLE KEYS */;
/*!40000 ALTER TABLE `recipes` ENABLE KEYS */;


-- Dumping structure for table database.unities
DROP TABLE IF EXISTS `unities`;
CREATE TABLE IF NOT EXISTS `unities` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table database.unities: ~0 rows (approximately)
DELETE FROM `unities`;
/*!40000 ALTER TABLE `unities` DISABLE KEYS */;
/*!40000 ALTER TABLE `unities` ENABLE KEYS */;
/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;