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.

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?

Solution

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:

Do you want uniformity in your measurements? (Do you want to always show "tsp" for teaspoon, or can it be freeform as in Nick's example)
How often will you need to add units? Will you need to add the dram or hogshead, or whatever as units as time goes on, or will you probably just stick to the basics?

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`)
)

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`)
)

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.

EDIT: 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`)
)

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