drcigvoy48900
2012-07-15 00:20
浏览 39
已采纳

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

I'm writing a program for kitchen/recipe management. As of now, the program can add new ingredients to the database. My next step is to add new recipes to the database, using the ingredients.

Since each recipe will have multiple ingredients, and an unlimited number of which can be added to a recipe, creating a separate column for each ingredient selected wouldn't be efficient (I'm assuming).

So the method I've come up with to store the ingredient selections is:

  1. Add a recipe_ingredients column to the recipe table in the db, and store the name of each ingredient in 1 single (text) field, separated by commas (CSV style).

  2. Write a PHP script to store the list of recipe_ingredients into an array.

  3. Do the same for all other attributes (recipe_ingredients_serving_size, recipe_ingredients_calories, etc.)

So the question is: Is this method optimal, and if not, what's a better way to go about this?

图片转代码服务由CSDN问答提供 功能建议

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

由于每个配方都有多种成分,并且可以将无限数量的配方添加到配方中,因此创建了一个配方。 选择的每种成分的单独列不会有效(我假设)。

因此,我提出的存储成分选择的方法是: \ n

  1. 将recipe_ingredients列添加到数据库中的配方表中,并将每个成分的名称存储在1个单独(文本)字段中,以逗号分隔(CSV样式)。 / p>

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

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

    所以问题是:这种方法是否是最优的,如果 不,有什么更好的方法来解决这个问题?

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

2条回答 默认 最新

  • drv13270 2012-07-15 00:50
    最佳回答

    This is a simple N:M(many-to-many) relationship, and your approach that you've laid out will likely spell out disaster in terms of efficiency and management.


    Here is your situation:

    • You have two entities: recipes and ingredients.
    • One ingredient may be a part of many recipes.
    • One recipe may be made up of many ingredients.

    Whenever you have this relationship between any two entities, you are going to want to have not two, but three tables:

    +-----------+     +-------------------------+     +-------------------+
    | recipes   |     | recipes_has_ingredients |     | ingredients       |
    +-----------+     +-------------------------+     +-------------------+
    | recipe_id |     | recipe_id               |     | ingredient_id     |
    | name      |     | ingredient_id           |     | name              |
    | ...       |     +-------------------------+     | calories          |
    +-----------+                                     +-------------------+
    

    recipes and ingredients are what's known as base tables, where they store intrinsic information about that particular entity.

    The recipes_has_ingredients table is what's known as a cross-reference table (or "XREF"), which stores the associations between the two entities. The fields in this table: recipe_id and ingredient_id both link to their respective base tables, and the combination of the two in each row in the XREF table is unique. It basically maps the many associations that each recipe_id may have to different ingredient_id's and vice versa.

    Why does this design facilitate many-to-many relationships? Because data in that XREF table is allowed to look like this:

    +-----------------------------+
    | recipe_id  |  ingredient_id |
    +-----------------------------+
    | 1          |  1             |
    | 1          |  2             |
    | 1          |  3             |
    | 2          |  1             |
    | 2          |  2             |
    | 2          |  3             |
    | 3          |  1             |
    | 3          |  2             |
    | 3          |  3             |
    +-----------------------------+
    

    As you can clearly see: One recipe is associated with many(3) ingredients, and one ingredient is associated with many(3) recipes. Also notice how values in either of the columns are allowed to repeat, but that the combination of the two columns is unique — this is really the key aspect of this design that makes the N:M relationship work.

    So here are some simple examples of how you can easily retrieve and manage data using this design:

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

    ^ The above DELETE operation also deletes all of that recipe's associations if you've properly defined the CASCADE rules between your relationships.


    Looking back at your original design, what if you wanted to update or delete certain ingredients that a recipe has, or how about change the name of an ingredient? You would need hacky procedural code to modify the right positions in csv strings, or you would need to update every row in the table to reflect even the slightest changes in a single ingredient.

    There are also many more compelling questions you could answer that you couldn't really otherwise using your original design such as:

    • Recipe with highest/lowest calorie count?
    • Ingredient that is contained by the most recipes?

    ...The list goes on, and the benefits of implementing this design will serve you well. You will prevent yourself from a great deal of hardship and suffering by doing things the right way. =)

    评论
    解决 无用
    打赏 举报
查看更多回答(1条)

相关推荐 更多相似问题