我正在编写一个厨房/食谱管理程序。截至目前,该程序可以将新成分添加到数据库中。我的下一步是使用成分将新食谱添加到数据库中。
由于每个配方都有多种成分,并且可以将无限数量的成分添加到食谱中,因此为所选的每种成分创建一个单独的列不会有效(我假设(。
因此,我想出的存储成分选择的方法是:
- 在
数据库中的配方表中添加一个recipe_ingredients列,并将每种成分的名称存储在 1 个(文本(字段中,用逗号(CSV 样式(分隔。
编写一个 PHP 脚本以将recipe_ingredients列表存储到数组中。
对所有其他属性(recipe_ingredients_serving_size、recipe_ingredients_calories等(执行相同的操作。
所以问题是:这种方法是最佳的吗,如果不是,有什么更好的方法呢?
这是一个简单的 N:M(多对多(关系,您制定的方法可能会在效率和管理方面说明灾难。
这是您的情况:
- 您有两个实体:
recipes
和ingredients
。 - 一种成分可能是许多食谱的一部分。
- 一个食谱可能由许多成分组成。
每当在任意两个实体之间建立这种关系时,您都需要的不是两个表,而是三个表:
+-----------+ +-------------------------+ +-------------------+
| recipes | | recipes_has_ingredients | | ingredients |
+-----------+ +-------------------------+ +-------------------+
| recipe_id | | recipe_id | | ingredient_id |
| name | | ingredient_id | | name |
| ... | +-------------------------+ | calories |
+-----------+ +-------------------+
recipes
和ingredients
就是所谓的基表,它们在其中存储有关该特定实体的内在信息。
recipes_has_ingredients
表就是所谓的交叉引用表(或"XREF"(,它存储两个实体之间的关联。此表中的字段:recipe_id
和 ingredient_id
都链接到其各自的基表,并且 XREF 表中每行中两者的组合是唯一的。它基本上将每个recipe_id
可能具有的许多关联映射到不同的ingredient_id
,反之亦然。
为什么这种设计促进了多对多关系?因为允许该外部参照表中的数据如下所示:
+-----------------------------+
| recipe_id | ingredient_id |
+-----------------------------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
+-----------------------------+
您可以清楚地看到:一个配方与许多 (3( 种成分相关联,一种成分与许多 (3( 种食谱相关联。还要注意如何允许任一列中的值重复,但两列的组合是唯一的 — 这实际上是使 N:M 关系正常工作的设计的关键方面。
因此,下面是一些如何使用此设计轻松检索和管理数据的简单示例:
// Given a particular recipe_id, retrieve all ingredients used in that recipe:
SELECT name
FROM recipes_has_ingredients
INNER JOIN ingredients USING (ingredient_id)
WHERE recipe_id = <id>
// Retrieve the name of recipe (of id 4), and total amount of calories it has:
SELECT a.name,
SUM(c.calories) AS calorie_count
FROM recipes a
INNER JOIN recipes_has_ingredients b ON a.recipe_id = b.recipe_id
INNER JOIN ingredients c ON b.ingredient_id = c.ingredient_id
WHERE a.recipe_id = 4
GROUP BY a.recipe_id,
a.name
// Given a list of ingredient_id's, retrieve all recipes that contain
// ALL of the listed ingredients
SELECT name
FROM recipes
INNER JOIN recipes_has_ingredients USING (recipe_id)
WHERE ingredient_id IN (1,2,3)
GROUP BY recipe_id
HAVING COUNT(*) = 3
// Given a particular recipe_id (id 6), add two more ingredients
// that it has (ids 4 & 9):
INSERT INTO recipes_has_ingredients VALUES (6,4), (6,9);
// Delete a particular recipe:
DELETE FROM recipe WHERE recipe_id = 4
^ 上述DELETE
操作还会删除该配方的所有关联,前提是您在关系之间正确定义了级联规则。
回顾您的原始设计,如果您想更新或删除食谱中的某些成分,或者更改成分的名称怎么办?您将需要破解程序代码来修改csv字符串中的正确位置,或者您需要更新表中的每一行以反映单个成分的最细微变化。
您还可以回答许多更引人注目的问题,否则您无法使用原始设计,例如:
- 卡路里
- 含量最高/最低的食谱?
- 大多数食谱所包含的成分?
。列表还在继续,实现此设计的好处将很好地为您服务。你将通过以正确的方式做事来防止自己遭受巨大的艰辛和痛苦。=)
这不是解决此问题的理想方法。
在列中逗号分隔值会使这些值变得毫无用处 - 如果您只是将一堆成分连接成一个字符串,则按成分查询食谱会很痛苦。
此外,如果您将单一成分的卡路里计数存储为逗号分隔的字符串,您将如何将它们与成分联系起来?
理想的解决方案是为成分创建一个新表,并将名称和相关信息存储在该表中。
ingredients
-----------
id
name
num_calories
有许多食谱具有许多成分,因此请创建一个将成分链接到特定食谱的表,这使得成分与食谱的关系多对多,而不是直接的多对多。
recipe_ingredients------------------recipe_idingredient_id
您应该在此处阅读规范化。