用于存储食品配方的数据库设计

I want to make a database of recipes that I like, but I'm having trouble designing it. I want to have at least two tables:

我想做一个我喜欢的食谱的数据库，但是我在设计上有困难。我想要至少两张桌子:

Recipe table (Has description, ingredients, directions, etc...)
食谱表(有说明、成分、说明等)
Ingredients table (ingredient type, other attributes, etc...)
配料表(配料类型、其他属性等)

What would be a way to associate a the two tables together? Would I need a third table that would store the several relationships from a recipe to multiple ingredients?

如何将a和这两个表联系起来?我是否需要第三张表来存储从食谱到多种配料的各种关系?

As you can probably tell, I'm relatively new to this stuff, just trying to figure out the right way to do it on the first try.

你可能知道，我对这方面比较陌生，只是想在第一次尝试时找出正确的方法。

Thanks!

谢谢!

3 个解决方案

#1

Here is a link to a pretty advanced one:

这里有一个相当高级的链接:

http://www.databaseanswers.org/data_models/recipes/index.htm

http://www.databaseanswers.org/data_models/recipes/index.htm

but if you really want to code it yourself I would go with a third relational table.

但是如果你真的想自己编写它，我就用第三个关系表。

cheers, Mike

干杯,迈克

#2

Does one recipe have many ingredients or many recipe's have many ingredients

一个食谱有很多配料还是很多配料

I'd expect it will be the latter to allow you to find recipe's by ingredient.

我希望是后者让你找到食谱的成分。

So you will need an intermediate table to break the many to many relationship into two one to many relationships.

因此，您将需要一个中间表来将多个关系分解为两个、一个或多个关系。

Recipe(RecipeID, etc...) 
Ingredients(IngredientID, etc....)
RecipeIngredients(RecipeID, IngredientID, etc..)

Then in RecipeIngredients I would put information such as quantities of that ingredient for that recipe.

然后，在recipe配料中，我会放一些信息，比如这个配方的原料的数量。

#3

You could consider something like a folder of text files, with a full-text index of some sort.

您可以考虑类似文本文件的文件夹，其中包含某种类型的全文索引。

Or a table like Recipes(ID, LikeRatio, Description as text). Again with a full-text search.

或者像recipe (ID, LikeRatio, Description as text)之类的表格。再一次用全文搜索。

Theorically you could use a normalized model like the one @Mike suggested. But looking at actual recipes, it needs to be more flexible. For example in this model, there's no evidence of the use at some step of the outcome of a previous step.

理论上，您可以使用一个规范化模型，就像@Mike建议的那样。但是从实际的食谱来看，它需要更加灵活。例如，在这个模型中，没有证据表明在前一步结果的某个步骤中使用。

#1