php - 用于此数据库建模/设计的最佳方法是什么？

Question

我正在编写一个厨房/食谱管理程序。截至目前，该程序可以将新成分添加到数据库中。我的下一步是使用配料将新食谱添加到数据库中。

由于每个食谱都有多种成分，并且可以将无限数量的成分添加到食谱中，因此为每个所选成分创建一个单独的列效率不高(我假设)。

所以我想出的存储配料选择的方法是:

1. 将 recipe_ingredients 列添加到数据库中的配方表，并将每种成分的名称存储在 1 个(文本)字段中，以逗号分隔(CSV 格式)。

2. 编写一个 PHP 脚本，将 recipe_ingredients 列表存储到一个数组中。

3. 对所有其他属性(recipe_ingredients_serving_size、recipe_ingredients_calories 等)执行相同操作

所以问题是:这种方法是最优的吗？如果不是，什么是更好的方法？

Answer 1

这是一个简单的 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，反之亦然。

为什么这种设计促进了多对多关系？因为 XREF 表中的数据允许如下所示:

+-----------------------------+
| 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

^ 如果您在关系之间正确定义了 CASCADE 规则，则上述 DELETE 操作还会删除该食谱的所有关联。

---

回顾一下您的原始设计，如果您想更新或删除食谱中的某些成分，或者更改成分的名称怎么办？您需要复杂的程序代码来修改 csv 字符串中的正确位置，或者您需要更新表格中的每一行以反射(reflect)单一成分中哪怕最细微的变化。

还有许多您可以回答的更有说服力的问题，而这些问题是您使用原始设计无法真正回答的，例如:

• 卡路里含量最高/最低的食谱？
• 食谱中包含的成分最多？

...不胜枚举，实现此设计的好处将为您提供良好的服务。通过以正确的方式做事，你将避免很多困难和痛苦。 =)