配方/成分/测量/数量的数据库架构 [英] Database Schema for Recipe/Ingredient/Measurement/Amount

问题描述

我正在创建一个食谱应用程序来帮助我妻子培养她的蛋糕爱好.这个想法是创建一个食谱数据库来保存她所有的蛋糕食谱.

I'm creating a recipe app to help my wife with her cake hobby. The idea is to create a recipe database to hold all of her cake recipes.

每个食谱都有多种成分.每种成分都有一个计量单位(克、毫升、茶匙等)，然后是数量.

Each recipe would have multiple ingredients. Each ingredient would have a measurement (gm, ml, teaspoons etc) and then a quantity.

我了解如何创建配方"和成分"表，以及如何将这两个表与配方_ingredients"连接起来，但是我正在努力解决如何实现测量和数量字段.

I understand how to create the 'recipes' and 'ingredients' tables and how to link the 2 with a junction table 'recipe_ingredients, however I am struggling with how to then implement the measurement and amount fields.

任何具有更多数据库经验的人都可以提出数据库方案或有任何处理此问题的技巧吗?

Can anyone with a bit more database experience suggest a database scheme or have any tips to handle this?

推荐答案

您对此有一些选择，并且与大多数事情一样，您可以走简单的路线(Nick Coons 在我输入此内容时发布了一个很好的示例)或越来越多的参与路线.以下是一些关于您如何看待此工作的问题，请自问:

You have some options for this, and like most things, you can go the easy route (Nick Coons posted a good example as I'm typing this) or progressively more involved routes. Here are some questions to ask yourself about how you see this working:

1. 您希望测量结果一致吗?(你想总是显示tsp"代表茶匙，还是像尼克的例子那样是自由形式)
2. 您需要多久添加一次单位?随着时间的推移，您是否需要添加 dram 或 hogshead 或其他任何单位，或者您可能只是坚持基础知识?

一个好的中间立场应该是这样的

A good middle ground would be something like

CREATE TABLE `recipe` (
  `recipe_id`    INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`         VARCHAR(128) DEFAULT NULL,
  `description`  TEXT,
  `instructions` TEXT,
  PRIMARY KEY (`recipe_id`)
)

CREATE TABLE `ingredient` (
  `ingredient_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `recipe_id`     INT(10) UNSIGNED NOT NULL,
  `ingredient`    VARCHAR(64) DEFAULT NULL,
  `amount`        DECIMAL(4, 2) DEFAULT NULL,
  `unit`          ENUM ('tsp', 'tbsp', 'oz', 'g', 'lb', 'cup', 'gallon', 'pinch') DEFAULT NULL,
  PRIMARY KEY (`ingredient_id`)
)

这通过强制执行一组单元来满足 #1，这很好.缺点是您必须更改表格以更新单位.使您的前端保持最新的有效选择也可能更加困难.

This satisfies #1 by enforcing a set of units, which is nice. The downside is that you have to alter your table to update the units. It also may be more difficult to keep your front end up to date with the valid choices.

接下来，您可以添加一个单位表，并通过成分表中的外键引用它，如下所示:

Next, you could add a table for units and reference it via foreign key from the ingredients table like so:

CREATE TABLE `unit` (
  `unit_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `label`   VARCHAR(64) DEFAULT NULL,
  `sort`    INT(10) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`unit_id`),
  UNIQUE KEY `unit_label_uk` (`label`)
)

CREATE TABLE `ingredient` (
  `ingredient_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `unit_id`       INT(10) UNSIGNED NOT NULL,
  `recipe_id`     INT(10) UNSIGNED NOT NULL,
  `ingredient`    VARCHAR(64) DEFAULT NULL,
  `amount`        DECIMAL(4, 2) DEFAULT NULL,
  `sort`          INT(10) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`ingredient_id`)
)

这满足 #1 和 #2，允许您轻松管理您的单位并访问列表以供前端使用，因此您无需在更改单位时更改前端.

This satisfies #1 and #2, allowing you to easily manage your units and access the list for use in your front end, so you don't have to alter your front end when you change units.

从那里你可以转入太空，想出处理单位转换等的方法，但这对于你正在尝试做的事情来说可能是过度的.

From there you could spin off into space coming up with ways to handle unit conversion, etc. but that is probably overkill for what you're trying to do.

根据您的评论，我会这样设置:

Per your comment, I would set it up like this:

CREATE TABLE `recipe` (
  `recipe_id`    INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`         VARCHAR(128) NOT NULL,
  `description`  TEXT,
  `instructions` TEXT,
  PRIMARY KEY (`recipe_id`)
)

CREATE TABLE `ingredient` (
  `ingredient_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `label`         VARCHAR(64) NOT NULL,
  `sort`          INT(10) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`ingredient_id`)
  UNIQUE KEY `ingredient_label_uk` (`label`)
)

CREATE TABLE `unit` (
  `unit_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `label`   VARCHAR(64) DEFAULT NULL,
  `sort`    INT(10) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`unit_id`),
  UNIQUE KEY `unit_label_uk` (`label`)
)

CREATE TABLE `recipe_ingredient` (
  `recipe_ingredient_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `recipe_id`            INT(10) UNSIGNED NOT NULL,
  `ingredient_id`        INT(10) UNSIGNED NOT NULL,
  `unit_id`              INT(10) UNSIGNED NOT NULL,
  `amount`               DECIMAL(4, 2) DEFAULT NULL,
  `sort`                 INT(10) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`recipe_ingredient_id`)
)

您的 recipe_ingredient 表在这里完成了大部分工作，将所有内容联系在一起.

Your recipe_ingredient table is doing the bulk of the work here, tying everything together.

这篇关于配方/成分/测量/数量的数据库架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！