3 Replies - 2679 Views - Last Post: 28 April 2008 - 07:07 AM Rate Topic: -----

#1 generalchaos316  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 04-November 07

Database design: Dealing with arrays

Posted 20 April 2008 - 06:42 AM

I am still hacking away at my little recipe program and am trying to find the best way to design the database that is going to hold all of my data (and not be dog slow when there are tons of rows). As part of this program, I would like to be able to calculate the nutritional information of a recipe "on the fly" by adding up the recipe's individual ingredients' nutrition info (which would be stored in an "ingredients" table).

I am still new to database design and have hit a wall with how to represent this data. If I were representing this in Java I would have a "Recipe" object that holds an array of "Ingredient" objects (along with other variables such as the recipe name, directions, etc). I am not sure what the best practices are for a.) representing an array (of unknown size) of values and b.) representing an abstract data type such as an "Ingredient."

I also want to be able to find recipes that contain a given ingredient(s), in case you want to use up food that is going bad, which is why I don't want to use a simple text variable for the ingredients.

Hopefully that is enough relevant information to get pointed on the right track :)

Is This A Good Question/Topic? 0
  • +

Replies To: Database design: Dealing with arrays

#2 mikeblas  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 43
  • View blog
  • Posts: 390
  • Joined: 08-February 08

Re: Database design: Dealing with arrays

Posted 20 April 2008 - 01:00 PM

In your case, I'd make a many-to-many table. One recipe can have many ingredients, and one ingredient can appear in many recipies.

If we have a recipie table:
RECIPIE
RecepieID,RecepieName
1,"Chocolate Mousse"
2,"Chocolate Cookies"
3,"Lemon Cake"



And a reference table of ingredients:

INGREDIENT
IngredientID,IngredientName
1,"Chocolate Chips"
2,"Lemons"
3,"Sugar"
4,"Fish oil"



We might have this RecepieIngredients table. (Or, we might not; I don't cook, so what the heck do I know?)

RECEPIEINGREDIENTS
RecepieID,IngredientID
1,1
1,3
2,1
2,3
3,2
3,3



In RECEPIE, the RecepieID is the primary key. Similarly, in the INGREDIENT table, the IngredientID is the PK.

In RECEPIEINGREDIENTS, the primary key has two columns: (RecepieID, IngredientID). The RecepieID there is a foreign key back to the RECEPIE table, and the IngredientID is a foreign key back to the INGREDIENT table.

I hope that helps, and that you can understand the examples--representing database tables in a forum is pretty cumbersome.
Was This Post Helpful? 0
  • +
  • -

#3 nsh22  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 23-April 08

Re: Database design: Dealing with arrays

Posted 25 April 2008 - 01:14 PM

just so you know mike, you spelt recipe wrong ( I may have too)
Was This Post Helpful? 0
  • +
  • -

#4 mikeblas  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 43
  • View blog
  • Posts: 390
  • Joined: 08-February 08

Re: Database design: Dealing with arrays

Posted 28 April 2008 - 07:07 AM

Gosh, I did. Curiously, I can't edit the post.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1