食品食谱数据库建模
我正在尝试设计一个“食谱盒”数据库，但我无法正确完成它。我不知道我是否走在正确的道路上，但这就是我所拥有的。
食谱(recipeID等)
成分（成分ID等）
配方成分（配方ID，成分ID，数量）
类别（类别ID，名称）
RecipeCategory(recipeID、categoryID、名称等)
所以我有几个问题。
- 到目前为止我怎么样？据大家了解，这个设计还好吗？
- 我将如何实施准备步骤？我应该创建一个额外的多对多实现（例如准备（prepID等）和recipePrep（recipeID，prepID））还是只在食谱表中添加说明？我希望这是用户界面（网页）中的有序列表。
感谢您的帮助。
I'm trying to design a "recipe box" database and I'm having trouble getting it right. I have no idea if I'm on the right track or not, but here's what I have.
recipes(recipeID, etc.)
ingredient(ingredientID, etc.)
recipeIngredient(recipeID, ingredientID, amount)
category(categoryID, name)
recipeCategory(recipeID, categoryID, name, etc.)
So I have a couple of questions.
- How am I doing so far? Is this design okay from what you all know?
- How would I implement the preparation steps? Should I create an additional many-to-many implementation (something like preparation(prepID, etc.) and recipePrep(recipeID, prepID)) or just add the directions in the recipes table? I would like this to be an ordered list in the UI (webpage).
Thank you for your help.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您是否查看过任何现有的架构，例如 这个数据库答案？
Have you looked at any of the existing schemas out there, such as this one at DatabaseAnswers?
一些想法：
您可能希望对配方和成分使用相同的表，并带有类型指示符列。原因是食谱可以包含子食谱。我们将组合表称为“Item”。然后你的 RecipeIngredient 表看起来就像
我期望的那样，该表也有一个排序列。
如果您想对这些配方进行任何计算（例如，缩放、营养分析、生产计划），那么您的数量将需要指定计量单位。您可以明确地执行此操作（通过为 uofm 设置单独的列），也可以使用文本字段表示数量并期望用户输入“1 杯”或“2 汤匙”等值。如果您采用这种方法，您需要确保它们输入的内容是可识别的，并在每次需要使用时对其进行解析。这可能会变得令人惊讶的复杂，特别是如果您想以形式化的方式表示配方产量。
假设您想要从食谱到类别 1:M，我仍然不确定为什么您的 RecipeCategory 表会有一个名称列。我认为该名称来自类别定义。
我同意戴夫的观点，即您不太可能重复使用从一个食谱到另一个食谱的准备步骤，因此 RecipePreparationSteps 表（或类似的表）会更合适。
然而，食谱中的成分和说明常常混合在一起。例如。
介绍文字
一些成分。
准备说明
更多成分
烘焙说明。
为了适应这一点，您需要巧妙地在 RecipeIngredient 和 RecipePreparation 步骤表中设置排序值，以便您可以按正确的顺序组合两者的数据进行演示。另一种方法是使用“RecipeLine”表来代替这两个表，这样每一行都可以代表一条指令或一种成分。我想这可能就是你所建议的。纯粹主义者会对这种表超载感到不满，但我不是纯粹主义者。
这是我碰巧知道很多的话题，所以有什么问题就问吧。
some thoughts:
You might want to use the same table for Recipe and Ingredient, with a type indicator column. The reason is that Recipes can contain sub-recipes. Let's call the combined table "Item". Then your RecipeIngredient table would look like
I'd expect that the table would also have a sequencing column.
If you want to do any calculations with these recipes (e.g., scaling, nutritional analysis, production planning) then your quantities will need to specify a unit of measure. You can do that explicitly (by having a separate column for uofm) or you can use a text field for quantity and expect the user to enter values like "1 cup", or "2 tbs". If you take that approach, you'll need to make sure that what they enter is recognizable, and parse it every time you need to use it. This can become surprising complex, especially if you want to represent recipe yields in a formalized manner.
Assuming you want 1:M from recipe to category, I'm still not sure why your RecipeCategory table would have a Name column. I'd think that the name comes from the Category definition.
I agree with Dave that it's unlikely that you'd reuse preparation steps from recipe to recipe, and so a RecipePreparationSteps table (or something like it) would be more appropriate.
However, recipes are often presented with ingredients and instructions intermixed. eg.
Intro text
some ingredients.
prep instructions
some more ingredients
baking instructions.
To accomodate that, you need to cleverly set sequencing values in the RecipeIngredient and RecipePreparation step tables so that you can combine data from both in the proper order for presentation. Another approach would be, instead of these two tables, use a "RecipeLine" table such that each row can represent either an instruction OR an ingredient. I think that may be what you were suggesting. Purists would frown on this kind of table overloading, but I'm not a purist.
This is a topic I happen to know a lot about, so ask anything.
看起来是一个好的开始。一些想法：
Looks like a good start. A few thoughts: