MYSQL Database Structure - Storing Multiple Values

Question

I have some questions regarding a new database structure.

1. Let's say a user can create a recipe. Each recipe will obviously have several ingredients. Each ingredient would have the following 4 fields.

1. ingredients
2. quantity of ingredients
3. unit size of the ingredients
4. prep method for the ingredients

I have been racking my brain trying to figure out how to structure this. Should I simply create four rows and contain each one of the inputs into an array and store it in the row it belongs to? What would be the best way to do this and most efficient?

2. If you have a description field and directions field where the length of characters are unknown, would a blob type be best here?

Thanks!

Pablo Francisco Pérez Hidalgo · Accepted Answer · 2014-04-06 16:14:30Z

1

As your ingredients can appear at many recipes and different recipes can contain the same ingredientes you have a N:N relation between recipes and ingredients.

On the other hand, you have what is called relation attributes so I would try something like:

Recipes table: RECIPES
Igredients table: INGREDIENTS
Recipe - has - Ingredient relation: Stored in a table: HAS_INGREDIENT where: quantity, unit size, pre method are fields and where the RECIPES primary key and INGREDIENTS primary key are foreing keys.

Consider the following ER diagram:

enter image description here

answered Apr 6, 2014 at 16:14

Pablo Francisco Pérez Hidalgo

27.8k8 gold badges39 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

aerook Over a year ago

Whoa! How did you do that? My goolefu is failing me!

aerook Over a year ago

Ah, nevermind. For some reason I was momentarily under the influence that you were able to generate that diagram using markup, I see now that is just an image.

MagentoMan Over a year ago

Thank you! I will use this approach.

juergen d · Accepted Answer · 2014-04-06 16:08:22Z

1

ingredients table
-----------------
id
name


users table
-----------
id
name


recipes table
-------------
id
user_id
prep_method


recipe_ingredients table
------------------------
recipe_id
ingredient_id
quantity
unit

answered Apr 6, 2014 at 16:08

juergen d

205k40 gold badges303 silver badges377 bronze badges

3 Comments

MagentoMan Over a year ago

Thanks. I have thought about doing it this way. However, that still does not answer the question of how it should be stored. Each one with a new row or all the data for each area in one row?

juergen d Over a year ago

Except for the recipe_ingredients only one record. In the recipe_ingredients table you create a record for every ingredient of the recipe.

Ryan-Neal Mes Over a year ago

I think juergen has hit it on the spot. With regards to storing the text, I would just store it as a varchar - pick a value that you feel would be sufficient. Add validation to prevent oversized values from being pushed to your db.

dethtron5000 · Accepted Answer · 2014-04-06 16:11:48Z

1

For #2 - generally you'd use a TEXT field. BLOBS are typically used to store binary data. MySQL has several different text types - you do have to consider your theoretical upper bound, and choose an appropriate text type.

answered Apr 6, 2014 at 16:11

dethtron5000

10.9k1 gold badge33 silver badges32 bronze badges

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

I actually just started working on a learning project involving exactly this, as part of an inventory tracking system for my pantry.

Question 1:

We have a recipe, which contains many ingredients, many directions, and various other properties such as servings, cook time, etc. The ingredients are their own table(model) in the database, and they reference (in my application, I don't know this is relevant to you) an item that exists in my pantry. Here are the basic thrusts of my models:

class Recipe < ActiveRecord::Base
  
  ####################
  #   Database Rows:
  #     id            -> integer
  #     name          -> string
  #     description   -> string
  #     directions    -> serialized string, array
  #     servings      -> integer

  serialize :directions

  # Relations
  has_many :ingredients

end

For me, working with the directions as an array of strings is the most straightforward solution. ActiveModel will store the entire array as a single string in a single column, and using the serialize helper method, it gets converted into an array inside the application. I'm not sure if you're using Rails, so this may not be helpful.

class Ingredient < ActiveRecord::Base

  ######################
  # Database Rows:
  #   id           -> integer
  #   quantity     -> decimal
  #   measurement  -> string
  #   inventory_id -> integer

  # Relations
  belongs_to :recipe
  has_one :inventory

end

More pointedly, in the database, there are tables "recipes" and "ingredients". A single recipe will reference many rows in the ingredients table. The recipe stores the list of ingredients that it contains.

Question 2:

According to this, we are looking at 8,000 byte limit for string columns. Will this be enough for your needs? You decide. If you think that's too small for the description, you can change the row type. You could store the directions in a separate table so that each individual direction has the 8,000 byte limit. Those are long directions, though...

My project is here, if you wanna poke around or anything. It's still in progress.

Collectives™ on Stack Overflow

MYSQL Database Structure - Storing Multiple Values

4 Answers 4

3 Comments

3 Comments

Comments

Question 1:

Question 2:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

3 Comments

Comments

Question 1:

Question 2:

Comments

Your Answer

Sign up or log in

Post as a guest

Related