CODEWITHSUNDEEP
mysqltypes
MagentoMan
MagentoMan

Reputation: 573

MYSQL Database Structure - Storing Multiple Values

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!

Upvotes: 2

Views: 711

Answers (4)

aerook
aerook

Reputation: 278

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.

Upvotes: 1

Pablo Francisco P&#233;rez Hidalgo
Pablo Francisco P&#233;rez Hidalgo

Reputation: 27692

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

Upvotes: 1

dethtron5000
dethtron5000

Reputation: 10841

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.

Upvotes: 1

juergen d
juergen d

Reputation: 204746

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


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


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


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

Upvotes: 1

Related Questions