Разработка базы данных рецептов, которая должна включать ингредиенты, а также подрецепты

Я разрабатываю базу данных рецептов, которая должна быть очень гибкой, поскольку она собирается напрямую обмениваться информацией с нашей системой инвентаризации за пределами дома. Это то, что я имею в отношении таблиц:

Рецепт: эта таблица будет содержать дату рецепта: имя, шаги, необходимые для приготовления и т.д.
Ингредиенты/инвентарь: это наш запас инвентаря для дома, поэтому у него будет информация о каждом продукте, который будет использоваться в наших рецептах.
Пункт рецепта: это сложная таблица, я хочу иметь возможность связываться с ингредиентами здесь, а также количество, необходимое для рецепта, но мне также нужно иметь возможность напрямую включать рецепты из таблицы рецептов (например, как соус маринара, который мы делаем в доме), и именно поэтому у меня возникли проблемы с поиском наилучшего способа разработки этой таблицы.

В принципе, таблица позиций в таблице рецептов должна иметь возможность ссылаться либо на таблицу ингредиентов, либо на таблицу рецептов в зависимости от того, какая позиция требуется, и я хочу знать, что было бы самым эффективным способом справиться с этим.

Большое вам спасибо заранее!

Ответ 1

Похоже, вам нужна модель базы данных, подобная этой:

Эта модель обладает следующими свойствами:

По сути, каждый рецепт представляет собой последовательность шагов.
Каждый шаг имеет свой порядок относительно других шагов одного и того же рецепта (STEP_NO), единицы (масса, объем, счет...), количество в этом блоке и т.д.
Конкретный шаг связан либо с ингредиентом (когда INGREDIENT_ID не является NULL), либо другим рецептом (когда SUBRECIPE_ID не является NULL). ¹
Кроме этого, STEP представляет собой довольно стандартную таблицу соединений, реализующую отношения "многие ко многим", что означает, что один и тот же ингредиент может использоваться в нескольких рецептах (или даже нескольких этапах одного и того же рецепта), а также рецепт может быть "подрецепт" нескольких других рецептов.
Это по существу ориентированный граф. Сама модель данных не будет препятствовать циклам - их следует избегать на уровне кода клиента и, возможно, обнаружить триггеры.

¹ Если MySQL поддерживает ограничения CHECK (который он не), вы можете убедиться, что один (но не оба) из них не имеет значения NULL:

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

В его нынешнем виде вам понадобится триггер.

Ответ 2

Ingredients и Recipes возможны 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)
);

Затем 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)
);

При таком подходе я рекомендую вам включить строгий режим SQL (иначе недопустимые значения будут приниматься в столбцах ENUM с пустой строкой '' как специальное значение ошибки): это может сломать предполагаемый ссылочный целостность указанной модели. Альтернативным (но немного более утомительным) подходом было бы принудительное использование ссылочной целостности вручную с помощью триггеров.

Если только MySQL поддерживает ограничения CHECK, а?

Ответ 3

Вам понадобятся три таблицы: таблица recipes, таблица ingredients и таблица recipe_ingredients, которая присваивает ингредиенты рецепту. Вы также можете сохранить дополнительную информацию в этой таблице, например, количества. Так, например, если у вас есть рецепт для овощного супа, у вас будет несколько записей для овощей с соответствующими количествами. Эти записи затем будут связаны с соответствующим рецептом и ингредиентами с помощью внешнего ключа.

EDIT: Схема простейшая:

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;

Ответ 4

Один из способов.

Dish
Key ID Name
1   1  Snails in Marinara Sauce
2   2  Marinara Sauce
3   3  Glass of Water


Ingredient
Key DISHID Name
1   NULL   Snail
2   NULL   Tomato
3   NULL   Onion
4   NULL   Evian
5   2      Marinara Sauce

Recipe
DishID IngredientKey Qty UOM
1      1             6   Each
1      5             3   TblSpoon
2      2             2   Each
2      3             1   Each
3      4             275 Millilitres

Итак, если ингредиент - это блюдо, у него есть рецепт.

Изменен после вопроса от OP, который указал на недостаток моего потенциального ответа.

Ответ 5

Этот script создаст вам базу данных, которая позволит вам управлять рецептами, ингредиентами, рецептурной композицией (components_recipes), а также единицами для вашего инвентаря и композиции. Это также позволяет управлять историей инвентаря.

Вот запрос, чтобы получить ваш текущий рецепт, необходимые ингредиенты, необходимое количество и имеющийся у вас запас:

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

база данных:

-- --------------------------------------------------------
-- 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 @[email protected]@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 [email protected]_CHARACTER_SET_CLIENT */;