Concevoir une base de données de recettes qui doit inclure des ingrédients ainsi que des sous-recettes

Question

Je suis en train de concevoir une base de données de recettes qui doit être très flexible car elle va communiquer directement avec notre système de gestion des stocks en arrière-boutique. Voici ce que j'ai jusqu'à maintenant en ce qui concerne les tables:

• Recette: cette table contiendra la date de la recette, le nom, les étapes nécessaires pour la cuisson, etc.
• Ingrédients/Inventaire: il s'agit de notre inventaire en arrière-boutique, donc cela aura les informations sur chaque produit qui sera utilisé dans nos recettes.
• Article de Ligne de Recette: C'est la table délicate, je veux pouvoir lier les ingrédients ici ainsi que la quantité nécessaire pour la recette, mais je dois également être en mesure d'inclure directement des recettes de la table des recettes (comme la sauce marinara que nous faisons maison), et c'est pourquoi j'ai du mal à trouver la meilleure façon de concevoir cette table.

Essentiellement, la table des articles de ligne de recette doit pouvoir être liée soit à la table des ingrédients soit à la table des recettes en fonction de l'article de ligne nécessaire et je voudrais savoir quelle serait la manière la plus efficace de gérer cela.

Merci d'avance!

Answer 1

Il semble que vous avez besoin d'un modèle de base de données similaire à celui-ci :

Ce modèle a les propriétés suivantes :

• Essentiellement, chaque recette est une série d'étapes.
• Chaque étape a son ordre par rapport aux autres étapes de la même recette (STEP_NO), une unité (masse, volume, compte...), une quantité dans cette unité etc.
• Une étape particulière est connectée soit à un ingrédient (lorsque INGREDIENT_ID n'est pas NULL) soit à une autre recette (lorsque SUBRECIPE_ID n'est pas NULL).1
• À part cela, l'étape est une table de jonction assez standard implémentant une relation many-to-many, ce qui signifie que le même ingrédient peut être utilisé dans plusieurs recettes (ou même plusieurs étapes de la même recette) et aussi une recette peut être une "sous-recette" de plusieurs autres recettes.
• C'est essentiellement un graphe orienté. Le modèle de données lui-même ne préviendra pas les cycles - ils devraient être évités au niveau du code client et éventuellement détectés par des déclencheurs.

---

1 Si MySQL supportait les contraintes de VÉRIFICATION (CHECK) (ce qu'il ne fait pas), vous pourriez vous assurer qu'un (mais pas les deux) d'entre eux est non NULL de cette manière :

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

Tel que c'est, vous aurez besoin d'un déclencheur pour cela.

Answer 2

Ingrédients et Recettes sont tous deux des RecipeItems possibles :

CREATE TABLE RecipeItems (
  ItemID       SERIAL,
  Type         ENUM('Ingrédient', 'Recette'),
  Name         VARCHAR(255) NOT NULL,
  Quantity     FLOAT NOT NULL,
  INDEX (ItemID, Type)
);

CREATE TABLE Ingredients (
  IngredientID BIGINT UNSIGNED NOT NULL,
  Type         ENUM('Ingrédient'),
  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('Recette'),
  SellPrice    DECIMAL(6,2),
  Date         DATE,
  Instructions TEXT,
  PRIMARY KEY (RecipeID),
  FOREIGN KEY (RecipeID, Type) REFERENCES RecipeItems (ItemID, Type)
);

Ensuite 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)
);

Avec cette approche, je recommande d'activer le mode strict de SQL (sinon des valeurs non valides seront acceptées dans les colonnes de type ENUM avec la chaîne vide '' comme valeur d'erreur spéciale) : cela pourrait compromettre l'intégrité référentielle voulue du modèle ci-dessus. Une alternative (mais légèrement plus fastidieuse) serait d'imposer l'intégrité référentielle manuellement en utilisant des déclencheurs.

Si seulement MySQL prenait en charge les contraintes CHECK, hein ?

Answer 3

Ce script va créer une base de données qui vous permettra de gérer des recettes, des ingrédients, la composition des recettes (ingrédients_recipes), ainsi que les unités pour votre inventaire et la composition. Il vous permettra également de gérer l'historique de votre inventaire.

Voici la requête pour obtenir votre recette actuelle, les ingrédients nécessaires, la quantité requise et le stock que vous avez actuellement :

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);

la base de données :

-- --------------------------------------------------------
-- Hôte:                         127.0.0.1
-- Version du serveur:           5.5.16 - Serveur MySQL Community (GPL)
-- Système d'exploitation du serveur:                    Win32
-- Version de HeidiSQL:             7.0.0.4053
-- Date/heure:                    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 */;

-- Structure de la base de données pour la base de données
DROP DATABASE IF EXISTS `database`;
CREATE DATABASE IF NOT EXISTS `database` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `database`;

-- Structure de dump pour la 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 'pour l\'unité par défaut',
  `Created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `Unity_id` (`unity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Données de vidage pour la table database.ingredients: ~0 lignes (environ)
DELETE FROM `ingredients`;
/*!40000 ALTER TABLE `ingredients` DISABLE KEYS */;
/*!40000 ALTER TABLE `ingredients` ENABLE KEYS */;

-- Structure de dump pour la 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;

-- Données de vidage pour la table database.ingredients_recipes: ~0 lignes (environ)
DELETE FROM `ingredients_recipes`;
/*!40000 ALTER TABLE `ingredients_recipes` DISABLE KEYS */;
/*!40000 ALTER TABLE `ingredients_recipes` ENABLE KEYS */;

-- Structure de dump pour la 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 'ingrédient',
  `qty` int(10) NOT NULL COMMENT 'quantité',
  `unity_id` int(11) NOT NULL COMMENT 'unité pour l'ingrédient',
  `update` datetime NOT NULL COMMENT 'date de la mise à jour de l'inventaire',
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Données de vidage pour la table database.inventories: ~0 lignes (environ)
DELETE FROM `inventories`;
/*!40000 ALTER TABLE `inventories` DISABLE KEYS */;
/*!40000 ALTER TABLE `inventories` ENABLE KEYS */;

-- Structure de dump pour la 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;

-- Données de vidage pour la table database.recipes: ~0 lignes (environ)
DELETE FROM `recipes`;
/*!40000 ALTER TABLE `recipes` DISABLE KEYS */;
/*!40000 ALTER TABLE `recipes` ENABLE KEYS */;

-- Structure de dump pour la 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;

-- Données de vidage pour la table database.unities: ~0 lignes (environ)
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 */;

Answer 4

Vous aurez besoin de trois tables : une table recipes, une table ingredients et une table recipe_ingredients qui attribue les ingrédients à une recette. Vous pouvez également stocker des informations supplémentaires dans cette table, telles que les quantités. Par exemple, si vous avez une recette de soupe aux légumes, vous auriez plusieurs entrées pour les légumes avec les quantités correspondantes. Ces entrées seraient ensuite liées à la recette et aux ingrédients pertinents via une clé étrangère.

MODIFIER : Schéma au plus simple :

CREATE TABLE `ingredients` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) TYPE=InnoDB;

CREATE TABLE `recipes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) TYPE=InnoDB;

CREATE TABLE `recipe_ingredients` (
  `recipe_id` int(10) unsigned NOT NULL,
  `ingredient_id` int(10) unsigned NOT NULL,
  `quantity` int(10) unsigned NOT NULL,
  KEY `recipe_id` (`recipe_id`),
  KEY `ingredient_id` (`ingredient_id`)
) TYPE=InnoDB;

ALTER TABLE `recipe_ingredients`
  ADD CONSTRAINT `recipe_ingredients_ibfk_2` FOREIGN KEY (`ingredient_id`) REFERENCES `ingredients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `recipe_ingredients_ibfk_1` FOREIGN KEY (`recipe_id`) REFERENCES `recipes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Answer 5

Un moyen.

Plat
Clé ID Nom
1   1  Escargots à la Sauce Marinara
2   2  Sauce Marinara
3   3  Verre d'eau

Ingrédient
Clé DISHID Nom
1   NULL   Escargot
2   NULL   Tomate
3   NULL   Oignon
4   NULL   Evian
5   2      Sauce Marinara

Recette
DishID Clé Ingrédient Qté UOM
1      1             6   Chacun
1      5             3   Cuillerée à table
2      2             2   Chacun
2      3             1   Chacun
3      4             275 Millilitres

Donc si un ingrédient est un plat, il a une recette.

Révisé après une question de l'OP, qui a souligné une petite faille dans ma réponse potentielle.