设计需要包括成分和子配方的食谱数据库 [英] Designing a recipe database that needs to include ingredients as well as sub-recipes

问题描述

我正在设计一个需要非常灵活的配方数据库，因为它将直接与我们的后备库存系统进行沟通。这是我到目前为止在表中：

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:

• 食谱：此表将包含食谱日期：名称，需要的步骤烹饪等。


• 成分/库存：这是我们的房屋库存，所以这将有关于我们食谱中将使用的每种产品的信息。


• 食谱行项目：这是棘手的表格，我想要能够链接到这里的成分以及食谱所需的数量，但是我还需要能够直接从食谱中添加食谱桌子（如我们内部制作的马里拉酱），这就是为什么我无法想出设计这张桌子的最佳方式。

基本上，配方订单项表格需要根据需要的订单项链接到成分表或配方表，我想知道最有效的方法是什么。

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.

提前谢谢！

推荐答案

看起来像需要一个与此类似的数据库模型：

Looks like you need a database model similar to this:

此模型具有以下属性：

• 本质上，每个配方是一系列步骤。


• 每个步骤都有相对于相同配方的其他步骤（STEP_NO），单位（质量，体积，数量...），数量


• 一个特定的步骤连接到一个成分（INGREDIENT_ID不为空）或另一个配方（当SUBRECIPE_ID不为NULL时） ¹


• 除此之外，STEP是一个相当标准的连接表，实现多对多关系，这意味着相同的成分可以用于多个配方（甚至多个相同配方的步骤）以及配方可以是多个其他配方的子配方。


• 这本质上是一个有向图。数据模型本身不会阻止循环 - 它们应该在客户端代码级别避免，并可能被触发器检测到。

• 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.

¹ 如果MySQL支持CHECK约束（它是不），你可以确保一个（但不是两个）是非NULL的，如下所示：

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

现在，您需要一个触发器。

这篇关于设计需要包括成分和子配方的食谱数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！